[consulting] Drupal server requirements

[Chris Johnson]

Harry Slaughter wrote:

> "Part of the reason MySQL doesn't suffer as much as expected is because 
> the majority of applications for which it is used consist primarily of 
> read queries. In fact, the MyISAM engine (MySQL's default) was built 
> assuming that 90% of all queries run against it will be reads. As it 
> turns out, MyISAM tables perform very well as long as the ratio of reads 
> to writes is very high or very low."
> 
> but i'd really like to see numbers!
> 
> lots of folks talk about optimization of various kinds. often when you 
> boil it down to numbers, lots of these optimizations aren't even worth 
> the effort. mysql_connect -vs- mysql_pconnect, for example. why bother?
> 
> i've got a DB that has something like a 50/50 read/write ratio. at less 
> than a million records, one of the tables is starting to slow the system 
> down. one of the options is to convert to innodb. but i like to know the 
> facts as much as possible before trying to implement solutions.
> 
> and i wouldn't recommend that anyone blindly convert his drupal DB to 
> innodb without understanding the diffs between innodb and myisam.
> 
> i always assummed innodb was for banks and missile launch software and 
> myisam was for your average website :) guess it's time to study.

It all depends on what one's application is like.  There's a reason that data 
base analysts get paid the big bucks, too.  It's not something one becomes 
expert at in a few hours of reading.

I've been using MySQL MyISAM tables for mission-critical data since 1999.  My 
application is a mix of reads and writes.  But, and here's the catch, during 
the day, most of the load is reads being issued by web clients (with a rare 
update).  At night, most of the load is batch writes.  I'm handling multiple 
tables with 25 million rows and doing about 100,000 writes per night.

If I were doing those 100,000 writes during the day while my users were doing 
their reads, there's a good chance I'd either being using InnoDB or even more 
likely Postgresql.  (I've got another application that is nearly 50/50 
read-write all the time and it runs on Postgresql for that very reason.)

For Harry's table at under a million that's slowing down the system, the first 
thing I'd do is run "optimize table" against it.

Kieran Lal posted a good PHP snippet on drupal.org that can be posted into a 
PHP node in Drupal that is good for analyzing some of the settings one can 
tweak in MySQL to improve performance.  There are quite a few "knobs and 
levers" that one can change in MySQL to tune its performance.

So I would agree and reiterate Harry's point about not blindly converting to 
InnoDB without understanding a lot more about MySQL and one's system.

Incidentally, one of the very first things I did to optimize my database 
performance was put MySQL on its own machine with a lot of RAM, instead of 
having it contend with a web server and/or other processes.

Chris Johnson