Re: Pgsql (and mysql) benchmark on T2000/Solaris and some - Mailing list pgsql-performance

Hi Jignesh,

The settings from that 'special T2000 dvd' differed from the recommended
settings on the website you provided. But I don't see much difference in
performance with any of the adjustments, it appears to be more or less
the same.

Here are a few iostat lines by the way:

     sd0           sd1           sd2           nfs1           cpu
kps tps serv  kps tps serv  kps tps serv  kps tps serv   us sy wt id
   7   1   12  958  50   35    0   0    7    0   0    0   13  1  0 85
   0   0    0  2353 296    3    0   0    0    0   0    0   92  7  0  1
   0   0    0  2062 326    2    0   0    0    0   0    0   93  7  0  0
   1   1    1  1575 350    0    0   0    0    0   0    0   92  7  0  1
   0   0    0  1628 362    0    0   0    0    0   0    0   92  8  0  1

It appears to be doing a little less kps/tps on sd1 when I restore my
own postgresql.conf-settings. (default wal/checkpoints, 20k buffers, 2k
work mem).

Is it possible to trace the stack's for semsys, like the memcpy-traces,
or are those of no interest here?

Best regards,

Arjen


On 16-5-2006 17:52, Jignesh K. Shah wrote:
> Hi Arjen,
>
> Can you send me my colleagues's names in a private email?
>
> One of the drawbacks of the syscall.d script is relative timings and
> hence if system CPU usage is very low, it gives the relative weightage
> about what portion in that low is associated with what call.. So even if
> you have say..1% system time.. it says that most of it was IO related or
> semsys related. So iostat output with -c option to include CPU times
> helps to put it in  the right perspective.
>
>
> Also do check the tunables mentioned and make sure they are set.
>
> Regards,
> Jignesh
>
>
> Arjen van der Meijden wrote:
>
>> Hi Jignesh,
>>
>> Jignesh K. Shah wrote:
>>
>>> Hi Arjen,
>>>
>>> Looking at your outputs...of syscall and usrcall it looks like
>>>
>>> * Spending too much time in semsys .... which means you have too many
>>> connections and they are contending to get a lock.. which is
>>> potentially the WAL log lock
>>>
>>> * llseek is high which means you can obviously gain a bit with the
>>> right file system/files tuning by caching them right.
>>>
>>> Have you set the values for Solaris for T2000 tuned for Postgresql?
>>
>>
>> Not particularly, we got a "special T2000 Solaris dvd" from your
>> colleagues here in the Netherlands and installed that (actually one of
>> your colleagues did). Doing so all the "better default"
>> /etc/system-settings are supposed to be set. I haven't really checked
>> that they are, since two of your colleagues have been working on it
>> for the mysql-version of the benchmark and I assumed they'd have
>> verified that.
>>
>>> Check out the tunables from the following URL
>>>
>>> http://www.sun.com/servers/coolthreads/tnb/applications_postgresql.jsp
>>>
>>> Try specially the /etc/system and postgresql.conf changes  and see if
>>> it changes/improves your performance.
>>
>>
>> I will see that those tunables are verified to be set.
>>
>> I am a bit surprised though about your remarks, since they'd point at
>> the I/O being in the way? But we only have about 600k/sec i/o
>> according to vmstat. The database easily fits in memory.
>> In total I logged about 500k queries of which only 70k where altering
>> queries, of which almost all where inserts in log-tables which aren't
>> actively read in this benchmark.
>>
>> But I'll give it a try.
>>
>> Best regards,
>>
>> Arjen
>>
>>>
>>> Arjen van der Meijden wrote:
>>>
>>>> Hi List,
>>>>
>>>> In the past few weeks we have been developing a read-heavy
>>>> mysql-benchmark to have an alternative take at
>>>> cpu/platform-performance. Not really to have a look at how fast
>>>> mysql can be.
>>>>
>>>> This benchmark runs on mysql 4.1.x, 5.0.x and 5.1.x and is modelled
>>>> after our website's production database and the load generated on it
>>>> is modelled after a simplified version of our visitor behaviour.
>>>>
>>>> Long story short, we think the test is a nice example of the
>>>> relatively lightweight, read-heavy webapplications out there and
>>>> therefore decided to have a go at postgresql as well.
>>>> Of course the queries and indexes have been adjusted to (by our
>>>> knowledge) best suit postgresql, while maintaining the same output
>>>> to the application/interface layer. While the initial structure only
>>>> got postgresql at about half the performance of mysql 4.1.x, the
>>>> current version of our postgresql-benchmark has quite similar
>>>> results to mysql 4.1.x, but both are quite a bit slower than 5.0.x
>>>> (I think its about 30-40% faster).
>>>>
>>>> Since the results from those benchmarks are not yet public (they
>>>> will be put together in a story at our website), I won't go into too
>>>> much details about this benchmark.
>>>>
>>>> Currently we're having a look at a Sun T2000 and will be looking at
>>>> will be looking at other machines as well in the future. We are
>>>> running the sun-release of postgresql 8.1.3 on that T2000 now, but
>>>> are looking at compiling the cvs-head version (for its
>>>> index-root-cache) somewhere this week.
>>>>
>>>> My guess is there are a few people on this list who are interested
>>>> in some dtrace results taken during our benchmarks on that T2000.
>>>> Although my knowledge of both Solaris and Dtrace are very limited, I
>>>> already took some samples of the system and user calls. I used
>>>> Jignesh Shah's scripts for that:
>>>> http://blogs.sun.com/roller/page/jkshah?entry=profiling_postgresql_using_dtrace_on
>>>>
>>>>
>>>> You can find the samples here:
>>>> http://achelois.tweakers.net/~acm/pgsql-t2000/syscall.log
>>>> http://achelois.tweakers.net/~acm/pgsql-t2000/usrcall.log
>>>>
>>>> And I also did the memcpy-scripts, here:
>>>> http://achelois.tweakers.net/~acm/pgsql-t2000/memcpysize.log
>>>> http://achelois.tweakers.net/~acm/pgsql-t2000/memcpystack.log
>>>> (this last log is 3.5MB)
>>>>
>>>> If anyone is interested in some more dtrace results, let me know
>>>> (and tell me what commands to run ;-) ).
>>>>
>>>> Best regards,
>>>>
>>>> Arjen
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 3: Have you checked our extensive FAQ?
>>>>
>>>>               http://www.postgresql.org/docs/faq
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 6: explain analyze is your friend
>>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>
>