By handelaar on

First off I'm obliged to offer a pre-emptive apology for the 'considered harmful' part of the title.

MySQL's reputation for being a 'toy' database, an opinion held by lots of database snobs, might be something which relates to its default storage engine, not to the server as a whole.

I'd like to introduce forum searchers to this thread from the Development mailing list on Drupal caching, MySQL's MyISAM storage engine, and sites which change a lot (like, for example, ones with busy forums):

Counterintuitive but true: Caching makes a high-traffic site slower

In a nutshell - MyISAM locks an entire table before writing to it. The knock-on effects of that on your site and what users think of it (and of you, if you're unlucky), can be huge.

⋅ Categories: Drupal 4.6.x

Comments

druvision’s picture

druvision commented

My higher traffic sites (drupal 4.6) started to have alot of MySQL errors on the cache tables, claiming that there is a duplicate key.

Currently, I turned the cache off until I have the time to debug it. My guess is that the cache mechanism is not fully supported by all the addon modules that I am using. It seems that each module have to implement it's own cache mechanism, sort of, but I haven't checked it yet. Is it so?

Amnon
-
Personal: Bring Dolphin's Simple Joy to your Work - Job - Career
Professional: Small Business Web Hosting Strategies, עסקים ברשת - מדריך ליזם

druvision’s picture

druvision commented

For the sake of completeness of discussion, also look at This thread: Cache support has to be added individually for each module and some 3rd party modules don't support it so well.

pamphile’s picture

pamphile commented

Not meaning to stray from the topic, but you can turn off drupal cache and turn on another caching 3rd party caching system.

aghajani’s picture

aghajani commented

In one of my website with semi heavy traffic, cache table locks and the whole site stops. In phpmyadmin on row for cache table a message says the table is in use.

insomoz’s picture

insomoz commented

If I dont have cache enabled, top flunctuates between
3-10

with cache enabled 0-1

On another server, I tried it without, I couldnt get the server back online and had to reboot

Traffic med abotu 12k uniques a day

kaparo’s picture

kaparo commented

These days I have problems with one of my sites too, using Drupal 4.6.6 ... 8000 hits per day (it's not that much traffic), with 30 concurrent db connections limitation from my shared host and certain pages are not accessible anymore ... I switched on the cache, do "empty" cache, sessions tables frequently, switched these tables from ISAM to InnoDB ... but nothing helps :(

Do you have any suggestions? If I switch off the cache, the site becomes unusable immediately ... I already searched the forum but seems no solutions found ... there is a thread mentioning the "loose" cache feature of 4.7 ... but I cannot afford installing 4.7 without all the plugins used migrated to the new version ...

moshe weitzman’s picture

moshe weitzman commented

this thread is a much of half truths. the cache we probably want to talk about here is the *page cache*. thats the feature that you switch on and off in admin/settings. that has nothing to do with 3rd party modules.

i agree wit hthe suggestions in that mailing list thread that our schema files should be engine agnostic. then sql admins can tweak as needed.

Art Morgan’s picture

Art Morgan commented

I'm having the same problem handelaar and others mention above -- adding a new comment or new post hangs users' browsers forever.

I have the cache ("page cache?") turned off under the admin settings, but how do I turn off the rest of the cache (all those filter entries)? That seems to be what is hanging things with the locks. My cache table grows to over 100MB, with 80k rows, in less than a day.

mws’s picture

mws commented

And have been considering moving my comment table to a ramdisk for faster access:

http://drupal.org/node/119411

2houseplague’s picture

2houseplague commented

After a week of headaches, I turned it off and things are good. Nuff said.

http://profitlabinc.com

mikhailian’s picture

mikhailian commented

Actually the Drupal cache is not only the source of slowness. It has other problems, it may return pages gzipped twice and this rendering complete garbage in the browser or feed the search engine bots with gzipped content without making sure they understand it, thus breaking your site's indexing.

I always recommend disabling Drupal caching for people and using php zlib compression and some kind of a php accelerator too boost performance.

It's surprising to see that one of the most advertised feature of Drupal 5.x is about even more caching.

ilya1st’s picture

ilya1st commented

http://brainstorm.name/blog/drupal-caching-try-0 - my beta version of fast file caching I use now on sites.

Drupal cache table slowdown I can see even on low loads