One of our customers has a Drupal enabled website… as many of you might know, Drupal is an open source CMS system with nice features, but it also is quite a bottleneck on server performance.

We suggest drupal enabled website hosting customers to opt for a Virtual Private Server Light or even Virtual Private Server Standard setup. This means you can set up a nice isolated environment for a Drupal driven CMS website with dedicated server performance at severe reduced cost.

When setting up the environment be sure to run through the following checklist:

• CentOS 5.3 (for example)
• Apache 2.2 webserver
• MySQL 5.0 database server
• PHP 5.1 or even better, 5.2
• and, very important: eAccelerator 9.5.3 or higher.

Why the eAccelerator? Well Drupal uses a lot of cached pages. Enabling Drupal caching already gives you a boost but installing eAccelerator makes sure frequently used php scripts are stored in a compiled state thus allow retrieval faster.

On top of that, you may optimize the MySQL query_caching settings, as they Drupal also uses a lot of the same queries.

You can find the latest version of eAccelerator here: http://eaccelerator.net/

Take care,

Dominique

in config.inc.php

$cfg['LoginCookieValidity'] = 86400; // seconds

When developing projects for our customers we now and then run into an application requiring tables with lots of rows. When talking about ‘lots’ we mean millions of rows, not a couple of thousand.
Imagine corn fields of 300,000,000 rows enlightened by a midsommer sun, awaiting to be queried by a bunch of hungry locust surfers.

MySQL server can handle these amounts of data without a problem, however getting the data into the table with an import for example proves to be the tricky part.

Apart from getting the data into the table, the trickiest job will become succesfully importing the data with the creating of indices. The problem is, when importing data, MySQL Server tries to re-create any indices (primary keys for example) and this can be a time consuming process. Do a simple test, pick a table of 500,000 rows, import it and wait… it will take some seconds.

"Seconds? Sounds fine to me!" you will probably say, but do not forget the larger the table the more time it will take to create the indices… do the math, 300,000,000 rows and you will find the mysqld process eating up CPU resources and yourself eating up a bunch of biscuits to kill the time waiting.

It can be done however, and quickly… there is just a matter of standard steps you will have to follow!

Step 1: getting the data on the server and importing it

The first part can already cause you some headaches: uploading the CSV file onto the server. We first compressed the file before uploading, it already saved us some time. Since CSV is plain text is will dramatically reduce once compressed.

When done uploading be sure to do a MD5 sum check on the original file and the remote file. Compare the keys, if both match you are sure the data arrived well and is ready to be processed. If not - and you ignore it - you will find yourself pulling out hairs while trying to figure out duplicate key errors and such during the improt process.

Once your file is uploaded, open the mysql server at console. Forget phpMyAdmin and other stuff, we are going hardcore console style here!

First create your table, for simplicity we will use a table with one column but it can be done with more columns of course. Do not create indices yet (you may of course but it is not necessary).

Some sources on the web suggest using mysqlimport for loading the data into the database. We will use LOAD DATA INFILE at the mysql server console. This is exactly the same, since mysqlimport uses LOAD DATA INFILE.
Be sure that if you are using a specific database user, it has the FILE persmission, otherwise you will get an error since you are loading a file from the disk and that particular user has no access.

We are using the following statement:

This statement is pretty convenient, it just says to load the data from the file located at /var/… into our created table named mytable. The low priority switch can be handy, it makes sure MySQL does not start eating up resources for a simple import, good if you are doing this on a server which can not be taken offline (as will be the case probably).

If your data is loaded into the table it is time to start the index buidling process. Your data probably loaded swiftly because MySQL server just loaded everything plainly into your table: no indices needed to be created so no disk swapping, memory swapping and similar voodoo.

Step 2: Building the indices

Next step is to close all open file handles, this can be done in the mysql console:

Optionally you could take the server down but we realize that this is not always possible.
Now go to the directory of your database files, in our case this was /var/lib/mysql/ourdatabase/

Rename the data file, because you do not want MySQL to start buidling indices from your data *yet*.

Switch back to your mysql console and truncate (empty) the table. "I will loose all my data!". No you will not, you are just telling MySQL that the mytable is empty, you stored your data in the mytable.MYD.backup file, remember?

Now you can start creating your indices, for example:

Again, flush the tables to close all connections:

Now switch back to the server prompt and move your backup file back to the normal name it once had:

When the server asks you to overwrite, say yes (do not worry you are overwriting the empty table again with your saved data… your data is back! Magic! ).

Now comes the final step!

Step 3: Repairing the indices!

"Repairing? But I didn’t break anything?". Wrong, you fiddled with some files and now you created an index (your MYI file) for a data file that was empty but is now fileld (your MYD file). MySQL server is not stupid so this means you have an index file with non matching indices (e.g.: none) for your data file.

We use the myisamchk command at the server prompt to repair the indices:

These options are typically for the platform we have, but be sure NOT too take values too high or your server will get stuffed whiel re-creating the indices!

Most important, you will save SERIOUS amounts of time and the whole swapping process of disk transactions is skipped!

Thanks to the MySQL list for the resources, as this is typically not a job you will find out yourself

With a fresh install of PHP and MySQL on a Windows 2003 server you may get the following error when loading pages in IIS that use MySQL database calls in PHP:

This is due to an incompatibility problem between MySQL and the PHP libmysql dll.
For PHP release 5.2.1 the following fix worked:

1. Rename the libmysql.dll file in the PHP installation directory
   
2. Download the PHP 5.2.1 Windows Binary from the PHP website ( http://www.php.net/releases/)
3. Replace the libmysql.dll with the one from the package you just downloaded