Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL insert speed tests

From: Sezai YILMAZ <sezai(dot)yilmaz(at)pro-g(dot)com(dot)tr>
To: pgsql-general(at)postgresql(dot)org
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: PostgreSQL insert speed tests
Date: 2004-02-28 08:29:56
Message-ID: 40405184.8060700@pro-g.com.tr (view raw or flat)
Thread:
Lists: pgsql-general
Tom Lane wrote:

>Sezai YILMAZ <sezai(dot)yilmaz(at)pro-g(dot)com(dot)tr> writes:
>  
>
>>I changed the three hash indexes to btree.
>>The performance is increased about 2 times (in PostgreSQL 7.3.4  1905 
>>rows/s).
>>Concurrent inserts now work.
>>    
>>
>Concurrent inserts should work with hash indexes in 7.4, though not 7.3.
>  
>
I notice this condition. I do not get dead locks with 7.4 on schema with 
hash indexes. 7.4 solves this problem but is very slow.

>The slowdown you report probably is due to the rewrite of hash indexing
>to allow more concurrency --- the locking algorithm is more complex than
>it used to be.  I am surprised that the effect is so large though.
>Could you make your test program available?
>  
>
The test program and .SQL script is attached

Comiple and link scenarios:

without transactions (where each insert is a transaction)
$ gcc -o tester tester.c -lpq

with default 400 inserts per transaction blocks
$ gcc -DTRANSACTION -o tester tester.c -lpq

with 200 inserts per transaction blocks
$ gcc -DTRANSACTION -DINSERTPERTRANSACTION=200 -o tester tester.c -lpq

I do concurrent tests by starting seperate tester programs from 
different xterm windows.

>>Changed indexes are more suitable for hash type.
>>    
>>
>
>Are they?  How many distinct values are there in those columns?
>I suspect that your test may be stressing the case where only a few hash
>buckets are used and each bucket chain gets to be very long.
>  
>
The biggest one gets 200 distinct values, the others are 5, and 10. More 
information is in "tester.c" where INSERT query string is built.

Regards,

-sezai

Attachment: tester.c
Description: text/x-c (3.7 KB)
Attachment: schema.sql
Description: text/x-sql (626 bytes)

In response to

Responses

pgsql-general by date

Next:From: Rajat KatyalDate: 2004-02-28 08:36:54
Subject: Re: value too long error
Previous:From: Uwe C. SchroederDate: 2004-02-28 07:16:42
Subject: Re: value too long error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group