Hello everybody. I'm trying to build a postgres server to act as the
repository for a bunch of data-crunching clients. Hardware-wise, the
machine is a dual p3 700 with 768MB of RAM and 3 striped 10k RPM u160
drives sitting on a DPT SmartRAID V. Software-wise, I've installed linux
2.4.18 and postgres 7.2.
The database is structured such that there is a control table that every
client will perform indexed lookups from and the very occasional update
on, and this table is small enough that it easily fits in memory. All the
data is written to a second table and never accessed again.
It seems to me that such a machine should be able to handle an awful lot
of simple database work, but I'm disappointed in how little seems to be
getting through. Each client connection does ~1500 lookups in the control
table and another ~1500 inserts into the data table. After I add about 10
of these clients simultaneously, the cpu load (according to vmstat) goes
to about 30% idle. Interestingly, despite the fact that the disks are not
very busy, adding more clients does not make the processors work
harder.... they stay around 20-30% loaded even if I triple the load to 30
simultaneous clients.
I've checked all the sql commands I use, and all the lookups and updates
are using indices. The system isn't swapping, and posgres is using its
400MB of shared buffers to good effect. I have noticed that context
switches seem rediculously high - about 30,000/sec, give or take 10k - but
having nothing to compare it to I don't know if that's unreasonable for a
busy database machine or even what I could do about it if it wasn't.
I am using a much-derided 8139-based ethernet card, but seeing as how I'm
only transfering about 30KB/sec each way (and I know for a fact that even
*that* chipset can transmit well over 2MB/sec), I don't think that is the
blocking issue.
Does anybody have any suggestions as to how I can make this server work
closer to its limits? It takes 10 clients about 3 minutes to run, meaning
that postgres is roughly maxing out at (5,000 indexed lookups + 5,000
inserts)/sec. It seems to me that my hardware should be able to support
more than that. Am I being unreasonable? I'm particularly interested as to
why adding more clients doesn't make postgres work harder, when the disks
are not being used very much and vmstat claims the cpus are not fully
utilized.