Re: Acclerating INSERT/UPDATE using UPS

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.18
100 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(at)gmail(dot)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(at)postgresql(dot)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