1

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?

0

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.

  • This is pretty much what i expected. The innodb_file_per_table = true in theory solves the HD space leak. The only reason for "packing" the tables is to reduce the number of tables in the DB. Normally "packed" tables hardly become unpacked again. The point is not having +16 million new tables per year in the DB because of the performance drop. – SaintJob 2.0 Nov 11 '14 at 15:25
  • If your db isn't running file per table currently the only way to convert is to reload into a new mysqld running w/ that config set from the get go. – atxdba Nov 11 '14 at 15:46
  • We are still not using it, so we can configure everything from scratch. – SaintJob 2.0 Nov 11 '14 at 16:07
  • If you're porting everything over into mysql perhaps now is a good time to work with the rest of the engineering team to figure out if your current paradigms of 16 million tables being created and destroyed makes sense before just blindly translating the usage pattern. – atxdba Nov 11 '14 at 16:57
  • Of course... i was just asking for a plain platform transfer just as it is now, but the project final objective is in fact adding a javascript program layer in the server to directly manage the main activity avoiding almost all sql calls. – SaintJob 2.0 Nov 11 '14 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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