Clever Geek Handbook

Will creating innodb table and partitioning improve performance in mysql?

I have a Myisam table with a complex unique key of 2 columns and 90 million data. We are now facing memory and loading issues and after going over the internet I plan to enable partitioning and change this table to Innodb for better performance. But I have the following problems:

  • Moving to innodb will have huge downtime, is it possible to minimize downtime?

  • Most of the select query is on a specific key column on which I plan to have a hash split, how much will this affect the query on a different key column?

Will these changes improve performance up to the theoretical amount? Is there a better solution for such cases. Any suggestion or experience might be helpful.

My queries are as simple as

Select * from Table where Col1= "Value"


Select * from Table where Col1="Value" and Col2 IN (V1,V2,V3)

Insertions are very common.

+3


source to share


3 answers


InnoDB will probably help some. There are some problems for converting to InnoDB as I state in My Conversion Blog .

The separation does not, in fact, provide a performance gain. My splitting blog has 4 cases where you can get performance with design changes.

Regardless of Engine, your two queries will be helpful

INDEX(col1, col2)


No form of separation will help. HASH

Separation is particularly useless.

The conversion to InnoDB will take a long time if it pt-online-schema-change

doesn't work for your case. Study this.

Also read my answers and comments on Can I configure Mysql to auto-split? for more details.

Perhaps adding this index is a major performance gain. But you have to make a long ALTER

one to get it. MyISAM does not ALGORITHM=INPLACE

.

+1


source


Innodb (which we are talking about now) only makes sense when the table has a lot of inserts and updates due to the row locking table. If most of the queries in your table are SELECTs, MyIsam will be faster. Hint: enter my.cnf key_buffer_size equal to 25% free memory.



0


source


If inserts are very frequent in your database, you will most likely get performance by switching to innodb, which will not lock entire tables for inserts, while allowing other clients to fetch data at the same time.

Regarding question # 1, if you are worried about downtime, I would suggest you find a parallel dump / load solution to dump your data into innodb. if you just run the ALTER statement on your tables, this is a single threaded operation that will be much slower.

As for # 2, you will have to publish the schema along with your partitioning strategy and the queries you are worried about.

0


source






More articles:


All Articles