Re: [PERFORM] PostgreSQL and sql-bench
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[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] PostgreSQL and sql-bench
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 [EMAIL PROTECTED] wrote: yoav x [EMAIL PROTECTED] writes: I've applied the following parameters to postgres.conf: max_connections = 500 shared_buffers = 3000 work_mem = 10 effective_cache_size = 30 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL and sql-bench
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 = 10 effective_cache_size = 30 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] PostgreSQL and sql-bench
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 = 10 effective_cache_size = 30 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] PostgreSQL and sql-bench
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 s 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 [EMAIL PROTECTED] 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 = 10 effective_cache_size = 30 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 ---(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
Re: [PERFORM] PostgreSQL and sql-bench
yoav x [EMAIL PROTECTED] writes: I've applied the following parameters to postgres.conf: max_connections = 500 shared_buffers = 3000 work_mem = 10 effective_cache_size = 30 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
Re: [PERFORM] PostgreSQL and sql-bench
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 = 10 effective_cache_size = 30 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL and sql-bench
On Thu, Sep 21, 2006 at 11:12:45AM -0400, Tom Lane wrote: yoav x [EMAIL PROTECTED] writes: I've applied the following parameters to postgres.conf: max_connections = 500 shared_buffers = 3000 work_mem = 10 effective_cache_size = 30 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[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] PostgreSQL and sql-bench
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
