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)
