Handling a big table in mysql

Question

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)

Answer 1

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.

Answer 2

This is not a simple question and will take some more effort to answer.

For a start, please post your tables schema, so people can take a closer look.

Some general advice:

Performance

To find out what is eating your performance, try profiling your statements which are INSERTing and SELECTing rows.

Example:

1. Turn on the profiler:

   SET profiling = 1;

2. Execute your INSERT or SELECT statement.

3. View the profilers result:

   SHOW PROFILES;

This will return something like this:

Query_ID |  Duration | Query
---------+-----------+-----------------------
  ...    | ...       | ...   
   29    | 0.0006200 | SHOW STATUS
   30    | 0.3600000 | (your query here)
  ...    | ...       | ...

For this example, show the details for Query_ID 30:

SHOW PROFILE FOR QUERY 30; 

...and you'll see what is the slow part of this statement. Depending on the reason you can take measures to optimize the behaviour, even if it comes down to simple hardware related things like faster hard discs etc.

Backup

With huge tables like this, conventional backups like mysqldump just take very long. You might want to consider different backup strategies. If you're using MyISAM it might be way faster using file-based backup to a different partition, then moving the files to your backup devize. You might want to search for professional alternatives, p.e. Percona XtraBackup or similar tools, too.

Another approach would be to set up replication.

InnoDB

Since MySQL 5.6 you can use fulltext on InnoDB, too. It promises significant performance boosts, which I haven't tried so far. Please be aware, this affects your system in more ways:

• Using InnoDB you have to deploy a different backup strategy.
• The results differ alot from FULLTEXT with MyISAM before 5.6. Test this to make sure it doesn't mess up your application.

Answer 3

If your table is mainly used in WRITING actions ( INSERT / UPDATE ), you should use MyISAM.

If your table is mainly used in READING actions ( SELECT ), you should use InnoDB.

However, you should consider housekeeping your table and add appropriate index to columns.

Answer 4

Do you need all of the data in this table, or can you purge some of it?

If you do need access to all of the data, can you separate it into a "hot" set that you need regular access to, and a "cold" set that you need once-in-a-while access to?

What kind of queries are you running? Could you summarize some of the data in another table for querying? For example, if you are pulling counts of fields, you could just store/update the counts in another table.

Tell us more.