There comes a point when a step-by-step clean-up becomes more work than a clean slate and migrate approach. System availability and time to migrate may factor in to the decision when dealing with larger volumes but at this size, not an issue.
Key factors for me here are:
- Renaming foreign key constraints to fit a new application framework.
- Refactoring a significant proportion of existing tables.
- Low volume of data.
In this situation I'd be very tempted to design a new schema that fits the model you now require and create the necessary scripts to migrate data across (your option 4).
At first glance, I would look over
To properly judge the first two values, you should run this query:
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;
This will tell you the ideal buffer pool size for the give dataset in MB. The innodb_buffer_pool_size
should never be larger that 75% of installed RAM.
As for the log file size, I have two suggestions (pick one)
Suggestion 1: Set the innodb_log_file_size
to be 25% of innodb_buffer_pool_size
Suggestion 2: If you want to set the log file size based on that actual amount of log file writes per hour, see my Aug 27, 2012 post Proper tuning for 30GB InnoDB table on server with 48GB RAM and my Feb 16, 2011 post How to safely change MySQL innodb variable 'innodb_log_file_size'?
As for innodb_log_buffer_size
, you should max it out to 8M
. The larger the innodb_log_buffer_size
, the less flushing of transactions will occur.
EPILOGUE
Your original question was What's the most unsafe MySQL configuration?
. The real answer to that is : a MySQL instance with no config file whatsoever !!! ;-)
Best Answer
There are two major things you can change. One involves the table and one involves a config setting.
TABLE CHANGE
You can increase SELECT and INSERT speed by changing the MyISAM table's row format to FIXED. In order to do do, you would run
There is a drawback to this: This could potentially increase the table size 80-100%
I have discussed changing MyISAM row format before
May 10, 2011
: What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?Aug 12, 2011
: Which DBMS is good for super-fast reads and a simple data structure?Jul 06, 2012
: MySQL table with 100,000 records queried oftenSep 26, 2012
: Choosing MyISAM over InnoDB for these project requirements; and long term optionsCONFIG SETTING
MyISAM supports a special protocol called a concurrent insert. It works by appending new rows to a MyISAM as long as there are no gaps as a result of DELETEs.
Here is some good news: you can disable the gap check for concurrent insert
According to the MySQL Documentation on
concurrent_insert
, here is what happens for value 2:If the table is being heavily read, new INSERTs just get appended. When you are the only DB connection with a file handle to the table, then a normal gap check is done.
See the MySQL Documentation on Concurrent Inserts
EPILOGUE
These two changes combined may produce surprising performance results. Why? Using
ROW_FORMAT=Dynamic
would trigger a search for a gap large enough to hold a new row. If the table hasROW_FORMAT=Fixed
, all gaps are the same size. A new row insertion would be as quick as possible because the first gap row searched is used immediately for the insertion.