I have BAD NEWS / GOOD NEWS for you
BAD NEWS
MyISAM is simply not suitable for you because...
- MyISAM is not a transactional storage engine
- Writes to a MyISAM cannot be rolled back
- MyISAM tables that are open crash very easily if the mysqld process or DB server crashes
GOOD NEWS
Using InnoDB is absolutely in your best interests. You will have to do some work for this to become a reality.
STEP 01 : Create a Script that will Convert all tables to InnoDB
Conversion of all tables to InnoDB is incredibly straightforward.
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM'"
SQL="${SQL} AND table_schema NOT IN ('information_schema','mysql','performance_schema')"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -ANe"${SQL}" > /root/ConvertMyISAMToInnoDB.sql
STEP 02 : Configure InnoDB Settings for Multithreading and Caching
If the DB Server has 8GB RAM, use the following settings in /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table
innodb_buffer_pool_size=4G
innodb_log_file_size=1G
innodb_log_buffer_size=32M
innodb_open_files=16384
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=5000
key_buffer_size=8M
STEP 03 : Create new InnoDB Logs
cd /var/lib/mysql
service mysql stop
rm -f ibdata1 ib_logfile0 ib_logfile1
service mysql start
STEP 04 : Perform InnoDB Conversion
mysql ${MYSQL_CONN} < /root/ConvertMyISAMToInnoDB.sql
STEP 05 : Make changes in your code for transactional support
You will have to employ the use of three commands : 1) START TRANSACTION; 2) COMMIT; 3) ROLLBACK;
Before you starting writing new data, precede it once with
START TRANSACTION;
START TRANSACTION;
will create an initial checkpoint in the event you want to
- commit all changes to the database (via COMMIT)
- abandon all subsequent changes (via ROLLBACK)
Thus, in your PHP code, you must strategically place COMMIT;
commands in places your know you want to fully submit all INSERTs, UPDATEs, and DELETEs of all InnoDB tables. In like fashion, you must also place ROLLBACK;
commands in places where you wish to abandoned compiled changes to InnoDB tables.
CAVEAT
If you still want to the data as MyISAM, I have an additional suggestion.
Get another DB Server. Load all data into the second server as follows:
STEP 01 : service mysql stop
STEP 02 : Add this to /etc/my.cnf
[mysqld]
skip-innodb
STEP 03 : service mysql start
STEP 04 : Load the data into the second server
mysql -hIPofSecondServer ${MYSQL_CONN} < /root/ConvertMyISAMToInnoDB.sql
STEP 05 : Setup replication from InnoDB Server to MyISAM server
That way, you can change back to MyISAM by just failing over to the server that's MyISAM
Best Answer
This would depend upon the queries you issue (suppose the large table is
mydb.logtable
).ASPECT #1
For starters, think about the MyISAM storage engine. It only caches index pages in the MyISAM key cache (sized by key_buffer_size). If any queries against
mydb.logtable
reads a significant number of index pages from/var/lib/mysql/mydb/logtable.MYI
, the query mya purge out index pages from other tables. This would causes other queries for those other table to have to reread them index pages from disk again.ASPECT #2
Any queries against a large MyISAM table that is infrequently accessed may have old index statistics, possibly resulting in full table scans down the road. During off hours, you should setup a crontab job that runs this SQL command:
This will rebuild the index statistics.
ASPECT #3
Regardless of Storage Engine (or even RDBMS), any query that requests more that 5% of a table's total size would cause the MySQL Query optimizer to stop using indexes and do full table scans. For example, if you query data for a year and a table contains less than 20 years of log info, it is very probable that a full table scan will happen.
ASPECT #4
If the cardinality of the indexes is very low, a certain key combination could result in a query not using any indexes and do a full table scan.
For a demonstration of low cardinality affecting query performance and EXPLAIN plain generation, see my
Nov 13, 2012
post Must an index cover all selected columns for it to be used for ORDER BY?SUGGESTION
You should schedule some form of log rotation. Perhaps this:
This will keep the log file lean and mean and archive the most recent entries.