On Thu, Dec 19, 2013 at 10:44 AM, Shaun Thomas <sthomas@xxxxxxxxxxxxxxxx> wrote:
On 12/19/2013 11:00 AM, Dave Johansen wrote:When you increase the size of the initialized pgbench tables, you increase the size on disk. My guess is that you doubled it so that the data no longer fits in memory. You can verify this yourself:
When I run pgbench in "SELECT only" after doing "-i -s 2000" I get what
appears to be good performance (60k-70k tps) but if I initialize a new
database with "-i -s 4000" the tps drops to 4k-7k. Is this order of
magnitude drop expected? Or is there something wrong with my hardware or
database configuration that is causing this issue?
SELECT pg_size_pretty(sum(pg_database_size(oid))::bigint)
from pg_database;
Any amount of memory you have that is smaller than that, will affect select performance. I can guarantee you will not get 60k-70k tps from anything short of an array of SSD devices or a PCIe NVRAM solution. Your '-s 2000' test was probably running mostly from memory, while the '-s 4000' did not.
What you're seeing is the speed your records are being supplied from disk, plus whatever cache effects are there when records are read before they are flushed in favor of more recent data.
Yep, that was exactly it and that definitely makes sense now that you point it out.
Right now, we're running a RAID 1 for pg_clog, pg_log and pg_xlog and then a RAID 1+0 with 12 disks for the data. Would there be any benefit to running a separate RAID 1+0 with a tablespace for the indexes? Or is reading the indexes and data a serial process where separating them like that won't have any big benefit?
Thanks,
Dave
Thanks,
Dave
