-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Khalid Baheyeldin schrieb:

Well, I did some benchmarks, and they show that, out of the box at least, MySQL is faster.

Here they are:

http://2bits.com/articles/benchmarking-postgresql-vs-mysql-performance-using...

One benchmark was for a concurrency of 1:

ab -n1 -c1000 http://mysql.example.com/

This seems wrong in some way. Cheers, Gerhard -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHSwggfg6TFvELooQRAiNbAJ0YRH5SG7DOXzcvtlRnOcV5ADlvvQCfRugS 4VjfGK6IdkivgXC55pGpX8o= =j2eB -----END PGP SIGNATURE-----

Two Thoughts: 1) MySQL 5.1 is supposed to be faster than MySQL 5.0. 2) Isn't the more accurate feature comparison InnoDB to PostgreSQL. MyISAM is a more light weight engine. InnoDB is more comparable to PostgreSQL with regard to features. Row level locking and that kind of thing. Matt Quoting Khalid Baheyeldin <kb@2bits.com>:

Well, I did some benchmarks, and they show that, out of the box at least, MySQL is faster.

Here they are:

http://2bits.com/articles/benchmarking-postgresql-vs-mysql-performance-using...

On Nov 25, 2007 1:24 PM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:

...

I just found this:

Converted the site from MySQL to PostgreSQL

http://jamonation.com/node/734

it is not explicitly said but the site should run drupal.

-- Ivan Sergio Borgonovo http://www.webthatworks.it

-- Khalid M. Baheyeldin 2bits.com http://2bits.com Drupal optimization, development, customization and consulting.

matt@mattfarina.com wrote:

2) Isn't the more accurate feature comparison InnoDB to PostgreSQL. MyISAM is a more light weight engine. InnoDB is more comparable to PostgreSQL with regard to features. Row level locking and that kind of thing. Agreed but simulating real load conditions involving the appropriate number of read to writes and bringing the effects/benefits/tradeoffs of row level locking and other variosu storage mechanisms etc is very difficult. Khalid was just lookign to comapere what you could expect OOTB under both systems supported by drupal.

Everyones individual use varies but my understanding of the paradigm is that MySQL will continue to excel with its MyISAM engine at sites targeted towards a read only nature (newspapaers, publishing mediums, etc) due to query caching and other various table reading efficiencies. Heavily interactive site that utilize alot of table writes and updates will begin to benefit more form the row level locking and advanced functionality (transactions, etc) of more complicated storage engines (InnoDB, postgresql, etc). Like most decisions in life it is seemingly about a trade off and sits on a nice continuum where many or most of use sit where there isnt a huge difference either way ;). -- Michael Favia michael@favias.org tel. 512.585.5650 http://michael.favias.org

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Bill Moran schrieb:

(MySQL, for example is faster at executing simply SELECT queries than anything else out there, whereas PostgreSQL is faster at complex joins ... if Drupal fetches related data from 5 tables in five separate SELECTS instead of 1 big SELECT with a bunch of joins, then it will always be faster on MySQL).

Drupal usually goes for joins rather than multiple single selects. Cheers, Gerhard -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHSxOifg6TFvELooQRAnw8AKCAnN5yBGC5lzZaiMvAlU4+Jp0SjgCaAnPu zRqdT1Vt/Z+9nzF9vehyxrk= =Ot4G -----END PGP SIGNATURE-----

Bill Moran wrote:

In response to Gerhard Killesreiter <gerhard@killesreiter.de>: My point is: a) Let me send you a tweaked postgresql.conf file and well find out whether the performance difference is simply a matter of untuned configuration and ... b) Let me have the query log after the test and I'll see if I can identify any queries that could be improved, then someone else can test to see if the improved queries are acceptable from the MySQL standpoint.

I think this misses the point of the experiment which was to see which ran faster out of the box without optimization, not which can be made to run faster (which is another useful experiment but much more complicated loads, read/write/update ratio, transactions, etc). On the other hand i completely agree with your points above and realize that those individuals most concerned with performance should have or desire the knowledge to properly configure the database engine/server. Unfortunately this isnt a black box many people like to touch readily. I'm all for making things go faster i just think this test wasn't meant to prove database superiority or anything like that and that is what this seems to have morphed into. -- Michael Favia michael@favias.org tel. 512.585.5650 http://michael.favias.org

In response to Michael Favia <michael@favias.org>:

Bill Moran wrote:

...

In response to Gerhard Killesreiter <gerhard@killesreiter.de>: My point is: a) Let me send you a tweaked postgresql.conf file and well find out whether the performance difference is simply a matter of untuned configuration and ... b) Let me have the query log after the test and I'll see if I can identify any queries that could be improved, then someone else can test to see if the improved queries are acceptable from the MySQL standpoint.

I think this misses the point of the experiment which was to see which ran faster out of the box without optimization, not which can be made to run faster (which is another useful experiment but much more complicated loads, read/write/update ratio, transactions, etc).

And my point is that "out of the box" performance is an oxymoron with PostgreSQL and isn't even worth testing. It's admittedly bad ... this is a known issue with PostgreSQL. If you shoot the horse again, all you do is waste bullets, it's already dead.

On the other hand i completely agree with your points above and realize that those individuals most concerned with performance should have or desire the knowledge to properly configure the database engine/server. Unfortunately this isn't a black box many people like to touch readily.

But there's more to it than that ... I want to _help_ improve this. I don't have a set up to test myself yet (hopefully after the first of the year, but I won't know until then) ... However, if someone is willing to feed me log output, I can help by identifying what is slow and methods that might speed it up. Frankly, I suspect this will help Drupal performance _overall_ on all database platforms.

I'm all for making things go faster i just think this test wasn't meant to prove database superiority or anything like that and that is what this seems to have morphed into.

That's the way most people read it and, quite frankly, I don't care. I'm interested in improving the performance of Drupal, the performance of PostgreSQL, and the performance of Drupal running on PostgreSQL. I can help with that, but trying to tune Drupal queries when PostgreSQL isn't tuned for the hardware it's running on is a recipe for wasted time. I'm not interested in proving that PostgreSQL is better than MySQL, I'm interested in _making_ PostgreSQL better than MySQL. To do that, I need data. Unfortunately, "PostgreSQL's default config performs poorly" isn't helpful data ;) -- Bill Moran http://www.potentialtech.com

On Nov 26, 2007 1:52 PM, Bill Moran <wmoran@potentialtech.com> wrote:

My point is: a) Let me send you a tweaked postgresql.conf file and well find out whether the performance difference is simply a matter of untuned configuration and ...

I will post them for all to see, maybe someone will point out something else. These are the non comment lines in postgresql.conf hba_file = '/etc/postgresql/8.2/main/pg_hba.conf' ident_file = '/etc/postgresql/8.2/main/pg_ident.conf' external_pid_file = '/var/run/postgresql/8.2-main.pid' listen_addresses = 'localhost' port = 5432 max_connections = 100 unix_socket_directory = '/var/run/postgresql' shared_buffers = 24MB max_fsm_pages = 153600 log_line_prefix = '%t ' stats_row_level = on autovacuum = on datestyle = 'iso, dmy' lc_messages = 'en_CA.UTF-8' lc_monetary = 'en_CA.UTF-8' lc_numeric = 'en_CA.UTF-8' lc_time = 'en_CA.UTF-8' For MySQL, this is the default delivered with Ubuntu (and I imagine Debian would be close). [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking bind-address = 127.0.0.1 key_buffer = 16M max_allowed_packet = 16M thread_stack = 128K thread_cache_size = 8 query_cache_limit = 1M query_cache_size = 16M log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M skip-bdb [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/ Note that there are no config files in the includedir. What I normally do (not for this benchmark, but for tuning) is: - Comment out the bin-log lines (log_bin, expire_logs_days, max_bin_log_size). This takes of some load, but more importantly saves disk space on some starved VPSs. - Add skip-innodb, which saves some 100MB of RAM for the MySQL. These are irrelevant to the benchmark at hand anyway, but is useful to know. For larger web sites, I use a configuration similar to the one listed here: http://2bits.com/articles/mysql-my-cnf-configuration-for-a-large-drupal-site...

b) Let me have the query log after the test and I'll see if I can identify any queries that could be improved, then someone else can test to see if the improved queries are acceptable from the MySQL standpoint.

-- Khalid M. Baheyeldin 2bits.com http://2bits.com Drupal optimization, development, customization and consulting.

On Nov 26, 2007 4:41 PM, Earnie Boyd <earnie@users.sourceforge.net> wrote:

Quoting Khalid Baheyeldin <kb@2bits.com>:

...

- Add skip-innodb, which saves some 100MB of RAM for the MySQL.

Speaking of innodb; I've been testing with it as well. InnoDB can be slightly faster with selects that MyISAM and provides for row locking which you can't do with MyISAM.

With some pitfalls. When you do a SELECT COUNT(*) FROM ... it is very slow on InnoDB. MyISAM just consults the index and gets you the number instantly. On InnoDB, the index is co-resident with the data, not separate, hence it is slow. So, most of the queries in Drupal that need to know the number of rows and do SELECT COUNT(*) are slower. For example pager_query() stuff. Also, explicit table locking is gone in Drupal 6 (at least for core), so both MyISAM and InnoDB will (should?) benefit from that. -- Khalid M. Baheyeldin 2bits.com http://2bits.com Drupal optimization, development, customization and consulting.

If you're not using innodb, it makes perfect sense to disable it, assuming you don't have memory to burn. Those who are using it might want to go the opposite route, default_table_type = INNODB --mark On Nov 26, 2007 4:05 PM, John Handelaar <john@userfrenzy.com> wrote:

On Nov 26, 2007 7:09 PM, Khalid Baheyeldin <kb@2bits.com> wrote:

...

What I normally do (not for this benchmark, but for tuning) is:

Add skip-innodb, which saves some 100MB of RAM for the MySQL.

That's a borderline-reckless thing to say around here.

Not using InnoDB is one thing -- and good luck with that when you've got lots of users, lots of comments, and some forums -- but actually removing the option at the server strikes me as more than a little foolish.

Incidentally, there are lots of places where Drupal could use transactions when they're available. user_add and node_save would both be a lot more DB-crash-resistant, for starters.

jh

On Tue, 27 Nov 2007 00:05:29 +0000, "John Handelaar" <john@userfrenzy.com> wrote:

On Nov 26, 2007 7:09 PM, Khalid Baheyeldin <kb@2bits.com> wrote:

...

What I normally do (not for this benchmark, but for tuning) is:

Add skip-innodb, which saves some 100MB of RAM for the MySQL.

That's a borderline-reckless thing to say around here.

Not using InnoDB is one thing -- and good luck with that when you've got lots of users, lots of comments, and some forums -- but actually removing the option at the server strikes me as more than a little foolish.

Incidentally, there are lots of places where Drupal could use transactions when they're available. user_add and node_save would both be a lot more DB-crash-resistant, for starters.

jh

The problem is that transaction support is not universal. In MySQL, for example, if you roll back a transaction it will roll back but throw a warning on any MyISAM tables that were affected, so unless you're using no MyISAM tables the rollback is not actually complete or atomic. If you're on shared hosting, 95% chance you're on MyISAM. David Strauss has been working on a transaction abstraction that should degrade gracefully, and I'm hoping to get that into the D7 database overhaul in some form or another. We need to first figure out how we're going to handle MySQL's multi-engine weirdness, though. --Larry Garfield

On Monday 26 November 2007, Earnie Boyd wrote:

...

...

Incidentally, there are lots of places where Drupal could use transactions when they're available. user_add and node_save would both be a lot more DB-crash-resistant, for starters.

jh

The problem is that transaction support is not universal. In MySQL, for example, if you roll back a transaction it will roll back but throw a warning on any MyISAM tables that were affected, so unless you're using no MyISAM tables the rollback is not actually complete or atomic. If you're on shared hosting, 95% chance you're on MyISAM.

If the choice is to use InnoDB then the port should default to InnoDB. Another port can be used for a MyISAM default.

I have no idea what you're talking about. :-) MySQL can be run quite happily with some tables InnoDB, some MyISAM. You can also run a Master/slave configuration with the Master InnoDB and the slaves MyISAM (or vice versa, although why you'd want to I have no idea). That means sometimes a transaction may not rollback properly, and other times it will. We can't have "two Drupals", one that uses transactions and one that doesn't (if that's what you mean by port, since TCP port wouldn't make any sense in this context). Our choices are: 1) Don't use transactions. 2) Use transactions and silently ignore it when a rollback doesn't actually roll back, and/or file a watchdog entry but otherwise don't do anything. 3) Allow the user to explicitly flag if a connection should use transactions, defaulting no, and if not then starting a transaction has no effect and neither does rolling back or committing. 4) Don't support database configurations that don't fully support transactions. #4 is not an option, naturally. #1 seems like a waste, but it is what we do now. That leaves #2 and #3 as alternatives. I am not entirely sure which route is least lame at the moment. :-) -- Larry Garfield AIM: LOLG42 larry@garfieldtech.com ICQ: 6817012 "If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it." -- Thomas Jefferson

Incidentally, there are lots of places where Drupal could use transactions when they're available. user_add and node_save would both be a lot more DB-crash-resistant, for starters.

I was really silent until now but enough is enough. In my not-brief-enough liasion with postgresql we learned that for a web application that casually writes the DB, postgresql seemed to be a very poor choice exactly because it was firing a transaction every time. Unless I run a banking application I do not want to know about transactions. I gave up the notion of dropping postgresql from core (I instead raised a compromise which everybody misunderstood probably deliberately and totally not reacted to it but we can discuss the next time postgresql helds a patch) but enough is enough -- I will be very resilient against adding transactions. I want a fast Drupal, sorry.

Whether transactions enchance or hurt performance depends on the situation. It is an error to assume that transactions equal slow performance. -----Original Message----- From: "Karoly Negyesi" <karoly@negyesi.net> Date: Tue, 27 Nov 2007 11:46:36 To:development@drupal.org Subject: Re: [development] drupal on postgresql benchmark

Incidentally, there are lots of places where Drupal could use transactions when they're available. user_add and node_save would both be a lot more DB-crash-resistant, for starters.

I was really silent until now but enough is enough. In my not-brief-enough liasion with postgresql we learned that for a web application that casually writes the DB, postgresql seemed to be a very poor choice exactly because it was firing a transaction every time. Unless I run a banking application I do not want to know about transactions. I gave up the notion of dropping postgresql from core (I instead raised a compromise which everybody misunderstood probably deliberately and totally not reacted to it but we can discuss the next time postgresql helds a patch) but enough is enough -- I will be very resilient against adding transactions. I want a fast Drupal, sorry.

On Nov 26, 2007 1:36 PM, Bill Moran <wmoran@potentialtech.com> wrote:

I have one additional question. You say the following: "MySQL's query cache makes its performance better. This can be demonstrated by restarting MySQL, then visiting the home page of the site and seeing the query time in devel's output. However this is not as marked as what PostgreSQL takes."

Could you explain/clarify that statement?

What I meant is that even when the query cache does not kick in, MySQL is still faster than PostgreSQL. An easy way to do that without fiddling with MySQL variables and such is to restart mysql, then hit the front page, see what devel says, then hit it again and see the difference. That difference is due to the query cache. With query cache not yet caching any queries Page execution time was *92.74* ms. Executed *98* queries in *30.04* milliseconds. With page's queries cached in the query cache Page execution time was *63.61* ms. Executed *98* queries in *10.38* milliseconds. Still, 93 ms is faster than PostgreSQL's 130 ms. -- Khalid M. Baheyeldin 2bits.com http://2bits.com Drupal optimization, development, customization and consulting.