Into.the.Void.

I don’t know how many times I’ve heard a sysadmin swearing at a web/application developer that has “forgotten” to add proper index to their database tables. Please dear developers, add those indexes, it’s not that hard!

Today I had to do it on a table for a xoops module. A single query could keep mysql very very busy…how busy ? the query could take more than 2 minutes, unless it was already cached! I’ll post the way I solved it as an example. It is actually usually quite easy to solve these kind of issues.

1) How to spot the problem
Your application is slow and your server’s “top” shows mysql running constantly at full load.

2) Find the problematic query. Login to mysql and run a ‘show processlist;’

mysql> show processlist;
+------+--------------+-----------+--------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id   | User         | Host      | db                 | Command | Time | State          | Info                                                                                                 |
+------+--------------+-----------+--------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 1792 | admin        | localhost | aaa                | Sleep   |    0 |                | NULL                                                                                                 | 
| 4790 | user         | localhost | bbb                | Query   |    0 | Sorting result | SELECT * FROM foobar WHERE (com_rootid = '22797' AND com_id >= '22797') ORDER BY com_id              | 
| 4791 | user         | localhost | bbb                | Query   |    0 | Sorting result | SELECT * FROM foobar WHERE (com_rootid = '22797' AND com_id >= '22797') ORDER BY com_id              | 
| 4817 | admin        | localhost | NULL               | Query   |    0 | NULL           | show processlist                                                                                     | 
+------+--------------+-----------+--------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

3) Take a closer look at the structure of the table

mysql> describe foobar;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| com_id       | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment | 
| com_pid      | mediumint(8) unsigned | NO   | MUL | 0       |                | 
| com_rootid   | mediumint(8) unsigned | NO   |     | 0       |                | 
| com_modid    | smallint(5) unsigned  | NO   |     | 0       |                | 
| com_itemid   | mediumint(8) unsigned | NO   | MUL | 0       |                | 
| com_icon     | varchar(25)           | NO   |     |         |                | 
| com_created  | int(10) unsigned      | NO   |     | 0       |                | 
| com_modified | int(10) unsigned      | NO   |     | 0       |                | 
| com_uid      | mediumint(8) unsigned | NO   | MUL | 0       |                | 
| com_ip       | varchar(15)           | NO   |     |         |                | 
| com_title    | varchar(255)          | NO   | MUL |         |                | 
| com_text     | text                  | NO   |     | NULL    |                | 
| com_sig      | tinyint(1) unsigned   | NO   |     | 0       |                | 
| com_status   | tinyint(1) unsigned   | NO   |     | 0       |                | 
| com_exparams | varchar(255)          | NO   |     |         |                | 
| dohtml       | tinyint(1) unsigned   | NO   |     | 0       |                | 
| dosmiley     | tinyint(1) unsigned   | NO   |     | 0       |                | 
| doxcode      | tinyint(1) unsigned   | NO   |     | 0       |                | 
| doimage      | tinyint(1) unsigned   | NO   |     | 0       |                | 
| dobr         | tinyint(1) unsigned   | NO   |     | 0       |                | 
+--------------+-----------------------+------+-----+---------+----------------+
20 rows in set (0.01 sec)

4) run EXPLAIN on the problematic query

mysql> explain SELECT * FROM foobar WHERE (com_rootid = '17536' AND com_id >= '17536') ORDER BY com_id;
+----+-------------+--------------------+------+-----------------------+------+---------+------+-------+-----------------------------+
| id | select_type | table              | type | possible_keys         | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+--------------------+------+-----------------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | foobar             | ALL  | PRIMARY               | NULL | NULL    | NULL | 18271 | Using where; Using filesort | 
+----+-------------+--------------------+------+-----------------------+------+---------+------+-------+-----------------------------+
1 row in set (0.02 sec)

If “type” is “ALL”, like the case above, then you have a problem. This is the worst “type” you could have. The query above scans the whole table and has to go through 18271 rows and then has to do a filesort on these rows.

5) Look at indexes, if there are any….

mysql> show index from foobar;
+--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table              | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| foobar             |          0 | PRIMARY    |            1 | com_id      | A         |       18272 |     NULL | NULL   |      | BTREE      |         | 
| foobar             |          1 | com_pid    |            1 | com_pid     | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
| foobar             |          1 | com_itemid |            1 | com_itemid  | A         |          18 |     NULL | NULL   |      | BTREE      |         | 
| foobar             |          1 | com_uid    |            1 | com_uid     | A         |           6 |     NULL | NULL   |      | BTREE      |         | 
| foobar             |          1 | com_title  |            1 | com_title   | A         |       18272 |       40 | NULL   |      | BTREE      |         | 
+--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

6)Create necessary new index

ALTER TABLE  `DB`.`foobar` ADD INDEX `com_id_rootid` (  `com_id` ,  `com_rootid` )

7) Explain the query again

mysql> explain SELECT * FROM foobar WHERE (com_rootid = '17536' AND com_id >= '17536') ORDER BY com_id;
+----+-------------+--------------------+------+------------------------+---------------+---------+-------+------+-----------------------------+
| id | select_type | table              | type | possible_keys          | key           | key_len | ref   | rows | Extra                       |
+----+-------------+--------------------+------+------------------------+---------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | foobar             | ref  | PRIMARY,com_id_rootid  | com_id_rootid | 3       | const |    1 | Using where; Using filesort | 
+----+-------------+--------------------+------+------------------------+---------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

now the query is of type “ref” and it only fetches 1 row!
If the query could be optimized even more by the developer and he/she could replace ‘>=’ with ‘=’ then it would make us even happier

mysql> explain SELECT * FROM foobar WHERE (com_rootid = '17536' AND com_id = '17536') ORDER BY com_id;
+----+-------------+--------------------+-------+-----------------------+---------+---------+-------+------+-------+
| id | select_type | table              | type  | possible_keys         | key     | key_len | ref   | rows | Extra |
+----+-------------+--------------------+-------+-----------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | foobar             | const | PRIMARY,com_id_rootid | PRIMARY | 3       | const |    1 |       | 
+----+-------------+--------------------+-------+-----------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

the type would now be ‘const’, which is the best type we could have.

The problem got fixed with just one command! Now the queries take milliseconds to finish and the web application is as fast as ever. The server load dropped from 2.5 to 0.4 with just this tiny addition to the indexes.

CPU Load

So dear developers, please run EXPLAIN to your queries before you submit your applications and if you find queries with type=ALL try to add indexes! It saves all of us a loooooot of trouble!

For anyone who wants to read a nice blog post about EXPLAIN and what each part of the output means, I recommend that he/she reads this: MySQL Explain – Reference

Filed by kargig at 22:06 under Linux
Tags: explain, index, Linux, mysql, problem, query, slow, web
3 Comments | 8,453 views

Lately I was given a task of moving some websites/webservices from real boxes to some VMs. Most of the sites were Joomla! applications so moving the installation was quite easy, tar files, check configuration.php for db username/pass/etc and dump the database on the old server and then copy these to the VM. Restore files, import database, minor path changes to configuration.php… that’s about it.

But then it was time to move an “eclass” application. Specifically it was an installation of Open eClass, a web based e-learning software created by Greek Academic Network. So I copied the files, found the configuration file with database credentials, dumped the db and moved it to the VM. The site came up but it was not functioning properly. Course material was missing from the website, but I could certainly see the files on the file system. I dumped the database again and re-imported it. Nothing, the site refused to work as expected. I went back to the original machine and shut down mysql to start it with “–skip-grant-tables” since I didn’t have the root mysql password. MySQL came up, I logged in as root and I typed: “show databases;”

Oh the horror!!!!
I couldn’t believe my eyes…in front of me there were more than 200 databases with the names of courses of the e-elearning platform! I shut down mysqld and restarted it normally. Then I logged in as the “eclass” user and issued the following:
show grants for eclass@localhost;
The output:

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'eclass'@'localhost' IDENTIFIED BY PASSWORD 'XX' | 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, CREATE TEMPORARY TABLES, LOCK TABLES ON `eclassdb`.* TO 'eclass'@'localhost'  |

I immediately started thinking that someone had _really_ fucked up the installation. I went to Open eClass website and tried to search for documentation on installation instructions. I downloaded a pdf and I read between the installation instructions:

A “username” and a “password” for MySQL with database creation rights.

.
Okie..let’s translate that to simple english, it needs a ‘root’ mysql account renamed to something else.

I am not a web developer, I do not even consider myself a developer, but this setup makes no sense for me. Who and why decided that it would be a good idea to have a web application’s mysql user being able to create new databases ? Is this application only to be installed on a machine of its own ? If so, it’s such a waste of resources. I can understand the complexity and the extra time that a well designed and correctly normalized database requires, but this isn’t an excuse when creating software to be distributed and widely used by lots of people, especially universities. I can’t judge the application, it actually looks quite useful, but it’s setup certainly has design problems that need to be solved.

And finally, what “if” there is some security hole in the application (sql injections anyone?) and a malicious user starts dropping databases other than the ones belonging to eclass ? Who’s to blame for that ?

My advice to anyone running this application is to have it as isolated as possible from the rest of his infrastructure. Possibly in a virtual machine of its own. And there should be a warning about it on the website.

P.S. Looking at the credits, it seems that I know in person some of its developers, and that makes it ever harder to blog about what I faced. I’ll certainly ask them about this web application the next time I meet them though.

Filed by kargig at 11:05 under Internet,Linux
Tags: database, design, eclass, mysql, openeclass, security, sql injection, virtual machine, web application, website
7 Comments | 16,042 views

comzeradd send me an e-mail about a mysql service not starting in a server we administer. I started taking a look around…nothing seemed suspicious.
I tried uninstalling and re-installing mysql-server-5.0 a few times and I always got this kind of output from apt-get:

Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!
invoke-rc.d: initscript mysql, action “start” failed.
dpkg: error processing mysql-server-5.0 (–configure):
subprocess post-installation script returned error exit status 1
Errors were encountered while processing:
mysql-server-5.0
Reading package lists… Done
Building dependency tree
Reading state information… Done
Reading extended state information
Initializing package states… Done
Reading task descriptions… Done

(more…)

Filed by kargig at 19:35 under Linux
Tags: bind-address, debian, init, Linux, loopback, mysql
2 Comments | 4,935 views

The Problem
To sum it up, the case is this: many many many web applications were programmed so that they used latin1 collation for their fields inside mysql databases. But most users now use utf8 from within their browsers. What happens is that utf8 characters are getting stored inside latin1 fields, which in return produces chaos! A huge web application that used that kind of madness was WordPress. Luckily (or not) WordPress now uses utf8 everywhere. I’ve known many many many people that got so frustrated when they tried to move from their old WordPress installation to a newer one because all their greek posts couldn’t be exported “easily”, I won’t say “properly” because there are always solutions to problems like this, but all the solutions were not straightforward at all, that they finally dumped the idea of moving the posts and started a new blog.

This is a HUGE problem for many greek (and not only) users and I hope I now have an elegant(?) solution to it.
(more…)

Filed by kargig at 21:14 under Greek,Internet,Linux
Tags: bash, convert, Greek, latin1, mysql, sed, utf8, wordpress
22 Comments | 38,566 views

A couple of days ago I’ve met a guy who has a high traffic blog about tech stuff. The guy was telling me that he has hosting problems and that his blog is getting slower and slower by the day. I’ve offered to help him by providing hosting for him in one of the servers that I administer. After making the transition from his old hosting to my server, which was not an easy thing to do due to latin1 to utf8 conversions that had to be made – it deserves a post of it’s own, I started to notice increased load on my server. Sure his blog had heavy traffic…but could it be that bad ?
(more…)

Filed by kargig at 13:49 under Linux
Tags: caching, db-cache, Linux, mysql, wordpress, wp-cache
10 Comments | 19,589 views