Re: postgresql 8.3 tps rate

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(at)gmail(dot)com> wrote:
> On 1/22/09, Ibrahim Harrani <ibrahim(dot)harrani(at)gmail(dot)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
>