Re: Acclerating INSERT/UPDATE using UPS - Mailing list pgsql-hackers

Bruce,

Let me answer to your question.

Question 1: How much faster is it ?

To answer to your question,  I measured execution times of INSERT 
operations on concurrent accesses on dual-core x 2 CPU (each 2.80GHz),
In the experiment, each client issues 5000 INSERT operations concurrently.
The following shows the average times (seconds) of 5 measurements.
#Cli: The number of concurrent clients
P-D: PostgreSQL Default (i.e. usual file system)
P-T: PostgreSQL tmpfs
S-D: Sigres Default (i.e. usual file system)
S-T: Sigres tmpfs
P-T/S-T Improve ratio of S-T to P-T

The result shows S-T is 10% to 18% faster than P-T.
Thus my answer to your question is 10% to 18% when concurrency is from1 
to 100.

#Cli    P-D    P-T    S-D    S-T    P-T/S-T  1   1.72   0.50   0.46   0.45       1.10  2   2.87   0.62   0.58   0.54
  1.15  3   3.08   0.89   0.77   0.77       1.15  4   3.14   0.98   0.86   0.84       1.16  5   3.31   1.23   1.09
1.07      1.15  6   3.57   1.44   1.31   1.27       1.14  7   3.91   1.68   1.51   1.48       1.14  8   4.49   1.89
1.71  1.67       1.13  9   4.78   2.21   1.9    1.92       1.15 10   5.33   2.47   2.22   2.14       1.15 20  11.50
5.66  5.16   4.86       1.16 50  32.96  16.54  14.92  13.97       1.18100  79.60  43.71  39.55  38.38       1.14
 


Question 2: Is that worth adding extra code to improve it ?

Yes, I think it is worth. It is because in the case of commercial DBMS,
only 5% improvement is achieved with version-up.

BTW, I and a friend of mine try to design & implement a parallel access 
way to the wal buffer on a shared memory. I think this is promising 
direction since WALInsertLock is more frequently issued than 
WALWriteLock, and the number of CPU-cores will increase definitely.


-- Hideyuki

Bruce Momjian wrote:
> Hideyuki Kawashima wrote:
>   
>> Bruce,
>>
>> Thanks for your comments, and let me answer to your question.
>> Sigres is *not* significantly faster than just creating a file system on 
>> the permanent memory and putting xlog on there.
>> Sigres is slightly faster than the case because each backend does not 
>> call XLogWrite while bgWriter does.
>>     
>
> The question then is how much faster is it, and is that worth adding
> extra code to improve it.
>
> ---------------------------------------------------------------------------
>
>
>   
>> -- Hideyuki
>>
>> Bruce Momjian wrote:
>>     
>>> Tom Lane wrote:
>>>   
>>>       
>>>> Gene <genekhart@gmail.com> writes:
>>>>     
>>>>         
>>>>> ... just my two cents. on a side note, would putting the wal on a
>>>>> tmpfs partition give you something similar?
>>>>>       
>>>>>           
>>>> Indeed, I'm wondering why one needs to hack the Postgres core to throw
>>>> away data integrity guarantees; there are plenty of ways to do that
>>>> already :-(.  Hideyuki-san has not explained exactly what integrity
>>>> assumptions he wants to make or not make.  I'm surely willing to listen
>>>> to supporting a different set of assumptions than we currently use, but
>>>> I'd like to see a clear explanation of what assumptions are being made
>>>> and why they represent a useful case.
>>>>     
>>>>         
>>> I am unsure why Sigres is significantly faster than just creating a file
>>> system on the permanent memory and putting xlog on there.
>>>
>>>   
>>>       
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>>        subscribe-nomail command to majordomo@postgresql.org so that your
>>        message can get through to the mailing list cleanly
>>     
>
>   

-- 
Hideyuki Kawashima (Ph.D), University of Tsukuba,
Graduate School of Systems and Information Engineering
Assistant Professor, TEL: +81-29-853-5322