patternsqlMinor
Handling a big table in mysql
Viewed 0 times
handlingbigmysqltable
Problem
I have a really big table (approx. > 100.000.000 rows and a size of > 50GB) and it's getting a big performance kill right now. Beneath the primary key (id) it uses a fulltext key on a varchar(500) field to use the mysql fulltext search option.
However, saving and getting rows to this table is getting really slow right now.. How can I handle this? This is my first problem. My second problem is, that getting a backup with mysqldump of this table is no use, because it would take months to import it back. The third problem is, that this table uses the engine MYISAM and a conversion to INNODB is also not possible (I've tested it and aborted the proccess after 72 hours).
So, what would be a good future-proof approach to speed this table up, back it up correctly and maybe convert it to INNODB? (INNODB should accept FULLTEXT with my mysql version)
However, saving and getting rows to this table is getting really slow right now.. How can I handle this? This is my first problem. My second problem is, that getting a backup with mysqldump of this table is no use, because it would take months to import it back. The third problem is, that this table uses the engine MYISAM and a conversion to INNODB is also not possible (I've tested it and aborted the proccess after 72 hours).
So, what would be a good future-proof approach to speed this table up, back it up correctly and maybe convert it to INNODB? (INNODB should accept FULLTEXT with my mysql version)
Solution
I'd suggest a more radical approach. For databases of your size a fulltext search is not only ineffective, but inefficient. I'm guessing that there's some kind of user-driven search function requiring your index.
How about you employ a real search engine? This would take the load of key generation and reordering off of your database. It would give you the chance to offload the load to even another machine.
Take a look at Apache Solr, a well-received, fast implementation based on Lucene. Many large non-profit and commercial websites use it with success.
Then, remove the fulltext indexing from your table. Inserts should then fly into the table with only the ID key left.
Also, if you regularily remove rows from the table, OPTIMIZE TABLE should be done regularily.
For backup purposes, you might consider replication. There are multiple ways to implement the replication, and all of them spread the load out over time, instead of creating a downtime for it, which is what you have now. As an added benefit some replications can produce a database that can be used as a stand-in replacement if the primary database fails, so that the application can be up again in little to no time.
How about you employ a real search engine? This would take the load of key generation and reordering off of your database. It would give you the chance to offload the load to even another machine.
Take a look at Apache Solr, a well-received, fast implementation based on Lucene. Many large non-profit and commercial websites use it with success.
Then, remove the fulltext indexing from your table. Inserts should then fly into the table with only the ID key left.
Also, if you regularily remove rows from the table, OPTIMIZE TABLE should be done regularily.
For backup purposes, you might consider replication. There are multiple ways to implement the replication, and all of them spread the load out over time, instead of creating a downtime for it, which is what you have now. As an added benefit some replications can produce a database that can be used as a stand-in replacement if the primary database fails, so that the application can be up again in little to no time.
Context
StackExchange Database Administrators Q#36050, answer score: 4
Revisions (0)
No revisions yet.