Re: Pgsql (and mysql) benchmark on T2000/Solaris and some - Mailing list pgsql-performance
From | Jignesh K. Shah |
---|---|
Subject | Re: Pgsql (and mysql) benchmark on T2000/Solaris and some |
Date | |
Msg-id | 446B3DE1.1060705@sun.com Whole thread Raw |
In response to | Re: Pgsql (and mysql) benchmark on T2000/Solaris and some (Arjen van der Meijden <acmmailing@tweakers.net>) |
List | pgsql-performance |
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 >>
pgsql-performance by date: