Greg Smith <gsmith@xxxxxxxxxxxxx> writes: > On Mon, 16 Mar 2009, ml@xxxxxxxxx wrote: > >> Any idea why my performance colapses at 2GB Database size? I don't understand how you get that graph from the data above. The data above seems to show your test databases at 1.4GB and 2.9GB. There are no 1GB and 2GB data points like the graphs show. Presumably the data point at 2G on the graph should really be at 2.9GB? In which case I don't find it surprising at all that performance would start to shift from RAM-resident before that to disk-resident above that. You have 1GB set aside for shared buffers leaving about 3GB for filesystem cache. You could try setting shared buffers smaller, perhaps 512kB or larger, perhaps 3.5GB. To minimize the overlap. I would tend to avoid the latter though. One thing to realize is that pgbench performs a completely flat distribution of data accesses. So every piece of data is equally likely to be used. In real life work-loads there are usually some busier and some less busy sections of the database and the cache tends to keep the hotter data resident even as the data set grows. > In your case, you've got shared_buffers=1GB, but the rest of the RAM is the > server isn't so useful to you because you've got checkpoint_segments set to the > default of 3. That means your system is continuously doing small checkpoints > (check your database log files, you'll see what I meant), which keeps things > from ever really using much RAM before everything has to get forced to disk. Why would checkpoints force out any data? It would dirty those pages and then sync the files marking them clean, but they should still live on in the filesystem cache. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
