Обсуждение: pgbench out of memory error
Hi, I'm trying to put a new server on line and I'm having a problem getting any kind of decent performance from it. pgbench yields around 4000 tps until scale and clients both are above 21, then I see the following: ... 2180000 tuples done. 2190000 tuples done. 2200000 tuples done. set primary key... NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_branches_pkey" for table "pgbench_branches" NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_tellers_pkey" for table "pgbench_tellers" NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_accounts_pkey" for table "pgbench_accounts" ERROR: out of memory DETAIL: Failed on request of size 67108864. The primary key index is not created and that takes the tps to around 2, if I let it run overnight. I'm using this script to run pgbench from another machine on the lan: #!/bin/sh #use this to automate pg_bench load #scale and clients are the same number #increment by one on each loop MAX_SCALE=25 HOST=varley.openvistas.net SCALE=22 while [ $SCALE -ne $MAX_SCALE ] ; do pgbench -i -s $SCALE -h $HOST pgbench vacuumdb --analyze -h $HOST pgbench psql -h $HOST -c "checkpoint;" pgbench time pgbench -h $HOST -t 2000 -c $SCALE -S pgbench time pgbench -h $HOST -t 2000 -c $SCALE -S pgbench time pgbench -h $HOST -t 2000 -c $SCALE -S pgbench let SCALE=$SCALE+1 done The server itself is a 2.4GHz dual processor Xeon with 4GB of ram running OpenBSD -current. For disks I have 6 15k U320 scsi disks set up as 3 sets of RAID1. The controller is an LSI MegaRAID 320-1LP. It has 64MB or ram onboard and I've installed the BBU. The controller cache is set to write thru for all three volumes because tests using dd and bonnie++ show that write thru is twice as fast as write back. I haven't dug into that any more to figure out why. I've used pgtune to help configure postgresql, and I'm using these values in postgresql.conf. All other settings are at the default value. listen_addresses = '*' # what IP address(es) to listen on; unix_socket_directory = '/var/postgresql/' # (change requires restart) checkpoint_completion_target = 0.7 # pgtune wizard 2009-11-17 checkpoint_segments = 8 # pgtune wizard 2009-11-17 maintenance_work_mem = 240MB # pgtune wizard 2009-12-14 effective_cache_size = 2816MB # pgtune wizard 2009-12-14 work_mem = 18MB # pgtune wizard 2009-12-14 wal_buffers = 4MB # pgtune wizard 2009-12-14 shared_buffers = 960MB # pgtune wizard 2009-12-14 max_connections = 200 # pgtune wizard 2009-12-14 #archive_mode = on # allows archiving to be done archive_command = 'cp -i %p /wal/5432/%f < /dev/null' # command to use to archive a logfile segment archive_timeout = 0 # force a logfile segment switch after this number of seconds; 0 disables log_statement = 'all' # none, ddl, mod, all log_line_prefix = '<%u%%%d> ' # special values: datestyle = 'iso, mdy' (I had archive command = on before but I kept filling up my 5GB /wal partition so I turned it off. Logging was none but I set it to all to try to get some more detail about the error.) Additionally, I have used sysctl to adjust some memory values: kern.seminfo.semmni=4096 kern.seminfo.semmns=9082 kern.shminfo.shmall=128000 kern.shminfo.shmmax=2048000000 I hope that's enough information to help someone point me in the right direction. Even before I get to the out of memory error 4000 tps seems very low for what I think this hardware should be capable of doing. Thanks, Jeff Ross
Jeff Ross wrote: > I'm trying to put a new server on line and I'm having a problem > getting any kind of decent performance from it. pgbench yields around > 4000 tps until scale and clients both are above 21, then I see the > following: > NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index > "pgbench_accounts_pkey" for table "pgbench_accounts" > ERROR: out of memory > DETAIL: Failed on request of size 67108864. You've got "maintenance_work_mem = 240MB", but it looks your OS is not allowing you to allocate more than around 64MB. Have you looked at the active ulimit settings for the accounts involved? > The controller cache is set to write thru for all three volumes > because tests using dd and bonnie++ show that write thru is twice as > fast as write back. I haven't dug into that any more to figure out why. That's bizarre, and you'll never get good pgbench results that way regardless of what dd/bonnie++ say--pgbench does database commits, which is what you need the cache to accelerate, while those two tests don't. But I don't think this is relevant to your immediate problems, because you're running the select-only test so far, which isn't doing writes at all. Regardless, if I got a new system and it performed worse on dd/bonnie++ with the cache turned on, I'd send it back. > time pgbench -h $HOST -t 2000 -c $SCALE -S pgbench Your number of transactions here is extremely low. I'd bet you're just measuring startup overhead here. Try using 20,000 per client to start instead and see what happens. On the select-only, you can easily need 1M total transactions to get an accurate reading here. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com