drupal on postgresql benchmark
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
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.
-----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-----
On Nov 26, 2007 12:53 PM, Gerhard Killesreiter <gerhard@killesreiter.de> wrote:
-----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
Thanks fixed the typo. ab -c1 -n1000 ... and ab -c5 -n1000 ... -- Khalid M. Baheyeldin 2bits.com http://2bits.com Drupal optimization, development, customization and consulting.
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.
Since these are Drupal-specific benchmarks, and Drupal doesn't utilize any of the ACID type stuff (even though it would be nice in D7), I think it is relevant to compare the out of the box configs like MyISAM. Speaking of which, maybe adding ACID isn't that hard - keep track of what is InnoDB and let the db include files send a START TRANSACTION when it is possible (speaking in a frame of PHP5 __construct()/__destruct() perhaps). matt@mattfarina.com wrote:
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
In response to "Khalid Baheyeldin" <kb@2bits.com>:
Well, I did some benchmarks, and they show that, out of the box at least, MySQL is faster.
Unfortunately, that really doesn't mean anything. I don't know about the configs that ship with Ubuntu, but the PGDG configs produce lousy performance. It's a known factor that the default configs are not tuned for performance and actually perform pretty badly. Based on your "Future considerations" section, if you send me the specs for the hardware this is running, and a copy of the postgresql.conf file, I'll send you a tweaked file to retest with. There is, however a chance that PostgreSQL simply isn't going to run as fast as MySQL. This is, in part, because Drupal is written (primarily) by MySQL people. I'm willing to bet that most of them don't even know when they're taking advantage of things in MySQL that are unusually fast (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). In any event, once you've run the tests, I'd like a copy of your PostgreSQL query log, which I can then analyze to see if there's any way to improve speed. Hopefully there will be ways to optimize queries that doesn't hurt MySQL's performance. 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? -- Bill Moran http://www.potentialtech.com
-----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-----
In response to Gerhard Killesreiter <gerhard@killesreiter.de>:
-----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.
*shrug* 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. -- Bill Moran http://www.potentialtech.com
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
On Nov 26, 2007 2:00 PM, Michael Favia <michael@favias.org> wrote:
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).
Michael You are right. This measures the out of the box experience. Tuning a site is labor intensive, expensive and on going. What people get out of the box is very important.
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.
This is why my comment at the end of the article which Bill picked up. I want people to share experiences on tuning PostgreSQL, so we can have a more level playing field with more complex setups. -- Khalid M. Baheyeldin 2bits.com http://2bits.com Drupal optimization, development, customization and consulting.
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
Quoting Bill Moran <wmoran@potentialtech.com>:
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 ;)
I've recently been playing with index modifications for improvement of the DB of MySql. I haven't gotten to the point of opening a ticket for all of the changes yet but I have at least one open. Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.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.
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. Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
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.
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
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 Nov 26, 2007 7: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.
If one is not using the feature, why bother keeping in memory? How about this for size: 16,000 nodes 124,000 comments 26,000 users 650,000 page views a day (peak day over 930,000) All running on a single box: dual core Opteron and 2GB of RAM. Yes, all MyISAM! Is that enough? -- Khalid M. Baheyeldin 2bits.com http://2bits.com Drupal optimization, development, customization and consulting.
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
Quoting Larry Garfield <larry@garfieldtech.com>:
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.
If the choice is to use InnoDB then the port should default to InnoDB. Another port can be used for a MyISAM default. Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
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
Quoting Larry Garfield <larry@garfieldtech.com>:
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).
My point is, if you want InnoDB for transactions then don't allow MyISAM tables within the the DB to prevent your point that a transaction may not rollback properly. I understand the MyISAM and InnoDB coexist within MySQL table structures but allowing it to happen is a different matter.
Our choices are:
1) Don't use transactions.
Covered in point 3.
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.
If transactions are used they should only be used for the insert/update/delete methods. The read only selects for display purposes should not be a transaction because there would be nothing to roll back.
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.
This might keep the transaction nay sayers happy.
4) Don't support database configurations that don't fully support transactions.
As you say, not an option.
#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. :-)
As I said, if transactions are allowed then the entire database needs to support it, not just some tables. BTW, when I converted my tables from MyISAM to InnoDB the display of the nodes happen quicker. I don't have supporting data yet but I plan to do just that. Of course there are no transactions in the current code base so they are not even a factor. Drupal version is 5.3. Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
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.
I guess I'm surprised anybody needed a benchmark to discover that out of the box, Drupal is faster on MySQL. After all, we've been spent years fine tuning Drupal to run fast on MySQL, and done nothing of the sort for any other database. I've personally spent many hours helping tweak Drupal's queries and database definitions to make it run fast on MySQL, and there are others who have devoted more time than I have.
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.
participants (14)
-
Bill Moran -
Chris Johnson -
David Norman -
David Strauss -
Earnie Boyd -
Gerhard Killesreiter -
Ivan Sergio Borgonovo -
John Handelaar -
Karoly Negyesi -
Khalid Baheyeldin -
Larry Garfield -
mark burdett -
matt@mattfarina.com -
Michael Favia