MySQL InnoDB + high table creation-destruction rate

innodbMySQL

We are transferring a microsoft SQL based website (+app + plugins) structure to MySQL.

The website has a high "Content + Comments + Activity Tracking" behaviour.

Mainly because of the activity tracking, InnoDB structure looks like a must (high read, insert, update and truncate rates). All the activity is written intensively in a table for each content unity and it's periodically "compressed" to shrink the table.

To reduce the amount of tables in the DB, only the tables related to often accessed content are kept in the DB. Others are "packed" and deleted.

If any content becomes "active" again then we "unpack" and recreate the tables.

Our total amount of tables is about 16 million a year but, daily, we normally create and delete about 5k of those tables and about 50k remain always "unpacked" in the DB.

What kind of memory and HD space leak problems can this structure give us in MySQL-InnoDB? Is this a good practice or should we consider a structure change?

Best Answer

What kind of memory and HD space leak problems can this structure give us in MySQL-InnoDB?

Given your description it's impossible to give any meaningful value to memory or storage requirements. You say "16 million per year" but what is that? 16 millions rows/tables? With no schema example it's still impossible to estimate. For tables you do have you can run

select table_name, data_length, index_length, data_free from information_schema.tables where table_schema='mydatabase';

To get a feel for current storage requirements. What you'll be more interested in is growth rate over time though to ensure you're not going to run out of space in 3months down the road. MySQL doesn't give a built in growth rate metric so that's something you'll need to periodically poll and record yourself.

For memory requirements, in a perfect world your innodb_buffer_pool would be big enough to accommodate all your data and indexes in memory. It's never a perfect world though and can become impractical or cost prohibitive to have such a machine. At the same time, strive to make the buffer pool as large as you can to keep most frequently accessed data in memory.

If you are straight up dropping tables the storage space will be reclaimed to the file system if and only if you are configured with innodb_file_per_table = true. Other wise all your data is stored along with metadata and transaction history in ibdata1.

Is this a good practice or should we consider a structure change?

It's not unheard of to offline old historical data. However your description makes it seems like this becoming active is some what common. I can't imagine any on demand process where you have to rebuild a table before being able to service the request.

Rebuilding a table and it's indexes is much slower then a raw copy of a file of similar size to the final table.

You say it's being "packed" and "unpacked" indicating it's still being stored somewhere. If it's going to be needed to be accessible by an application just keep it in the database and let old pages fall out of the buffer pool. When requested again the information needs to be read from disk versus having a table completely rebuilt.

ASPECT #1

The MEMORY table behaves like a MyISAM table in that it performs a full table lock when doing INSERTs, UPDATEs, and DELETEs (MySQL Documentation says Locking granularity : Table). I would be very concerned with a high lock ratio if it bottlenecks your I/O performance. If 300 INSERTs per second is coming at this table, you can be fully confident that there will be 300 full table locks per second.

ASPECT #2

No matter how much data you load into a MEMORY table, mysqld will always contact the .frm file of the MEMORY table to verify the table's existence before query parsing. Although the .frm is not a large file, it will incur a very tiny amount of disk I/O. Thousands, or even just hundreds, of queries against a MEMORY table can reveal a noticeable, even a significant, amount of disk I/O.

Since this is the case with the MEMORY storage engine, you must also avoid MyISAM because it has the same locking granularity (Full Table Locking).

Here are my recommedations

Recommedation #1

Cleanup InnoDB so that it has innodb_file_per_table enabled.

Recommedation #2

Use a large InnoDB Buffer Pool

Since the table is 200MB, you can make InnoDB Buffer Pool 5 times as large (restart required)

[mysqld]
innodb_buffer_pool_size=1G

Recommedation #3

Upgrade to MySQL 5.5 in order to have multithreaded reads and write that engages multiple CPUs for InnoDB. If you have InnoDB 5.1.38+ and are using the InnoDB Plugin, the same options that engages multiple CPUs for InnoDB already exists.

Recommedation #4

Common sense would tell me to recommend turning the table back to InnoDB

Mysql – How to configure MySQL Innodb to handle 1000s of inserts per hour

You are dealing with a deadlock, not a performance bottleneck issue.

If you have a thousand new records per hour, you are far far far away from reaching MySQL limits. MySQL can handle at least 50 times your load.

Deadlocks are cause by application code and are not the database server's fault. Deadlocks can not be fixed on the MySQL server side, except in some specific situations.

InnoDB can show you detailed deadlock information by running SHOW ENGINE INNODB STATUS at the MySQL prompt, or with mysql -uroot -p... -e "SHOW ENGINE INNODB STATUS".

However, this only shows the last deadlock that occurred, there is no deadlock log.

Thankfully, there's a tool pt-deadlock-logger which takes care of that problem, it takes care of polling InnoDB status and saves all the detailed deadlock information before it's refreshed with a new deadlock.