Changes between Initial Version and Version 8 of Ticket #396

Timestamp:

02/29/12 13:18:41 (5 years ago)

Author:

chris

Comment:

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...