On Thu, Dec 19, 2013 at 10:44 AM, Shaun Thomas <stho...@optionshouse.com>wrote:
> On 12/19/2013 11:00 AM, Dave Johansen wrote: > > 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? >> > > 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: > > 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
