Re: postgresql 8.3 tps rate - Mailing list pgsql-performance

Hi Merlin,

Here is the bonnie++ and new pgbench result with high transaction numbers.


$ pgbench -i -s 30 -U pgsql pgbench
$ pbench -c 100 -t 1000 -U pgsql -d pgbench

transaction type: TPC-B (sort of)
scaling factor: 30
number of clients: 100
number of transactions per client: 1000
number of transactions actually processed: 100000/100000
tps = 45.145051 (including connections establishing)
tps = 45.162367 (excluding connections establishing)

$ bonnie++
Writing a byte at a time...done
Writing intelligently...done
Rewriting...done
Reading a byte at a time...done
Reading intelligently...done
start 'em...done...done...done...done...done...
Create files in sequential order...done.
Stat files in sequential order...done.
Delete files in sequential order...done.
Create files in random order...done.
Stat files in random order...done.
Delete files in random order...done.

Version 1.93d       ------Sequential Output------ --Sequential Input- --Random-
Concurrency   1     -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
myserver 300M   391  97  9619   1  8537   2   673  99 +++++ +++  1196  16
Latency               211ms     388ms     325ms   27652us     722us    6720ms
Version 1.93d       ------Sequential Create------ --------Random Create--------
myserver    -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP
                 16  9004  25 +++++ +++ +++++ +++  8246  20 +++++ +++ +++++ +++
Latency               592ms     208us     102us     673ms     179us     100us

1.93c,1.93d,myserver,1,1232710758,300M,,391,97,9619,1,8537,2,673,99,+++++,+++,1196,16,16,,,,,9004,25,+++++,+++,+++++,+++,8246,20,+++++,+++,+++++,+++,211ms,388ms,325ms,27652us,722us,6720ms,592ms,208us,102us,673ms,179us,100us

When I compare my  bonnie++ result with the one at
http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm,
It seems that there is something wrong with the disks!?

On Thu, Jan 22, 2009 at 7:14 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On 1/22/09, Ibrahim Harrani <ibrahim.harrani@gmail.com> wrote:
>>
>>  Is this rate is normal or not? What can I do to improve tps and insert
>>  performance?
>>
>>  postgresql.conf
>>
>>  shared_buffers = 800MB                  # min 128kB or max_connections*16kB
>>  work_mem = 2MB                          # min 64kB
>>  maintenance_work_mem = 32MB             # min 1MB
>
> I would raise maintenance_work_mem, although it's unrelated to your issue.
>
>>  max_connections = 600                   # (change requires restart)
>>  max_fsm_relations = 2000                # min 100, ~70 bytes each
>>  synchronous_commit = off
>
> Something is very wrong.  40tps is low, even for sata raid 1, but
> absolutely terrible with synchronous_commit = off.  This suggests you
> are problems are read related and not sync related.  Can you rerun
> pgbench using the custom sql feature, passing insert statements?
>
> Are you sure nothing else is going on/wrong with the box? Can we see a
> bonnie++ run?
>
> merlin
>