Counterintuitive but true: Caching makes a high-traffic site slower
In the continuing absence of anywhere to discuss these performance edge-cases (more below)... I've already mentioned recently that MyISAM is the enemy of Drupal performance. Because it requires a full-table lock, if you have lots of active members logged in at once, the process of posting a comment causes page rendering to cease entirely for several seconds. Onto today's point: The same table-level locking is *lethal* on a heavily- trafficked site which has both a) Content which alters regularly (like a busy forum), and b) Caching turned on. Try turning it off if your slow query log starts showing cache-related things taking 90 seconds (!) or more (!!). And then take a backup of your database and change these table types to InnoDB format. And then start looking at delayed inserts. jh PS: Still no bigsite/performance forum. Have we decided that Drupal doesn't *need* to scale? I missed that meeting.
And then start looking at delayed inserts.
... and then at memcached. Nothing beats memcached for cached pages, I tell you! (OK, APC shared memory may but I have not dared to try it). Regards NK
I am a sql-clueless person. So can someone explain me why the --- we have myisam there, if it performs so bad? Ber Op donderdag 29 december 2005 18:24, schreef John Handelaar:
In the continuing absence of anywhere to discuss these performance edge-cases (more below)...
I've already mentioned recently that MyISAM is the enemy of Drupal performance. Because it requires a full-table lock, if you have lots of active members logged in at once, the process of posting a comment causes page rendering to cease entirely for several seconds.
Onto today's point:
The same table-level locking is *lethal* on a heavily- trafficked site which has both
a) Content which alters regularly (like a busy forum), and
b) Caching turned on.
Try turning it off if your slow query log starts showing cache-related things taking 90 seconds (!) or more (!!).
And then take a backup of your database and change these table types to InnoDB format.
And then start looking at delayed inserts.
jh
PS: Still no bigsite/performance forum. Have we decided that Drupal doesn't *need* to scale? I missed that meeting.
I've wondered this, too. A friend of mine (mysql admin for a very, very high-traffic site) was looking over a blog entry I made about some drupal-stuff, and his first words were, 'Dear God, don't use myisam!' I assume there's a good reason for it, but the drawbacks for high-load sites seem considerable. --Jeff
-----Original Message----- From: Bèr Kessels [mailto:ber@webschuur.com] Sent: Thursday, December 29, 2005 3:10 PM To: development@drupal.org Subject: Re: [development] Counterintuitive but true: Caching makes ahigh-traffic site slower
I am a sql-clueless person. So can someone explain me why the --- we have myisam there, if it performs so bad?
Ber
MyISAM is really fast compared to other engines. Only when you are running a very high traffic site does locking contention become an issue. For small to medium sites, it should be faster. MyISAM is the default engine for MySQL. The reason of defaulting to MySQL is mainly historical. Until recently ISPs would not support other engines, and everyone had to use MyISAM because that is what their ISP has. This is specially true in shared hosting. Nowadays, even shared hosting has InnoDB, which is ACID, has transaction, and row level locking. The future of InnoDB with MySQL is unclear though, now that Oracle has purchased InnoBase, the company that makes InnoDB. This could mean reduced revenue for MySQL AB which sells non-GPL versions of MySQL+InnoDB. We have to wait and see what happens here. Not sure about making it the default though ... On 12/29/05, Bèr Kessels <ber@webschuur.com> wrote:
I am a sql-clueless person. So can someone explain me why the --- we have myisam there, if it performs so bad?
Ber
Op donderdag 29 december 2005 18:24, schreef John Handelaar:
In the continuing absence of anywhere to discuss these performance edge-cases (more below)...
I've already mentioned recently that MyISAM is the enemy of Drupal performance. Because it requires a full-table lock, if you have lots of active members logged in at once, the process of posting a comment causes page rendering to cease entirely for several seconds.
Onto today's point:
The same table-level locking is *lethal* on a heavily- trafficked site which has both
a) Content which alters regularly (like a busy forum), and
b) Caching turned on.
Try turning it off if your slow query log starts showing cache-related things taking 90 seconds (!) or more (!!).
And then take a backup of your database and change these table types to InnoDB format.
And then start looking at delayed inserts.
jh
PS: Still no bigsite/performance forum. Have we decided that Drupal doesn't *need* to scale? I missed that meeting.
Op donderdag 29 december 2005 22:17, schreef Khalid B:
Not sure about making it the default though ...
Again I am no SQL Guru, but it sounds like we could easyly make a shell or even PHP CLI script that: * runs the database.mysql (does this count for postgre too?) and changes the MyISAM stuff into another (innodb) command and then saves that as a database_innodb.mysql * changes an existing database trough dump and pipes into an innodb database. Anyone who can give me some hints? I would like to add this script to drupalCOM Ber
On 12/30/05, Bèr Kessels <ber@webschuur.com> wrote:
Op donderdag 29 december 2005 22:17, schreef Khalid B:
Not sure about making it the default though ...
Ber I think I was not clear enough. What I meant was I do not think making InnoDB the default for Drupal will not be a good idea, since only the very high traffic sites that experience locking contention suffer from this. For the majority, it seems MySQL is not an issue.
Again I am no SQL Guru, but it sounds like we could easyly make a shell or even PHP CLI script that: * runs the database.mysql (does this count for postgre too?) and changes the MyISAM stuff into another (innodb) command and then saves that as a database_innodb.mysql
That is quit possible. But the issue is whether we want it to be the default or not. John Handelaar was able to alter the tables on the fly and change their type on a live site. Perhaps John can give us some more technical details.
* changes an existing database trough dump and pipes into an innodb database.
Anyone who can give me some hints? I would like to add this script to drupalCOM
Op vrijdag 30 december 2005 15:58, schreef Khalid B:
I think I was not clear enough. What I meant was I do not think making InnoDB the default for Drupal will not be a good idea, since only the very high traffic sites that experience locking contention suffer from this.
I think /I/ was not quit clear. From what I heard, using myISAM is the best option for Joe Schmoe. But it also seems that some people will need innodb. Hence I want to ship my drupalCOM with a simple script that allows anyone to "change gears" (change to innoDB withoout having to jump trough hoops) if need arises.
Got it. Clear now. See here: http://dev.mysql.com/doc/refman/4.1/en/converting-tables-to-innodb.html "The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table. That is, use ALTER TABLE ... TYPE=INNODB, or create an empty InnoDB table with identical definitions and insert the rows with INSERT INTO ... SELECT * FROM ...." Seems a script with show tables, then alter table does the trick. InnoDB involves creating a table space a la Oracle though, so there is more preparatory work that has to be done. On 12/30/05, Bèr Kessels <ber@webschuur.com> wrote:
Op vrijdag 30 december 2005 15:58, schreef Khalid B:
I think I was not clear enough. What I meant was I do not think making InnoDB the default for Drupal will not be a good idea, since only the very high traffic sites that experience locking contention suffer from this.
I think /I/ was not quit clear. From what I heard, using myISAM is the best option for Joe Schmoe. But it also seems that some people will need innodb. Hence I want to ship my drupalCOM with a simple script that allows anyone to "change gears" (change to innoDB withoout having to jump trough hoops) if need arises.
Here is another idea. We can remove the Type=MyISAM from all core and contrib creation scripts, and let the database default to what the admin set it up as. The parameter: default-table-type=innodb in the [mysqld] section of the my.cnf file specifies which type they will be created as.
This might be a good "SQL snippet": http://drupal.org/node/41586 Cheers, Djun
See here: http://dev.mysql.com/doc/refman/4.1/en/converting-tables-to- innodb.html
"The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table. That is, use ALTER TABLE ... TYPE=INNODB, or create an empty InnoDB table with identical definitions and insert the rows with INSERT INTO ... SELECT * FROM ...."
Seems a script with show tables, then alter table does the trick.
InnoDB involves creating a table space a la Oracle though, so there is more preparatory work that has to be done.
Just cross posting this to documentation list. Indeed this would be a good page. If someone would care to create the entire SQL script required to make duplicate Innodb tables - and the corresponding INSERT INTO ... SELECT * FROM queries, I would be happy to put it in the snippets page (and/or elsewhere). I would do this myself - but I think this requires an expert's hand (i.e. someone that has a actually done the conversion themselves). andre puregin wrote:
This might be a good "SQL snippet":
Cheers, Djun
See here: http://dev.mysql.com/doc/refman/4.1/en/converting-tables-to- innodb.html
"The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table. That is, use ALTER TABLE ... TYPE=INNODB, or create an empty InnoDB table with identical definitions and insert the rows with INSERT INTO ... SELECT * FROM ...."
Seems a script with show tables, then alter table does the trick.
InnoDB involves creating a table space a la Oracle though, so there is more preparatory work that has to be done.
___________________________________________________________ To help you stay safe and secure online, we've developed the all new Yahoo! Security Centre. http://uk.security.yahoo.com
On 29 Dec 2005, at 18:24, John Handelaar wrote:
Try turning it off if your slow query log starts showing cache-related things taking 90 seconds (!) or more (!!).
That's somewhat odd; 90 seconds looks extreme. Table locks are fast but have poor concurrency. However, UPDATE and INSERT queries should get a higher priority than SELECT queries to prevent starvation. They should only wait for the active SELECT queries on the table to complete. The UPDATE/INSERT sequence in cache_set() should be fast so '90 seconds or more' is somewhat ... long. To me, it suggests that some queries are really slow, regardless any table locking. Can you share what queries take that long? Whether we can support InnoDB out of the box depends on its availability/adoption. Do we have any references about that? Any other systems that ship with InnoDB?
PS: Still no bigsite/performance forum. Have we decided that Drupal doesn't *need* to scale? I missed that meeting.
That's somewhat harsh. Isn't making Drupal scale developer-specific and suitable for development@drupal.org? -- Dries Buytaert :: http://www.buytaert.net/
Whether we can support InnoDB out of the box depends on its availability/adoption. Do we have any references about that? Any other systems that ship with InnoDB?
Perhaps Drupal can be engine-agnostic, i.e. ships with all the CREATE TABLE statements *without* a "type=" part. In most cases, this will default to MyISAM. But again, how many need Inno anyway? They can do ALTER TABLE or any of the other methods if they so wish. So, is it worth the trouble doing all this for 1% of the users?
On 30-Dec-05, at 1:46 PM, Dries Buytaert wrote:
PS: Still no bigsite/performance forum. Have we decided that Drupal doesn't *need* to scale? I missed that meeting.
That's somewhat harsh. Isn't making Drupal scale developer- specific and suitable for development@drupal.org?
Mailing lists suck when searching for solutions...hence the public facing "Performance and Scalability" forum. We'll continue to have requests for ever more micro "groups", for which forums are the only bucket we have today. Of course, finished solutions/recipes/tips/etc. should go in the handbook -- John, is there something specific you want to cover in that respect? A "performance and scalability" section underneath the Configuration and Customization book is probably the right place. /me mutters something about groups.drupal.org.... -- Boris Mann Vancouver 778-896-2747 San Francisco 415-367-3595 SKYPE borismann http://www.bryght.com
On 30 Dec 2005, at 23:02, Boris Mann wrote:
PS: Still no bigsite/performance forum. Have we decided that Drupal doesn't *need* to scale? I missed that meeting.
That's somewhat harsh. Isn't making Drupal scale developer- specific and suitable for development@drupal.org?
Mailing lists suck when searching for solutions...hence the public facing "Performance and Scalability" forum. We'll continue to have requests for ever more micro "groups", for which forums are the only bucket we have today.
On http://drupal.org/forum, should that go under 'General', 'Support' or 'Development'? Care to provide a one-line subscription? I'm OK with making more forums but we should put some effort in the performance and scalability of the forum module (irony?), as well as in a subscription module. -- Dries Buytaert :: http://www.buytaert.net/
On 31-Dec-05, at 12:15 AM, Dries Buytaert wrote:
On 30 Dec 2005, at 23:02, Boris Mann wrote:
PS: Still no bigsite/performance forum. Have we decided that Drupal doesn't *need* to scale? I missed that meeting.
That's somewhat harsh. Isn't making Drupal scale developer- specific and suitable for development@drupal.org?
Mailing lists suck when searching for solutions...hence the public facing "Performance and Scalability" forum. We'll continue to have requests for ever more micro "groups", for which forums are the only bucket we have today.
On http://drupal.org/forum, should that go under 'General', 'Support' or 'Development'? Care to provide a one-line subscription?
Development (since generally high-traffic sites will have more technically savvy maintainers) I assume you meant description. How's this? -- Performance and Scalability: A forum for high-traffic site maintainers to discuss tips and techniques, as well as to discuss any development changes that are needed. -- Aside: tools that work for a small site won't work for sites with high numbers of users/content/etc. -- so likely this would be an appropriate spot for related issues.
I'm OK with making more forums but we should put some effort in the performance and scalability of the forum module (irony?), as well as in a subscription module.
I think we'll see some good work around subscription in the next little while. -- Boris Mann Vancouver 778-896-2747 San Francisco 415-367-3595 SKYPE borismann http://www.bryght.com
On Thu, Dec 29, 2005 at 12:24:46PM -0500, John Handelaar wrote:
The same table-level locking is *lethal* on a heavily- trafficked site which has both
There is a talk about dropping locks completly on http://drupal.org/node/39460 (yes, postgresql issue, but I think might apply to mysql too), with some performance tests (see link from http://drupal.org/node/39460#comment-58541). -- Piotrek irc: #debian.pl Mors Drosophilis melanogastribus!
participants (10)
-
andre -
Boris Mann -
Bèr Kessels -
Dries Buytaert -
Jeff Eaton -
John Handelaar -
Karoly Negyesi -
Khalid B -
piotr＠mallorn.ii.uj.edu.pl -
puregin