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.