Re: Pgsql (and mysql) benchmark on T2000/Solaris and some

I have seen MemoryContextSwitchTo taking time before.. However I am not
sure why would it take so much CPU time?
Maybe that function does not work efficiently on Solaris?

Also I donot have much idea about slot_getattr.

Anybody else? (Other option is to use "collect -p $pid" experiments to
gather the data to figure out what instruction is causing the high CPU
usage) Maybe the Sun engineers out there can help out

-Jignesh

Arjen van der Meijden wrote:
> We have the 4 core machine. However, these numbers are taken during a
> benchmark, not normal work load. So the output should display the system
> being working fully ;)
>
> So its postgres doing a lot of work and you already had a look at the
> usrcall for that.
>
> The benchmark just tries to do the queries for "random page visits".
> This totals up to about some 80 different queries being executed with
> mostly random parameters. The workload is generated using php so there
> are no connection pools, nor prepared statements.
>
> The queries vary, but are all relatively lightweight queries with less
> than 6 or 7 joinable tables. Almost all queries can use indexes. Most
> tables are under a few MB of data, although there are a few larger than
> that. Most records are relatively small, consisting of mostly numbers
> (id's and such).
>
> The results presented here was with 25 concurrent connections.
>
> Best regards,
>
> Arjen
>
>
> Jignesh K. Shah wrote:
>
>> You usertime is way too high for T2000...
>>
>> If you have a 6 core machine with 24 threads, it says all 24 threads
>> are reported as being busy with iostat output.
>>
>> Best way to debug this is use
>>
>> prstat -amL
>> (or if you are dumping it in a file prstat -amLc > prstat.txt)
>>
>> and find the pids with high user cpu time and then use the usrcall.d
>> on few of those pids.
>>
>> Also how many database connections do you have and what's the type of
>> query run by each connection?
>>
>> -Jignesh
>>
>>
>>
>> Arjen van der Meijden wrote:
>>
>>> 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
>>>>
>>>>
>>>>
>>>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>