I have been using MySQL for a very intensive read-write web application (averaging 102 queries per second) for more than two years. I had ups and downs with it, like crazy MyISAM behavior that readers can block writers AND OTHER READERS. Basically, a table level lock is issued for read. I have 100+ records in a table, so it takes a while to find anything that is not indexed. In the meantime, users are pondering (102qps, remember) and load goes up so much because of web server processes queuing like crazy. Ok, I learned not to do that anymore. I now use binary logging, restore to a different server and query there. Maybe a switch to InnoDB would be a good idea, but in this case I’d rather use a serious MVCC database like Firebird. Why, you might ask… well, here’s one of many reasons, the one that prompted my to write this:

In Firebird, I can happily do this:

delete from atable a1 
where exists ( 
select 1 from atable a2 where a1.data = a2.data and a1.id <> a2.id );

It just does it, and fast, because index on primary key field ID is used. In MySQL, to quote the manual:

“Currently, you cannot delete from a table and select from the same table in a subquery.”

Come on, this is one of the most basic database operation. So, what am I now to do? Waste my time dumping the list of IDs to delete to some temporary location, and then iterating that list to delete. :(

I decided to automate conversion of MySQL database to Firebird. I got
tired of manual find&replace process, and created a small parser that
changes from MySQL to Firebird syntax. Currently it only supports
database structure dump (no data, only structure) from PHPMyAdmin. Not
all datatypes are supported, but feel free to “fix” it. It’s written
in PHP and it’s open source. It would be great if you would send back
any modifications you make. Download it here (source code included):

http://www.countdowngames.tv/mysql2firebird.tgz

Please note that this is quick&dirty converter, so make sure you test
the output before using it in production. It does support some tricky
stuff like auto increment columns and LAST_INSERT_ID. There is a
stored procedure for this at the end of the script, you can call it
like this:

select id from last_insert_id;

On Windows, you can run the program like this:

php.exe -f mysql2firebird.php mysql_dump.sql > firebird_dump.sql

Once you create the Firebird script, make sure you search for text
WARNING: which might indicate some incompatibilities. For example,
Firebird does not have tinyint, mediumint and unsigned datatypes.
Also, check for TEXT and LONGTEXT. I’m using UTF8, so I converted
those to varchar(8000), which might not be what you want. Maybe BLOB
SUB_TYPE 0 would be a better choice, but I don’t like PHP support for
it, so I’m using varchar instead. Maybe some command line options
could be added to the tool to support different behavior.

This program covers all the databases I had to convert so far. If you
are interested in improving it, please contact me.

MySQL fixed vs dynamic table

I have a MySQL table with 16 million records using MyISAM format. I
tried to converting it to fixed as opposed to dynamic format hoping to
improve performance. However, it killed my RAM and CPU. Here’s the
table structure:

CREATE TABLE IF NOT EXISTS `poeni` ( 
 `userid` int(11) NOT NULL, 
 `naziv_igre` varchar(11) NOT NULL, 
 `datum` date NOT NULL, 
 `poena` int(11) default NULL, 
 `podaci` varchar(65) default NULL, 
 `zapoceto` datetime default NULL, 
 `zavrseno` datetime default NULL, 
 PRIMARY KEY (`userid`,`datum`,`naziv_igre`), 
 KEY `zapoceto` (`zapoceto`), 
 KEY `datum` (`datum`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I created a new table named ‘poenifixed’ using ROW_FORMAT=FIXED option
and copied the data using INSERT INTO … SELECT * FROM…. It took
about 24 minutes. Then I created indexes on the new table. It took 35 
minutes and 23 minutes respectively. While creating index MySQL also
created temporary files as big as the table file. I have no clue why.

As expected, a new table has much bigger filesize, 4GB vs 1GB for
dynamic. I could live with that though. So, I put the new table in
production and 20 minutes later all the RAM on machine was consumed :(

I suspected something else is wrong, but couldn’t find anything. So I
rebooted Linux, and it started working nice again, although I did not
see any speed improvement. About half an hour later, the system load
when sky-high, over 90. I took me a few minutes just to log into the
box remotely. Now I switched back to dynamic table, inserted the
records that where inserted by the users in the meantime and let it
run. Everything works as before.

I really don’t understand why would a fixed table format require more
CPU power or RAM. It should have been less in theory.

I have a heavy-used website powered by LAMP stack (CentOS Linux,
Apache 2, MySQL and PHP). It started on a shared hosting so I had to
use MySQL. Year and a half later, I switched shared, virtual hosting
and not run it on a dedicated server. I decided to try Firebird to see
how it performs and also how it compares to MySQL in RAM usage, disk
usage, etc.

The software

The system is CentOS 5.5 64bit with default LAMP stack. I installed
Firebird 2.5. RC3 from the .rpm package on Firebird website.
Surprisingly, it does not require any additional rpm package :)

Converting the database

As far as I can tell, there are no tools to do this automatically. I
created Firebird database and tables by hand, slightly editing the
schema dump from phpMyAdmin. This was easy. Loading the data seemed a
problem because default mysqldump places multiple VALUES clasuses in
INSERT statements. I used a Postgres tool mysql2pgsql to convert the
file to a more usable form:

http://pgfoundry.org/projects/mysql2pgsql/

I had to alter it a little bit, to avoid prefixing strings with E
character. I commented out this line:

#s/'((?:.*?(?:\\')?.*?)*)'([),])/E'$1'$2/g;

# for the E” see
http://www.postgresql.org/docs/8.2/interactive/release-8-1.html

Next problem was that ” and ’ are escaped with backslash .

With Firebird ” does not need escaping and ’ is escaped with another
’, becomes ”. A simple sed command to fix this:

cat postgres.sql | sed s1\\\\\"11g | sed s1\\\\\'1\'\'1g > firebird.sql

A few more manual edits were needed to remove the CREATE TABLE and
similar stuff, because I only needed data. After that I added
“commit;” to the end of the script and ran it via isql:

/opt/firebird/bin/isql /var/db/firebird/s.fdb -user sysdba -pass 
******** -i firebird.sql

this took some time. Here is the result:

# du -h -s /var/lib/mysql/slagalica/
1.9G /var/lib/mysql/slagalica/
# du -h -s /var/db/firebird/slagalica.fdb 
2.1G /var/db/firebird/slagalica.fdb

This is before I created indexed on tables in Firebird database.
Afterwards we get:

2.3G /var/db/firebird/slagalica.fdb

So, Firebird database is slightly bigger.

Now, it’s time to convert the DB access layer in PHP application, and
compare the perfomance. Stay tuned…

I’m moving one of my websites to a different server, and part of it is
moving a MySQL database. It has different international users and a
lot of data in UTF-8 character set which does not fit into default ISO
8859-1 space. Using phpMyAdmin (no other way on old host) I backed up
the entire database into .sql file encoded with UTF8, but when I
imported it from the command line using:

mysql -p mydatabase

however, all the non-ISO8859_1 characters got busted and don’t display
correctly. Solution is to supply the connection character set, so all
data is transferred as UTF-8:

mysql -p --default_character_set utf8  mydatabase < dump.sql