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

Поиск
Список
Период
Сортировка
От Arjen van der Meijden
Тема Re: Pgsql (and mysql) benchmark on T2000/Solaris and some
Дата
Msg-id 4469D986.6090300@tweakers.net
обсуждение исходный текст
Ответ на Re: Pgsql (and mysql) benchmark on T2000/Solaris and some  ("Jignesh K. Shah" <J.K.Shah@Sun.COM>)
Ответы Re: Pgsql (and mysql) benchmark on T2000/Solaris and some  ("Jignesh K. Shah" <J.K.Shah@Sun.COM>)
Список pgsql-performance
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
>

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: IMMUTABLE?
Следующее
От: "Jignesh K. Shah"
Дата:
Сообщение: Re: Pgsql (and mysql) benchmark on T2000/Solaris and some