My blog

In theory, theory and practice are the same. In practice, they are not. "Albert Einstein"

Posts Tagged ‘performance’

Apache .htaccess and disk access, a quick but deeper look

October 30th, 2009

I was discussing (well twitting is more appropriate) with my friend Arnaud about .htaccess files and why they should not be used.

To be quick, using .htacess files is not good at all for disk performance because Apache will hit the disk for each HTTP request, so when the site is on production you can easily imagine this can quickly be an issue if you use those files.

But let’s have a look at how it actually work on the system.

Remember, for each HTTP request Apache will look on your file system if it finds a .htaccess file. So if we create the following virtual web application :

webproject
|-- cache
|-- design
|   |-- css
|   |-- html
|   |-- images
|   `-- javascript
`-- src
 |-- php
 `-- python

With the following directive in your Apache configuration file (wether a VirtualHost or not) :

<Directory "/path/to/webproject/">
 AllowOverride All
 [....]
 </Directory>

Now let’s have a look at how Apache handle AllowOverride on the Operating System, what happens if I visit the ‘webproject/src’ directory ?

Calling http://localhost/webproject/src generated the following system calls

1679/0x97c9998:  open("/Users/jerome/work/www/.htaccess\0", 0x0, 0x1B6)         = -1 Err#2
 1679/0x97c9998:  open("/Users/jerome/work/www/webproject/.htaccess\0", 0x0, 0x1B6)         = -1 Err#2
 1679/0x97c9998:  open("/Users/jerome/work/www/webproject/cache/.htaccess\0", 0x0, 0x1B6)         = -1 Err#2
 1679/0x97c9998:  open("/Users/jerome/work/www/webproject/design/.htaccess\0", 0x0, 0x1B6)         = -1 Err#2
 1679/0x97c9998:  open("/Users/jerome/work/www/webproject/src/.htaccess\0", 0x0, 0x1B6)         = -1 Err#2
 1679/0x97c9998:  open("/Users/jerome/work/www/webproject/src/python/.htaccess\0", 0x0, 0x1B6)         = -1 Err#2
 1679/0x97c9998:  open("/Users/jerome/work/www/.htaccess\0", 0x0, 0x1B6)         = -1 Err#2
 1679/0x97c9998:  open("/Users/jerome/work/www/webproject/.htaccess\0", 0x0, 0x1B6)         = -1 Err#2
 1679/0x97c9998:  open("/Users/jerome/work/www/webproject/src/.htaccess\0", 0x0, 0x1B6)         = -1 Err#2
 1679/0x97c9998:  open("/Users/jerome/work/www/webproject/src/php/.htaccess\0", 0x0, 0x1B6)         = -1 Err#2

As you can see Apache looked for the .htaccess file for each directory I had to browse in order to go to the webfolder/src directory.

That is a simple example, but if you have a much more complex directory structure and a lot of traffic I guess you can imagine how much Apache is going to hit the disk in order to find a .htaccess file.

So in order to avoid that it is always recommended to configure everything in VirtualHosts and to avoid .htaccess files.

‘Hope that helped a bit

3 comments »

Posted in Tech

Tags: Apache performance

MySQL and SELECT, the evil ORDER BY clause

August 13th, 2009

I was at a customer, helping them to make their import/export run fast and after testing a few things we realized that only one query was the source of the problem.

The query looked like this :

SELECT id, [...] FROM the_table WHERE the clause ORDER BY id ASC;

After a few tests we discovered that this query took around 15 seconds to run on only around 1 000 000 rows.

Running explain show the following result (slightly modified for more readability)
+----+------+-----+------------+--------+-------+-----+--------+-----------+ | id | [...] | type | possible_keys | key | key_len | ref | rows | Extra | +----+------+-----+------------+--------+-------+-----+--------+-----------+ | 1 | [...] | index | idx1, idx2 | PRIMARY | 8 | NULL | 1103921 | Using where | +----+------+-----+------------+--------+-------+-----+--------+-----------+

The problem is that they already were the correct indices on this table but MySQL was not able to choose the correct one. I tried to enforce the use of the correct INDEX with USE INDEX( … ) in the query and it worked fine but I did not like this solution.

After that I noticed that the ORDER BY clause was useless since the field used to sort was the primari key id.
And even if this clause is removed, the result set will be sorted on the field ‘id’.

You can verify this with the simple SQL below :

CREATE TABLE table1 ( id tinyint(4) NOT NULL AUTO_INCREMENT, field varchar(20) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO table1 VALUES (1, '4');

INSERT INTO table1 VALUES (2, '3');

SELECT * FROM table1;

Which returns

+----+-------+ | id | field | +----+-------+ | 1 | 4 | | 2 | 3 | +----+-------+

Which is correct.

So after removing the ORDER BY clause, EXPLAIN was more polite :

+----+---+----+------------+-----+-------+-----------+-----+-----------+ | id | [...] | type | possible_keys | key | key_len | ref | rows | Extra | +----+---+----+------------+-----+-------+-----------+-----+-----------+ | 1 | [...] | ref | idx1, idx2 | idx2 | 8 | const, const | 2 | Using where | +----+---+----+------------+-----+-------+-----------+-----+-----------+

And the query took a few milliseconds to run.

So next times you have slow queries, check your ORDER BY clause first and see of you can remove it or find a workaround.