Hi there.
I am trying to set up a perfect local environment for Drupal on my Windows Vista machine, and since my knowledge of PHP and MySQL is limited, I am constantly struggling with issues surrounding the AMP stack, as well as Drupal itself.
I am not sure, but I might have installed Apache/MySQL/PHP more than a few dozen times, trying practically every combination there is--from packages like WampServer all the way to installing all the individual programs from zip archives, and configuring their conf/ini myself.
What I learned from all these install procedures is that Drupal is completely dependent on how the technology stack below it is configured. I have always wondered why did it take more than 30 seconds to simply install Drupal, and more than 15 seconds to load the module list page on a fresh install, until an incident a few days ago made me experiment a bit.
What basically happened was that after installing MySQL from a zip package (i.e. no wizard pre-configuration of the my.ini), I got an amazingly well performing Drupal. It installed in less than 5 seconds, and I could access every page of the site in a second or two--even the modules page! At first I wasn't sure why this dramatic increase in performance happened, but after trying out a few other schemes of installing AMP, it was clear that the reason for this was in my.ini.
I realized that if I use any of the sample configuration files like my-small.ini, or my-medium.ini, or even a my.ini with no configuration at all except for the mysql/data directories, Drupal is blazing fast. If I use MySQL installer and then set up a my.ini file using the configuration wizard however, Drupal becomes a snail.
After a bit of research, I figured out that the reason for all this should be in the table type and/or its configuration. If I use the default MyISAM (a my.ini with zero configuration), Drupal loads in no time. If I use InnoDB--the wizard's default--the way the installer configures it, I get a tremendously slow Drupal, even on a fresh install.
Working with a Drupal installation where it takes more than 15-20 seconds to access a page can be really frustrating, and it makes me not want to use Drupal. I realize it's a configuration issue, but since I don't know much about Apache/PHP/MySQL (in the end, that's why I am using a CMS, god dam't), I don't know how to proceed. Should I use MyISAM, or should I configure InnoDB in some better way?
What am I supposed to do?!
Sorry for the rant, but I just want to use Drupal.
Thank you.
Comments
MyISAM
I'm using XAMPP on a Windows XP machine. I just now checked my database tables with PHPMyAdmin, and they're all MyISAM tables.
According to the MySQL manual, the default table engine should be MyISAM:
http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html
I'm guessing you're using the Windows Essentials installer?
http://dev.mysql.com/doc/refman/5.1/en/innodb.html
"The Windows Essentials installer makes InnoDB the MySQL default storage engine on Windows, if the server being installed supports InnoDB."
Apparently the XAMPP installer doesn't do this.
I would go ahead and change that in the my.conf file as you've done - it should be enough to merely un-comment the line that says "skip-innodb" and comment out the lines under it (that start with "innodb_").
If you do want to use InnoDB (for foreign key constraints or whatever), then this guide should be somewhat helpful:
http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimi...
Obviously the best thing to do would be to optimize queries - but since you're not a Drupal developer, that's not up to you. (Queries from Drupal's core modules should be fine, there might be a few contributed modules that aren't.)
-Karlheinz
Yes, MySQL installers make
Yes, MySQL installers make innodb the default, but the way innodb comes preconfigured in my.ini kills Drupal's speed--as I already wrote, it takes too long to load simple pages, and makes the hard drive work very intensely. I guess it's because of how InnoDB is configured, but I simply don't have the know-how to tweak it.
I am trying to imitate my future host environment as much as possible, and that's why I am installing the programs manually.
I guess the question now becomes--should I use innoDB at all?! I will be running the basic Drupal installation with CCK and Views--nothing fancy, I just want to create a blog with forums. I plan to use a 320MB VPS, and that's what I am trying to recreate on my local computer.
It seems like there is no perfect fit from the sample my.ini files in mysql's directory, but I went for my.ini-large, and that's what I am using right now without any modifications, except for the directories. Should I use another my.ini? Should I optimize anything further? Should I bother with InnoDB?
InnoDB is usually used either
InnoDB is usually used either on large websites, which have a dedicated MySQL server, or on sites where data corruption is catastrophic (e.g. banking sites).
For a typical Drupal installation, I see no reason to use anything but MyISAM. My opinion obviously, but I don't think the extra features are worth the overhead.
Is your future host environment on a server you have direct access to? If you're going with a commercial hosting provider, they usually have everything set up and optimized already (and almost all use Linux).
-Karlheinz
I won't be using a typical
I won't be using a typical shared host--I plan to get a Linode; start from the smallest one, and build up. Hearing the horrific stories about shared hosting, I prefer to go with a VPS, and set up things myself.
Although it took me quite a lot of time, I learned how to set up Apache and PHP correctly, but now MySQL appears to be a bit of a challenge. Oddly enough, I feel like using the most basic my.ini file:
gives me the fastest Drupal. But I want to build for the future, and that's why I need to know what's the optimal my.ini to start with. Again, it's just a blog with forums and a few pages, but as such I want it to be set properly, and be fast.
Thanks for your replies.
tweaking innodb
I also wrote a blog with some basic InnoDB tweaking pointers, have a look at http://cafuego.net/2009/10/10/mysql-yoursql
Your recommendation on:
Your recommendation on: http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimi... was a godsend! Wow, it has made my development server nearly as fast as the production Linux box...
The specific changes I made were:
innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency=8
transaction-isolation=READ-COMMITTED
Computer = Windows 7 RTM, 4gig of memory and 2.2ghz dual core processor.
Mysql version = 5.1.35
Drupal version = I am running on Pressflow (based on Drupal 6.13)
Thank you very much :)
David Peterson