Migrate MySQL Databases from MyISAM to InnoDB

[chris]

This should result in things being faster, however there are some potential new issues that it would create, see for example ​The perils of InnoDB with Debian and startup scripts.

There is also an issue with the one !InnoDB table on kiwi which will need investigation and fixing, the dev Mediawiki database backup fails each night, this is the error:

Warning: mysqldump: Got error: 1286: Unknown table engine 'InnoDB' when using LOCK TABLES
Warning: Failed to dump mysql databases transwiki

[chris]

The !InnoDB problem on kiwi appears to related to it not being enabled, this is shown by this:

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

Despite this the dev version of the MediaWiki site is working OK, ​http://wiki.dev.transitionnetwork.org/ these are the settings the my.cnf file contained:

# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
innodb_log_file_size                  = 8MB
innodb_buffer_pool_size               = 32M

And the files in /var/lib/mysql:

-rw-rw----  1 mysql mysql  18M Dec  5 21:08 ibdata1
-rw-rw----  1 mysql mysql 5.0M Dec  5 21:08 ib_logfile0
-rw-rw----  1 mysql mysql 5.0M Oct 31 10:22 ib_logfile1

Following the suggestions here, ​http://blog.coldtobi.de/1_coldtobis_blog/archive/300_enabling_innodb_support_in_debian_mysql.html the following was added to /etc/mysql/my.cnf:

innodb_data_home_dir =
innodb_data_file_path = /var/lib/mysql/ibdata1:20M:autoextend

However this didn't result in !InnoDB being listed as supported, so more work is needed on this... The good news is that the live server don't have this issue, but I want to get !InnoDB working on the dev server so that we can test the migration from !MyISAM before doing it on the live database.

[jim]

I've moved each Drupal site I get to InnoDB with a ALTER table XXX engine=InnoDB; for each table.

Note that depending on how new MySQL is tables with long text (like some of the search_ tables_ will not convert. That's not a problem.

The process will lock each table and convert it, so it might be best to do this when Apache isn't running to reduce the small risk of a auto-increment index going out of sync.

Once converted, a great query for working out a good value for InnoDB buffer pool size is:

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
	LIMIT 0, 100

Finally, there's ​http://mysqltuner.pl/mysqltuner.pl for more goodness, though I've found it always wants MORE for buffers and other values... To be used as a guide only.

[chris]

One suggestion, here, ​http://www.turnkeylinux.org/forum/support/20090111/drupal-6-problem-enable-innodb was that the ib_logfile0 and ib_logfile1 files were corrupted and this could be preventing !InnoDB from working, so this was tried:

cd /var/lib/mysql
/etc/init.d/mysql stop
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
/etc/init.d/mysql start

However !InnoDB is still not listed as supported:

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

Starting !MySQL manually shows the problem:

/etc/init.d/mysql stop
/usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf
120228 10:31:51 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
120228 10:31:51 [Note] Plugin 'FEDERATED' is disabled.
120228 10:31:51  InnoDB: Initializing buffer pool, size = 32.0M
120228 10:31:51  InnoDB: Completed initialization of buffer pool
InnoDB: Error: auto-extending data file /var/lib/mysql/ibdata1 is of a different size
InnoDB: 1152 pages (rounded down to MB) than specified in the .cnf file:
InnoDB: initial 1280 pages, max 0 (relevant if non-zero) pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
120228 10:31:51 [ERROR] Plugin 'InnoDB' init function returned error.
120228 10:31:51 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
120228 10:31:51 [Note] Event Scheduler: Loaded 0 events
120228 10:31:51 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.58-1~dotdeb.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)

This was fixed by moving back the log files and commenting out these lines in my.cnf:

#innodb_log_file_size                  = 8MB
#innodb_data_file_path = /var/lib/mysql/ibdata1:20M:autoextend

Now !InnoDB is listed as supported on kiwi:

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

And the backupninja cron job is now being tested.

[chris]

Regarding the new disk on the live server, it is suggested here, ​http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html that the write-back cache be disabled, however the suggested command doesn't appear to work with this disk:

hdparm -W0 /dev/xvda3

/dev/xvda3:
 setting drive write-caching to 0 (off)
 HDIO_DRIVE_CMD(identify) failed: Invalid argument
 HDIO_DRIVE_CMD(flushcache) failed: Invalid argument
 HDIO_DRIVE_CMD(setcache) failed: Invalid argument
 HDIO_DRIVE_CMD(identify) failed: Invalid argument
 HDIO_DRIVE_CMD(flushcache) failed: Invalid argument
 HDIO_DRIVE_CMD(identify) failed: Invalid argument

Nor does the SCSI version:

aptitude install sdparm
sdparm --all /dev/xvda3 
unable to access /dev/xvda3, ATA disk?

I'm not sure how to proceed with this, for now the disk settings are going to be left at their default.

[chris]

*Jim* see the bottom of this comment for some questions you might have some answers to...

On the live server all the existing tables to checked to see if they are !MyISAM using SHOW TABLE STATUS -- all are !MyISAM:

echo "SHOW TABLE STATUS" | mysql live | grep InnoDB
echo "SHOW TABLE STATUS" | mysql live_sharingengine | grep InnoDB
echo "SHOW TABLE STATUS" | mysql live_workspaces | grep InnoDB
echo "SHOW TABLE STATUS" | mysql stats | grep InnoDB
echo "SHOW TABLE STATUS" | mysql transwiki | grep InnoDB

However on the dev server some of the Mediawiki tables are !InnoDB:

 echo "SHOW TABLE STATUS" | mysql transwiki | grep InnoDB
iwlinks InnoDB  10      Compact 0       0       16384   0       16384   11534336        NULL    2011-10-21 12:34:28     NULL    NULL    binary  NULL
module_deps     InnoDB  10      Compact 2       8192    16384   0       0       11534336        NULL    2011-10-21 12:34:28     NULL    NULL    binary  NULL
msg_resource    InnoDB  10      Compact 8       2048    16384   0       0       11534336        NULL    2011-10-21 12:34:28     NULL    NULL    binary  NULL
msg_resource_links      InnoDB  10      Compact 8       2048    16384   0       0       11534336        NULL    2011-10-21 12:34:28     NULL    NULL    binary  NULL

These tables were converted back to !MyISAM:

mysql> ALTER TABLE iwlinks ENGINE = MyISAM;
mysql> ALTER TABLE module_deps ENGINE = MyISAM;
mysql> ALTER TABLE msg_resource ENGINE = MyISAM;
mysql> ALTER TABLE msg_resource_links ENGINE = MyISAM;

And the backup task was set running.

The new extra disk was mounted on the live server:

cd /var/lib/mysql
mkdir ibdata
chown mysql:mysql ibdata
mount /dev/xvda3 /var/lib/mysql/ibdata

And set to be mounted on boot by adding this to /etc/fstab:

/dev/xvda3 /var/lib/mysql/ibdata ext3 defaults,errors=remount-ro 0 1

On the dev server a partition was created for the !InnoDB data:

cd /var/lib/mysql/
mkdir ibdata
chown mysql:mysql ibdata

Then the server was shutdown and the existing !InnoDB files deleted:

/etc/init.d/mysql stop
cd /var/lib/mysql
rm ibdata1
rm ib_logfile0
rm ib_logfile1

Then the following settings were changed in /etc/mysql/my.cnf:

innodb_log_file_size                  = 8MB
innodb_buffer_pool_size               = 32M

innodb_data_home_dir = /var/lib/mysql/ibdata
innodb_data_file_path = ibdata1:256M:autoextend

And the server was restarted, not using the init script so that the output can be checked:

/usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf

The same thing was then done on the live server, in my.cnf:

innodb_log_file_size                  = 8MB
innodb_buffer_pool_size               = 32M
innodb_data_home_dir = /var/lib/mysql/ibdata
innodb_data_file_path = ibdata1:256M:autoextend

The on the dev server the mediawiki database was converted:

mysql -e "show tables in transwiki;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > transwiki_alter.sql
cat transwiki_alter.sql | mysql transwiki
ERROR 1214 (HY000) at line 36: The used table type doesn't support FULLTEXT indexes

Tables including and after searchindex were not converted, so this table was omitted:

mysql -e "show tables in transwiki;" | grep -v searchindex | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > transwiki_alter.sql
cat transwiki_alter.sql | mysql transwiki

And the following was done for the other tables:

mysql -e "show tables in bbforum;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > bbforum_alter.sql
cat bbforum_alter.sql | mysql bbforum
ERROR 1214 (HY000) at line 3: The used table type doesn't support FULLTEXT indexes

Try again:

mysql -e "show tables in bbforum;" | grep -v bb_posts | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > bbforum_alter.sql
cat bbforum_alter.sql | mysql bbforum

And the others:

mysql -e "show tables in dev;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > dev_alter.sql
cat dev_alter.sql | mysql dev

The above took over half an hour complete, the live server has more resources but bigger databases so it will no doubt take a while -- Jim do we really need to stop apache while it's done or could we put the site into maintenance mode?

The tables get locked for writes while this is happening but reads are allowed, is it worth considering the suggestion here, ​http://mysqldump.azundris.com/archives/76-An-InnoDB-tutorial.html :

During the ALTER TABLE both tables will be locked. You can avoid that with a sequence of

CREATE TABLE b LIKE a;
ALTER TABLE b ENGINE=InnoDB
INSERT INTO b SELECT * FROM a;

This will put only a read lock on a for the duration of the copy process. Also, you may use a LIMIT clause on your INSERT ... SELECT to create only a limited subset of data for experimentation.

These commands have yet to be run on the dev server, no databases have been converted yet on the live server:

mysql -e "show tables in dev_sharingengine;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > dev_sharingengine_alter.sql
cat dev_sharingengine_alter.sql | mysql dev_sharingengine

mysql -e "show tables in devworkspace;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > devworkspace_alter.sql
cat devworkspace_alter.sql | mysql devworkspace

mysql -e "show tables in piwik;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > piwik_alter.sql
cat piwik_alter.sql | mysql piwik

mysql -e "show tables in test_workspaces;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > test_workspaces_alter.sql
cat test_workspaces_alter.sql | mysql test_workspaces

[jim]

Re stopping Apache: this was pure belt and braces stuff from me, the idea being to stop pretty much all Drupal DB writes during the conversion process so that we're guaranteed consistent data.

If you put www.tn.org, workspaces.tn.org AND news.tn.org into maintenance mode and update those databases that'll remove the risk to a very large extent, and the risk was tiny to begin with. Remember 'workspaces' and 'www' sites share a lot of tables, so these will need to be done together. Hence me thinking just turning off Apache for 5 minutes would do the job just as well.

And if you can minimise writes then there's little to no chance of an auto-increment index going out of sync with foreign keys in other tables. Dunno if that query is worth it...

[chris]

Replying to jim:

Once converted, a great query for working out a good value for InnoDB buffer pool size is:

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
	LIMIT 0, 100

Cheers, on the dev server this returns:

+-------------------------------------+
| recommended_innodb_buffer_pool_size |
+-------------------------------------+
| 1080M                               |
+-------------------------------------+
1 row in set (19.82 sec)

Finally, there's ​http://mysqltuner.pl/mysqltuner.pl for more goodness, though I've found it always wants MORE for buffers and other values... To be used as a guide only.

Thanks, this is the output from that, again on the dev server (not all db's have been converted yet):

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.58-1~dotdeb.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 1G (Tables: 606)
[--] Data in InnoDB tables: 823M (Tables: 332)
[!!] Total fragmented tables: 346

-------- Security Recommendations  -------------------------------------------
[!!] User 'jeffrey@localhost' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21h 26m 8s (291K q [3.781 qps], 5K conn, TX: 2B, RX: 111M)
[--] Reads / Writes: 44% / 56%
[--] Total buffers: 130.0M global + 77.2M per thread (151 max threads)
[!!] Maximum possible memory usage: 11.5G (1151% of installed RAM)
[OK] Slow queries: 1% (4K/291K)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Key buffer size / total MyISAM indexes: 32.0M/45.0M
[OK] Key buffer hit rate: 99.4% (1M cached / 5K reads)
[OK] Query cache efficiency: 65.2% (121K cached / 185K selects)
[!!] Query cache prunes per day: 32198
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 4K sorts)
[!!] Temporary tables created on disk: 30% (3K on disk / 12K total)
[OK] Thread cache hit rate: 99% (13 created / 5K connections)
[OK] Table cache hit rate: 28% (1K open / 6K opened)
[OK] Open file limit used: 22% (1K/8K)
[OK] Table locks acquired immediately: 99% (155K immediate / 155K locks)
[!!] InnoDB data size / buffer pool: 823.2M/32.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 32M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    innodb_buffer_pool_size (>= 823M)

I have no idea where this user "jeffrey" came from, I have removed them:

mysql> delete from user where User="jeffrey";
Query OK, 1 row affected (0.10 sec)

And on the unconverted live server:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-3-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 3G (Tables: 664)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 114

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21h 37m 40s (9M q [123.076 qps], 81K conn, TX: 87B, RX: 2B)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 130.0M global + 66.3M per thread (151 max threads)
[!!] Maximum possible memory usage: 9.9G (330% of installed RAM)
[OK] Slow queries: 0% (436/9M)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Key buffer size / total MyISAM indexes: 32.0M/293.5M
[OK] Key buffer hit rate: 99.9% (703M cached / 411K reads)
[OK] Query cache efficiency: 74.4% (6M cached / 8M selects)
[!!] Query cache prunes per day: 1993702
[OK] Sorts requiring temporary tables: 0% (260 temp sorts / 411K sorts)
[!!] Joins performed without indexes: 17564
[!!] Temporary tables created on disk: 27% (137K on disk / 497K total)
[OK] Thread cache hit rate: 99% (31 created / 81K connections)
[OK] Table cache hit rate: 32% (1K open / 4K opened)
[OK] Open file limit used: 24% (2K/8K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 32M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)

[chris]

It's suggested here, ​https://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

# Set buffer pool size to 50-80% of your computer's memory

But clearly we can't dedicate this much memory to !MySQL so, on the dev server these values have been changed, memory use increased for items but max_connections added (default is 151) to ensure it can't use too much:

 query_cache_size        = 64M

 tmp_table_size                        = 64M
 max_heap_table_size                   = 64M

 max_connections        = 10
 
 innodb_buffer_pool_size               = 128M

Then another run of the perl script resulted in:

Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    key_buffer_size (> 45.0M)
    table_cache (> 4096)
    innodb_buffer_pool_size (>= 823M)

So these were changed:

key_buffer              = 64M
table_cache            = 1028

And these were changed on the live server:

max_connections         = 20
table_cache             = 1028

query_cache_limit       = 64M

join_buffer_size        = 256K

tmp_table_size                        = 64M
max_heap_table_size                   = 64M

And the perl script was rerun:

Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)

And these things were changed:

join_buffer_size        = 512K
tmp_table_size                        = 128M
max_heap_table_size                   = 129M

And the script was run again:

Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with joins)

So this was changed:

join_buffer_size        = 1024K

The script was run again:

Variables to adjust:
    query_cache_limit (> 64M, or use smaller result sets)

So this was changed:

query_cache_limit       = 128M

The script was run again:

Variables to adjust:
    join_buffer_size (> 1.0M, or always use indexes with joins)
    tmp_table_size (> 128M)
    max_heap_table_size (> 129M)

And these were changed:

join_buffer_size        = 2056K
tmp_table_size                        = 256M
max_heap_table_size                   = 256M

The script was run again:

Variables to adjust:
    query_cache_limit (> 128M, or use smaller result sets)
    join_buffer_size (> 2.0M, or always use indexes with joins)

And these were changed:

query_cache_limit       = 256M
join_buffer_size        = 4096K

The script was run again:

Variables to adjust:
    query_cache_limit (> 256M, or use smaller result sets)
    join_buffer_size (> 4.0M, or always use indexes with joins)

And these were changed:

query_cache_limit       = 512M
join_buffer_size        = 8192K

Then the script was rerun:

Variables to adjust:
    key_buffer_size (> 293.6M)
    query_cache_limit (> 512M, or use smaller result sets)
    join_buffer_size (> 8.0M, or always use indexes with joins)

So these were changed:

key_buffer              = 512M
query_cache_limit       = 1024M
join_buffer_size        = 12288K

And the script was run again:

Variables to adjust:
    query_cache_limit (> 1G, or use smaller result sets)
    join_buffer_size (> 12.0M, or always use indexes with joins)

And at this point I think we should leave it some time to see what the results of changes are...

On the dev server, the conversion of the databases, hit a problem with piwik:

mysql -e "show tables in piwik;" | tail --lines=+2 | xargs -i echo "ALTEr.sqlLE {} ENGINE=INNODB;" > piwik_alter.sql 
kiwi:/tmp# cat piwik_alter.sql | mysql piwik
ERROR 1064 (42000) at line 39: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option ENGINE=INNODB' at line 1

This is the corresponding line:

ALTER TABLE option ENGINE=INNODB;

Option is a reserved word, ​http://www.mysqlfaqs.net/mysql-faqs/Errors/ERROR-1064-%2842000%29:-You-have-an-error-in-your-SQL-syntax so edit it to:

ALTER TABLE `option` ENGINE=INNODB;

And it runs OK. the same was done for session and user:

ALTER TABLE `session` ENGINE=INNODB;
ALTER TABLE `user` ENGINE=INNODB;

Now all the tables that can be converted on the dev server have been and the perl tuning script reports that more physical RAM should be added before changing any further settings:

General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 1.0M, or always use indexes with joins)
    table_cache (> 4096)
    innodb_buffer_pool_size (>= 2G)

Regarding switching off apache or putting the site into maintenance mode, my concern is that it won't be a 5 min job, it will probably take several hours to convert all the databases (based on how long it's taking on the dev server...) -- can we afford this much downtime? The process locks the tables as it goes so how much danger is there? I'd be tempted to run the conversion with apache and drupal still running in order to minimise downtime...

[chris]

This script will convert all the tables that can be converted on the live server, it's in /root:

mysql -e "show tables in live;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > live_alter.sql
cat live_alter.sql | mysql live

mysql -e "show tables in live_sharingengine;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > live_sharingengine_alter.sql
cat live_sharingengine_alter.sql | mysql live_sharingengine

mysql -e "show tables in live_workspaces;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > live_workspaces_alter.sql
cat live_workspaces_alter.sql | mysql live_workspaces

mysql -e "show tables in stats;" | tail --lines=+2 | xargs -i echo "ALTER TABLE \`{}\` ENGINE=INNODB;" > stats_alter.sql
cat stats_alter.sql | mysql stats

mysql -e "show tables in transwiki;" | grep -v searchindex | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > transwiki_alter.sql
cat transwiki_alter.sql | mysql transwiki

We just need to decide what services to turn off, if any, while it is running.

[chris]

A couple of additional tweaks on the live /etc/mysql/my.cnf:

query_cache_size        = 64M
join_buffer_size        = 24576K

The perl script now concludes:

Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 24.0M, or always use indexes with joins)

But the max threads, 20 is, probably not going to be reached, the max for the last month is 14, though in November / December it was going just above 20 and back in May, June, July it was up to 41, so this is something we need to keep an eye on, see ​https://kiwi.transitionnetwork.org/munin/webarch.net/quince.webarch.net/mysql_threads.html

The memory usage changes can be seen here:

​https://kiwi.transitionnetwork.org/munin/webarch.net/quince.webarch.net/multimemory.html

[jim]

Re Conversion to InnoDB:
My Linode chomped though a couple of mid-sized Drupal InnoDB conversions pretty quick. Given LIVE is much more powerful/bigger than DEV I reckon it won't be hours at all...

Perhaps I'm overstating the risks... Go for it and if we have any issues there's always REPAIR TABLE and restoring from a backup.

Re MySQL tuning:
As you see the tuner script suggests lots of things, I reckon now we've got here we should convert and wait a week to continue... Some settings may be able to be shrunk again.

And these scripts assume the server has nothing other than MySQL to do, which is false. I'd guess (but I have no basis other than a hunch) that the various buffers can be shrunk 20-50% from suggestions because we have Memcache doing the job of these buffers to a large extent... At least where the meaty data is stored in the cache_* tables.

So if the query I sent for the InnoDB buffer pool says '1080Mb' then we can safely knock 30% off that as we're doing other caching ourselves. And the settings you've upped based on the suggestions may be overzealous since we have finite memory.

It's definitely a journey rather than a destination! Basically, let's suck it an see and if we start to swap we can ease back.

[jim]

(writing again since it didn't save for some reason)

I reckon the safest/best approach is:

1. put www.tn.org in maintenance mode
2. clear caches (which truncates cache*)
3. truncate batch and sessions too
4. convert to innodb

this ensures no-one gets errors and no risks to indexes exists, and it makes the conversion faster by significantly reducing the size of some of the biggest tables.

I think maintenance mode is safer/better because: if DB is locking one table at a time we MAY get to a state where one is updated and other referring to an index in the first cannot be updated because it's locked, meaning the foreign key is broken.

[jim]

And putting the other sites in MMode can be avoided since they get so few hits.

[chris]

Jim, that all sounds good to me.

There will be about 3 hours downtime tomorrow while the host machine is upgraded to squeeze, for the duration I think I'll be switching the IP address to another server and redirecting all requests to a splash page saying "we will be back soon..." or something and I suggest I do the switch to InnoDB after the vserver is back up and before the IP is switched back, so an updated version of Jim's steps:

1. put www.tn.org in maintenance mode
2. clear caches (which truncates cache*)
3. truncate batch and sessions too
4. stop apache
5. convert to innodb

I'm not quite sure what you mean by "truncate batch and sessions", do you mean drop some tables?

[jim]

No I mean run TRUNCATE TABLE `batch`; and TRUNCATE TABLE `sessions`; in mysql command prompt to empty those tables... Dropping them would be VERY BAD!

[chris]

The tables are all now converted, apart from the mediawiki searchindex table which can't be converted, there is now a 6.5G /var/lib/mysql/ibdata/ibdata1 file with all InnoDB tables in it and this is on the new 15G SCSI disk.

The conversion would have been quicker the massive (over 4G) live_sharingengine.cache_views_data table had been cleared first. I don't seem to have an account on the ​https://news.transitionnetwork.org/ site -- Jim would it be worth adding one for me and or clearing the cache on this site?

mysqld is was using around 500M of RAM, ​https://kiwi.transitionnetwork.org/munin/webarch.net/quince.webarch.net/multimemory.html so I have lowered some settings as Jim suggested, also we should look at running OPTIMIZE TABLE ​https://dev.mysql.com/doc/refman/5.1/en/optimize-table.html -- this is one of the things reported by the tuning script.

To reduce mysql RAM usage these variables were changed:

key_buffer              = 256M
query_cache_limit       = 256M
tmp_table_size                        = 128M
max_heap_table_size                   = 128M

This is the output of the tuning script now:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-3
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 169K (Tables: 1)
[--] Data in InnoDB tables: 5G (Tables: 664)
[!!] Total fragmented tables: 664

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2m 47s (28K q [169.251 qps], 341 conn, TX: 228M, RX: 5M)
[--] Reads / Writes: 81% / 19%
[--] Total buffers: 732.0M global + 90.2M per thread (25 max threads)
[!!] Maximum possible memory usage: 2.9G (97% of installed RAM)
[OK] Slow queries: 0% (3/28K)
[OK] Highest usage of available connections: 48% (12/25)
[OK] Key buffer size / total MyISAM indexes: 256.0M/231.0K
[OK] Key buffer hit rate: 100.0% (1K cached / 0 reads)
[OK] Query cache efficiency: 70.5% (17K cached / 25K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Joins performed without indexes: 107
[!!] Temporary tables created on disk: 28% (487 on disk / 1K total)
[OK] Thread cache hit rate: 96% (12 created / 341 connections)
[OK] Table cache hit rate: 25% (704 open / 2K opened)
[OK] Open file limit used: 0% (48/8K)
[OK] Table locks acquired immediately: 100% (12K immediate / 12K locks)
[!!] InnoDB data size / buffer pool: 5.4G/256.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 24.0M, or always use indexes with joins)
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)
    innodb_buffer_pool_size (>= 5G)

[jim]

Well done Chris...

I'd have enabled 1 the innodb setting for 1 file per table to have a more managable filesystem, but that's a job for a rainy day.

Re ​https://news.transitionnetwork.org/ -- I've sent you login details via Skype. However it might be nice to be able to such tasks quickly quickly from the command line, so I have take the liberty of installing Drush (a safe and very very powerful Drupal management command line tool...):

jim@quince:/web/news.transitionnetwork.org/www$ sudo apt-get install drush
The following NEW packages will be installed:
  drush
... snip ...
Unpacking drush (from .../archives/drush_3.3-1_all.deb) ...
Processing triggers for man-db ...
Setting up drush (3.3-1) ..

It's only version 3.3.1 (5 is the latest) but it's still very handy... e.g.:

jim@quince:/web/news.transitionnetwork.org/www$ sudo -i
quince:~# cd /web/news.transitionnetwork.org/www
quince:/web/news.transitionnetwork.org/www# drush cc
Enter a number to choose which cache to clear.
  [0] : Cancel
  [1] : all
  [2] : theme
  [3] : menu
  [4] : css+js
1
WD php: Notice: Constant FEEDS_NODE_BATCH_SIZE already defined in require_once() (line 10 of                                                         [error]
/web/news.transitionnetwork.org/www/sites/all/modules/custom/transition_sharing_engine/FeedsMetaNodeProcessor.inc).
WD php: Notice: Constant FEEDS_NODE_SKIP_EXISTING already defined in require_once() (line 14 of                                                      [error]
/web/news.transitionnetwork.org/www/sites/all/modules/custom/transition_sharing_engine/FeedsMetaNodeProcessor.inc).
WD php: Notice: Constant FEEDS_NODE_REPLACE_EXISTING already defined in require_once() (line 15 of                                                   [error]
/web/news.transitionnetwork.org/www/sites/all/modules/custom/transition_sharing_engine/FeedsMetaNodeProcessor.inc).
WD php: Notice: Constant FEEDS_NODE_UPDATE_EXISTING already defined in require_once() (line 16 of                                                    [error]
/web/news.transitionnetwork.org/www/sites/all/modules/custom/transition_sharing_engine/FeedsMetaNodeProcessor.inc).
'all' cache was cleared

Now those errors are something I will fix when I updated the SE Drupal core files this weekend (the security update is largely irrelevant for our usage as we don't use the affected modules), but as you can see the command is drush cc which stands for 'Clear Cache'. More here: ​http://drush.ws/help/3

FYI Drush is fast becoming the backbone of good hosting/provisioning/development work as it can be used to script all manner of goodness on Drupal, and even for 'drush makefiles' which spit out a pre-built, pre-configured site from a make file... But I digress...

Anyway, I reckon the mysql config values you have for now are good, and can be made 'more safe' by dropping the mysql max threads down to 20 or 22 to guarantee <80% memory use.

We've now done all the most important speed enhancements on the site, apart from seriously track down the causes of any slow queries -- and out friend NginX of course.

[jim]

Meant to add: with Drush you must cd to the root of the site (or the site's directory in the sites/ folder if doing multi-site like Workspaces) and run commands... Drush will work out its context from there and perform tasks on the relevant site.

[jim]

One last thing, I've further lowered the caching on the SE -- it now caches only the query results, not the output, which is probably better since JSON generation from the underlying data is pretty swift, and such requests account for the majority.

Should keep that cache_views_data table's size right down...

I've also Skyped you the SE Drupal user 1 username/password for your records.

[chris]

I'd have enabled 1 the innodb setting for 1 file per table to have a more managable filesystem, but that's a job for a rainy day.

Ah, OK, I didn't know about that, none of the documents I was looking at mentioned this.

Cheers for installing Drush, I've added some documentation for it here wiki:NewLiveServer#drush, we could also use it with this munin module for drupal ​https://drupal.org/project/munin if we could think of a sensible use for it...

Anyway, I reckon the mysql config values you have for now are good, and can be made 'more safe' by dropping the mysql max threads down to 20 or 22 to guarantee <80% memory use.

I've dropped max_connections to 20, it's generally around 3, see: ​https://kiwi.transitionnetwork.org/munin/webarch.net/quince.webarch.net/mysql_threads.html

I've further lowered the caching on the SE

Thanks.

Last night I added a few more munin graphs:

​https://kiwi.transitionnetwork.org/munin/webarch.net/quince.webarch.net/mysql_innodb.html
​https://kiwi.transitionnetwork.org/munin/webarch.net/quince.webarch.net/mysql_querycache.html
​https://kiwi.transitionnetwork.org/munin/webarch.net/quince.webarch.net/mysql_cachememory.html