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
