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