Thread: PostgreSQL and sql-bench
Hi After upgrading DBI and DBD::Pg, this benchmark still picks MySQL as the winner (at least on Linux RH3 on a Dell 1875 server with 2 hyperthreaded 3.6GHz CPUs and 4GB RAM). I've applied the following parameters to postgres.conf: max_connections = 500 shared_buffers = 3000 work_mem = 100000 effective_cache_size = 3000000000 Most queries still perform slower than with MySQL. Is there anything else that can be tweaked or is this a limitation of PG or the benchmark? Thanks. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Thu, 2006-09-21 at 07:52 -0700, yoav x wrote: > Hi > > After upgrading DBI and DBD::Pg, this benchmark still picks MySQL as the winner (at least on Linux > RH3 on a Dell 1875 server with 2 hyperthreaded 3.6GHz CPUs and 4GB RAM). > I've applied the following parameters to postgres.conf: > > max_connections = 500 > shared_buffers = 3000 > work_mem = 100000 > effective_cache_size = 3000000000 > > Most queries still perform slower than with MySQL. > Is there anything else that can be tweaked or is this a limitation of PG or the benchmark? As mentioned by others, you are using a benchmark that is slanted towards MySQL.
Not to offend, but since most of us are PG users, we're not all that familiar with what the different tests in MySQL's sql-bench benchmark do. So you won't get very far by saying "PG is slow on benchmark X, can I make it faster?", because that doesn't include any of the information we need in order to help. Specifics would be nice, including at least the following: 1. Which specific test case(s) would you like to try to make faster? What do the table schema look like, including indexes and constraints? 2. What strategy did you settle on for handling VACUUM and ANALYZE during the test? Have you confirmed that you aren't suffering from table bloat? 3. What are the actual results you got from the PG run in question? 4. What is the size of the data set referenced in the test run? -- Mark Lewis On Thu, 2006-09-21 at 07:52 -0700, yoav x wrote: > Hi > > After upgrading DBI and DBD::Pg, this benchmark still picks MySQL as the winner (at least on Linux > RH3 on a Dell 1875 server with 2 hyperthreaded 3.6GHz CPUs and 4GB RAM). > I've applied the following parameters to postgres.conf: > > max_connections = 500 > shared_buffers = 3000 > work_mem = 100000 > effective_cache_size = 3000000000 > > Most queries still perform slower than with MySQL. > Is there anything else that can be tweaked or is this a limitation of PG or the benchmark? > > Thanks. > > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
Hi. Do you compare apples to apples? InnoDB tables to PostgreSQL? Are all needed indexes available? Are you sure about that? What about fsync? Does the benchmark insert a lot of rows? Have you tested placing the WAL on a separate disk? Is PostgreSQL logging more stuff? Another thing: have you analyzed the tables? Have you tested higher shared_buffers? And the last thing: there are lies, damn lies and benchmarks. What does a benchmark, which might be optimized for one DB, help you with your own db workload? There are soooo many things that can go wrong with a benchmark if you don't have real knowledge on how to optimize both DBMS that it is just worthless to use it anyway if you don't have the knowledge ... PostgreSQL outperforms MySQL in our environment in EVERY situation needed by the application. So, does the benchmark represent your work load? Does the benchmark result say anything for your own situation? Or is this all for the sake of running a benchmark? cug On 9/21/06, yoav x <yoav112003@yahoo.com> wrote: > Hi > > After upgrading DBI and DBD::Pg, this benchmark still picks MySQL as the winner (at least on Linux > RH3 on a Dell 1875 server with 2 hyperthreaded 3.6GHz CPUs and 4GB RAM). > I've applied the following parameters to postgres.conf: > > max_connections = 500 > shared_buffers = 3000 > work_mem = 100000 > effective_cache_size = 3000000000 > > Most queries still perform slower than with MySQL. > Is there anything else that can be tweaked or is this a limitation of PG or the benchmark? > > Thanks. -- PostgreSQL Bootcamp, Big Nerd Ranch Europe, Nov 2006 http://www.bignerdranch.com/news/2006-08-21.shtml
yoav x <yoav112003@yahoo.com> writes:
> I've applied the following parameters to postgres.conf:
> max_connections = 500
> shared_buffers = 3000
> work_mem = 100000
> effective_cache_size = 3000000000
Please see my earlier reply --- you ignored at least
checkpoint_segments, which is critical, and perhaps other things.
Don't forget also that testing mysql/myisam against fsync = on
is inherently unfair.
regards, tom lane
On Thu, 2006-09-21 at 07:52 -0700, yoav x wrote:
> Hi
>
> After upgrading DBI and DBD::Pg, this benchmark still picks MySQL as the winner (at least on Linux
> RH3 on a Dell 1875 server with 2 hyperthreaded 3.6GHz CPUs and 4GB RAM).
> I've applied the following parameters to postgres.conf:
>
> max_connections = 500
> shared_buffers = 3000
That's a low setting. 3000*8192 = 24MB. This should probably be closer
to 25% total memory, or 1GB, or 131072 shared buffers (however, that's
just a rule of thumb, there may be a better setting).
> work_mem = 100000
> effective_cache_size = 3000000000
That is a very high setting. effective_cache_size is measured in disk
pages, so if you want 3GB the correct setting is 393216.
>
> Most queries still perform slower than with MySQL.
> Is there anything else that can be tweaked or is this a limitation of PG or the benchmark?
>
As others have pointed out, sql-bench may not be a realistic benchmark.
The best way to examine performance is against real work.
Also, consider that relational databases were not developed to increase
performance. Things like filesystems are inherently "faster" because
they do less. However, relational databases make development of systems
of many applications easier to develop, and also make it easier to make
a well-performing application. If the benchmark isn't testing anything
that a filesystem can't do, then either:
(a) Your application could probably make better use of a relational
database; or
(b) The benchmark doesn't represent your application's needs.
Regards,
Jeff Davis
Regards,
Jeff Davis
On Thu, Sep 21, 2006 at 11:12:45AM -0400, Tom Lane wrote: > yoav x <yoav112003@yahoo.com> writes: > > I've applied the following parameters to postgres.conf: > > > max_connections = 500 > > shared_buffers = 3000 > > work_mem = 100000 > > effective_cache_size = 3000000000 You just told the database that you have 23G of storage. effective_cache_size is measured in blocks, which are normally 8K. > Please see my earlier reply --- you ignored at least > checkpoint_segments, which is critical, and perhaps other things. > > Don't forget also that testing mysql/myisam against fsync = on > is inherently unfair. Even with fsync = off, there's still a non-trivial amount of overhead brought on by MVCC that's missing in myisam. If you don't care about concurrency or ACIDity, but performance is critical (the case that the MySQL benchmark favors), then PostgreSQL probably isn't for you. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On 21-9-2006 23:49 Jim C. Nasby wrote: > Even with fsync = off, there's still a non-trivial amount of overhead > brought on by MVCC that's missing in myisam. If you don't care about > concurrency or ACIDity, but performance is critical (the case that the > MySQL benchmark favors), then PostgreSQL probably isn't for you. That depends on the required scalability (both in number of cpu's and in number of concurrent clients). In our benchmarks MySQL is beaten by PostgreSQL in a read-mostly environment with queries that are designed for MySQL, but slightly adjusted to work on PostgreSQL (for MySQL 5.0 and 5.1, about the same adjustments where needed). But for very low amounts of concurrent users, MySQL outperforms PostgreSQL. Have a look here: http://tweakers.net/reviews/646/10 and here: http://tweakers.net/reviews/638/4 As you can see both MySQL 5.0 and 4.1 start much higher for a few clients, but when you add more clients or more cpu's, MySQL scales less good and even starts dropping performance and soon is far behind compared to PostgreSQL. So for a web-application, PostgreSQL may be much better, since generally the only situation where you need maximum performance, is when you have to service a lot of concurrent visitors. But if you benchmark only with a single thread or do benchmarks that are no where near a real-life environment, it may show very different results of course. Best regards, Arjen van der Meijden
Hi I am not comparing Postgres to MyISAM (obviously it is not a very fair comparison) and we do need ACID, so all comparison are made against InnoDB (which now supports MVCC as well). I will try again with the suggestions posted here. Thanks. --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > yoav x <yoav112003@yahoo.com> writes: > > I've applied the following parameters to postgres.conf: > > > max_connections = 500 > > shared_buffers = 3000 > > work_mem = 100000 > > effective_cache_size = 3000000000 > > Please see my earlier reply --- you ignored at least > checkpoint_segments, which is critical, and perhaps other things. > > Don't forget also that testing mysql/myisam against fsync = on > is inherently unfair. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Sep 25, 2006, at 10:58 AM, yoav x wrote: > I am not comparing Postgres to MyISAM (obviously it is not a very > fair comparison) and we do need > ACID, so all comparison are made against InnoDB (which now supports > MVCC as well). I will try > again with the suggestions posted here. Make sure that you're not inadvertently disabling ACIDity in MySQL/ InnoDB; some options/performance tweaks will do that last I looked. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)