Why is myisam slower than Innodb

1
votes

There are several Q&A for "Why is InnoDB (much) slower than MyISAM", but I could not find any topic for the opposite.

So I had a table defined as InnoDB wherin I stored file contents in a blob field. Because normally for that MyISAM should be used I switched over that table. Here is its structure:

CREATE TABLE `liv_fx_files_files` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `filedata` longblob NOT NULL,
  `filetype` varchar(255) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `filesize` int(11) NOT NULL,
  `context` varchar(1) NOT NULL DEFAULT '',
  `saveuser` varchar(32) NOT NULL,
  `savetime` int(11) NOT NULL,
  `_state` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`fid`),
  KEY `_state` (`_state`)
) ENGINE=MyISAM AUTO_INCREMENT=4550 DEFAULT CHARSET=utf8;

There are 4549 records stored in it so far (with filedata going from 0 to 48M. Sum of all files is about 6G.

So whenever I need to know current total filesize I issue the query

SELECT SUM(filesize) FROM liv_fx_files_files;

The problem is that since I switched from InnoDB to MyISAM this simple query lasts really long (about 30sec and longer) whereas on InnoDB it was done in a under one second.

But aggregations are not the only queries which are very slow; it's almost every query.

I guess I could fix it by adopting config (which is currently optimized for InnoDB (only) use), but don't know which settings to adjust. Does anyone have a hint for me please?

current mysql server config (SHOW VARIABLES as csv)

Example for another query fired on both table types (both contain exact same data and have same definition). All other tested queries behave the same, say run much longer against MyISAM table as InnoDB!

SELECT sql_no_cache `fxfilefile`.`filename` AS `filename` FROM `myisamtable`|`innodbtable` AS `fxfilefile` WHERE `fxfilefile`.`filename` LIKE '%foo%';
3
mysqlinnodbmyisamquery-performance
Post current config file, probably you're using a very small footprint configuration. Also consider adding index on filesize field. - Ghigo
@Ghigo thanks, adding index solves speed problem with the aggregation of that column, but does not improve speed for other queries. config file added - toshniba
This doesn't help much: for MyISAM tables the caching is handled by the underlying OS, The space available for this isn't visible from inside MySQL. - symcbean
It's better if you include /etc/mysql/my.cnf directly - Ghigo
This is one of the reasons why I am so opposed to saving files as blobs. If you didn't have that blob, you would be having a tiny database 4500 entries. Probably less than a mega byte in size. That would be blazing fast on any table type even without an index. - e4c5

3 Answers

3
votes

Executive Summary: Use InnoDB, and change the my.cnf settings accordingly.

Details:

"MyISAM is faster" -- This is an old wives' tale. Today, InnoDB is faster in most situations.

Assuming you have at least 4GB of RAM...

  • If all-MyISAM, key_buffer_size should be about 20% of RAM; innodb_buffer_pool_size should be 0.
  • If all-InnoDB, key_buffer_size should be, say, only 20MB; innodb_buffer_pool_size should be about 70% of RAM.
  • If a mixture, do something in between. More discussion.

Let's look at how things are handled differently by the two Engines.

  • MyISAM puts the entire BLOB 'inline' with the other columns.
  • InnoDB puts most or all of each blob in other blocks.

Conclusion:
A table scan in a MyISAM table spends a lot of time stepping over cow paddies; InnoDB is much faster if you don't touch the BLOB.
This makes InnoDB a clear winner for SELECT SUM(x) FROM tbl; when there is no index on x. With INDEX(x), either engine will be fast.

Because of the BLOB being inline, MyISAM has fragmentation issues if you update records in the table; InnoDB has much less fragmentation. This impacts all operations, making InnoDB the winner again.

The order of the columns in the CREATE TABLE has no impact on performance in either engine.

Because the BLOB dominates the size of each row, the tweaks to the other columns will have very little impact on performance.

If you decide to go with MyISAM, I would recommend a 'parallel' table ('vertical partitioning'). Put the BLOB and the id in it a separate table. This would help MyISAM come closer to InnoDB's model and performance, but would add complexity to your code.

For "point queries" (looking up a single row via an index), there won't be much difference in performance between the engines.

Your my.cnf seems antique; set-variable has not been necessary in a long time.

0
votes

Try to edit your MySQL config file, usually /etc/mysql/my.cnf and use "huge" preset.

# The MySQL server
[mysqld]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock
skip-locking
set-variable    = key_buffer=384M
set-variable    = max_allowed_packet=1M
set-variable    = table_cache=512
set-variable    = sort_buffer=2M
set-variable    = record_buffer=2M
set-variable    = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable    = thread_concurrency=8
set-variable    = myisam_sort_buffer_size=64M
0
votes

Certainly 30 seconds to read 4500 records is very slow. Assuming there is plenty of room for I/O caching then the first thing I would try is to change the order of the fields; if these are written to the table in the order they are declared the DBMS would need to seek to the end of each record before reading the size value (I'd also recommend capping the size of those vharchar(255) columns, and that varhar(1) NOT NULL should be CHAR). 

CREATE TABLE `liv_fx_files_files2` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `filesize` int(11) NOT NULL,
  `context` char(1) NOT NULL DEFAULT '',
  `saveuser` varchar(32) NOT NULL,
  `savetime` int(11) NOT NULL,
  `_state` int(11) NOT NULL DEFAULT '0',
  `filetype` varchar(255) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `filedata` longblob NOT NULL,
  PRIMARY KEY (`fid`),
  KEY `_state` (`_state`)
) ENGINE=MyISAM AUTO_INCREMENT=4550 DEFAULT CHARSET=utf8;

INSERT INTO liv_fx_files_files2
(fid, filesize, context, saveuser, savetime, _state, filetype, filename, filedata)
SELECT fid, filesize, context, saveuser, savetime, _state, filetype, filename, filedata
FROM liv_fx_files_files;

But ideally I'd split the data and metadata into separate tables.