6

I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway:

I have a MyISAM table in my database that stands central to my site. There are many reference tables created around it and many other tables that link directly to row ids from this table. The table is about 260k rows in size, with 28 fields which for the most part is varchars and ints. I have about 15 indexes on it (the data is about 70 MiB, indexes are about 80 MiB). There are maybe several hundred writes a day, versus the tens of thousands of reads. My databases are located on an SSD drive.

Now my question is; would be be beneficial to alter this table to a InnoDB table? I am working on extending my systems to include API traffic as well, meaning that the table will even the queried even more and I want to make sure that everything runs as smooth as possible. Because of this, I'm slightly leaning towards converting it to InnoDB. However, since the table requires many indexes, I'm thinking MyISAM might work more effectively. Can anyone help me make up my mind? Thanks in advance1

Edit: (added by jcolebrand from a comment)

I have added the indexes a long time ago and I changed some things to the PHP code base, so maybe some indexes are out-dated, but overall; yes, they are all used. I run many different types of queries on the table. Different query, different index. But my main question here does not concern the table read performance, but how it performs with many indexes. InnoDB indexes are 'leaved'/ stored with the data, instead of MyISAM's key-file principle. I am just worried that this might cause enough of a performance drop not to outweigh the table lock issue.

4
  • 1
    InnoDB supports transactions, but the additional overhead carries a performance penalty. It's a trade-off and the most appropriate engine for your needs will depend on your own requirements.
    – eggyal
    Jul 3, 2012 at 22:57
  • would innodb clustered indexes significantly improve read times in your scenario ? stackoverflow.com/questions/4419499/…
    – f00
    Jul 4, 2012 at 10:35
  • I have several clustered indexes. They do improve performance a lot, but there is redundancy in the indexes this way. Regardless, the way I see it is that InnoDB only out-performs the key-file system is if the table has one (or at least very few) indexes if it uses all the indexes for different situations. In the case of clustered indexes, it would only work better if it replaces the pk_index.
    – Battle_707
    Jul 4, 2012 at 14:49
  • If you switch to InnoDB (which I would highly recommend) you need to make sure you configure InnoDB properly specifically the buffer pool size: mysqlperformanceblog.com/2007/11/03/… Jul 6, 2012 at 19:37

3 Answers 3

6

There are maybe several hundred writes a day, versus the tens of thousands of reads. My databases are located on an SSD drive.

Based on this statement, let's play with some numbers. Lets's say there are 500 writes a day and 20,000 reads a day. That computes out to the following

  • 97.56% reads per day
  • 2.44% writes per day
  • 40 reads / write

As much as I love InnoDB, I would have to choose MyISAM in this case for several reasons

REASON #1

You only have 150MiB of MyISAM tables (70MiB Data, 80 MiB Indexes)

REASON #2

InnoDB indexes tend to get very bloaty because Secondary Indexes have keys into the Clustered Index. This always results in a double index lookup. This can be overlooked with large, write-heavy datasets.

REASON #3

InnoDB tablespaces tend to get very bloaty because of MVCC being created and discarded without an automatic reclaiming of disk space:

All this can be avoid with MyISAM

REASON #4

InnoDB protects individual rows by performing MVCC for transaction control. The overhead generated for reads in a day would probably be greater than 150 MiB.

I can probably name 2 or 3 more reasons, but let's cut to the chase: Is there anything that can improve performance for MyISAM in your case? Why, yes there is.

Your said the following

The table is about 260k rows in size, with 28 fields which for the most part is varchars and ints

If you have many varchars, there is something you can do to increase read/write performance. For any MyISAM table mydb.mytable: run this command:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

What will this do, this will treat all VARCHARs as CHARs. Every row will be the exact same length. This will increase disk space 80%-100%. In your case, let's assume it doubles your 150 MiB MyISAM table to 300 MiB. Where is the benefit? Your MyISAM table can now be read/written anywhere from 20% - 30% faster without changing anything else I learned that from pages 72,73 from MySQL Database Design and Tuning.

I have written about this in the past:

1

As for the kind of usage you do from your table (write wise), I'ld suggest you switch to innoDB for 2 reasons:

  • Row Locks instead of table locks: reduces risks of deadlocks or waits
  • Database integrity native checking

About the performances at read time: I don't think you should be worried switching to innodb with your volumes. Here and there we can read about millions of rows databases under innodb without any problem.

tip: are you sure your indexes are all used? Don't forget mysql for the moment only uses one per (query, table reference)

0
0

No, I don't think it would help at all. I have a very similar situation to what you describe, except my main table is closer to 800k rows, and I have RAID, but not SSD like you. Reads are most of my db interaction, like you, and I have as much index data as data. My research led me to use MyISAM, and the results have been nothing short of spectacular.

1
  • FWIW, as the useage of my system grew, and more reads came in, I decided to build a flowgate of sorts. I have another database which accepts incoming data as it comes (in spurts at irregular intervals) and then i have 'worker bee' cron jobs running, that update a certain number of records every minute, transferring the data from the incoming db to the application heavily read db in very short, predicatble increments.
    – colonelclick
    Jul 3, 2012 at 23:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.