Re: Memory Usage and OpenBSD - Mailing list pgsql-general

Greg Smith wrote:
> Jeff Ross wrote:
>> pgbench is run with this:
>> pgbench -h varley.openvistas.net -U _postgresql -t 20000 -c $SCALE
>> pgbench
>> with scale starting at 10 and then incrementing by 10.  I call it
>> three times for each scale.  I've turned on logging to 'all' to try
>> and help figure out where the system panics, so that may lower the
>> TPS somewhat but I have not been very favorably impressed with the
>> speed of these U320 15K disks in RAID10 yet.
>
> "-c" sets the number of clients active at once.  pgbench has a
> database scale option when you're initializing, "-s", that sets how
> many records are in the tables, and therefore how large the database
> is.  If you don't set the scale to a larger number, so that "-c" >
> "-s", you'll get bad performance results.  The way you're saying scale
> but changing the client numbers is a little confusing.
>

My bad.  I know that more information is better than too little.

I think I'm doing it right.  Here's the whole script.  I run it from
another server on the lan.

#!/bin/sh
#use this to automate pg_bench load
MAX_SCALE=150
HOST=varley.openvistas.net
SCALE=70
rm -rf /tmp/pgbench_results_scale*
while [ $SCALE -ne $MAX_SCALE ] ; do
  pgbench -i -s $SCALE -h $HOST -U _postgresql pgbench | tee -a
/tmp/pgbench_results_scale_$SCALE.txt
  vacuumdb --analyze -h $HOST -U _postgresql pgbench  | tee -a
/tmp/pgbench_results_scale_$SCALE.txt
  psql -h $HOST -U _postgresql -c "checkpoint;" pgbench  | tee -a
/tmp/pgbench_results_scale_$SCALE.txt
  psql -h $HOST -U _postgresql -c "SELECT relname, reltuples,
pg_size_pretty(relpages * 8192) as size FROM pg_class \
    where relname like ('%account%');" pgbench  | tee -a
/tmp/pgbench_results_scale_$SCALE.txt
  psql -h $HOST -U _postgresql -c "select
pg_size_pretty(pg_database_size(oid)) from pg_database \
    where datname = 'pgbench';" pgbench  | tee -a
/tmp/pgbench_results_scale_$SCALE.txt
  time pgbench -h $HOST -U _postgresql -t 20000 -c $SCALE  pgbench  |
tee -a /tmp/pgbench_results_scale_$SCALE.txt
  time pgbench -h $HOST -U _postgresql -t 20000 -c $SCALE  pgbench  |
tee -a /tmp/pgbench_results_scale_$SCALE.txt
  time pgbench -h $HOST -U _postgresql -t 20000 -c $SCALE  pgbench  |
tee -a /tmp/pgbench_results_scale_$SCALE.txt
  cat /tmp/pgbench_results_scale_$SCALE.txt | mail -s "Results for Scale
$SCALE" jross@openvistas.net
  let SCALE=$SCALE+10

done


> I can't comment how whether yours are good or bad numbers without
> knowing the actual database scale number.  When reporting a pgbench
> result, it's handy to include the complete output from one of the
> runs, just so people can see exactly what test was run.  After that
> you can just show the TPS values.  Showing the command used to
> initialize the pgbench database can also be helpful.
>
>
And here's the whole output of scale = 70.  It made it throught this
last time without panickinging, but then did panic on scale=80.

CHECKPOINT
        relname        |  reltuples  |  size
-----------------------+-------------+--------
 pgbench_accounts_pkey |       7e+06 | 120 MB
 pgbench_accounts      | 6.99985e+06 | 868 MB
(2 rows)

 pg_size_pretty
----------------
 994 MB
(1 row)

transaction type: TPC-B (sort of)
scaling factor: 70
query mode: simple
number of clients: 70
number of transactions per client: 20000
number of transactions actually processed: 1400000/1400000
tps = 293.081245 (including connections establishing)
tps = 293.124705 (excluding connections establishing)
transaction type: TPC-B (sort of)
scaling factor: 70
query mode: simple
number of clients: 70
number of transactions per client: 20000
number of transactions actually processed: 1400000/1400000
tps = 332.154154 (including connections establishing)
tps = 332.219275 (excluding connections establishing)
transaction type: TPC-B (sort of)
scaling factor: 70
query mode: simple
number of clients: 70
number of transactions per client: 20000
number of transactions actually processed: 1400000/1400000
tps = 354.983013 (including connections establishing)
tps = 355.181403 (excluding connections establishing)