> | innodb_buffer_pool_size | 8388608 |
Ouch! See
http://mysql.rjweb.org/doc.php/memory
This seems like the cuprit:
> | tab8 | InnoDB | 821 | 516947968 | 101220352 | mediumtext ( 2 columns having mediumtext datatype )
SELECT MAX(length(col1)), MAX(length(col2)) FROM tab8;
(where col1 and col2 are the names of the MEDIUMTEXT columns)
It does not hurt to make this larger; suggest 100M:
> | innodb_log_file_size | 15728640 |
However, it is non-trivial. See
http://forums.mysql.com/read.php?24,395365 (Correctly setting mysql options for a huge DB with intense trafic)
http://forums.mysql.com/read.php?20,385005 (innodb_log_file_size)
Baron's advice was fine in 2008. Newer versions of MySQL have STATUS variables that make it easier to calculate:
Uptime * innodb_log_file_size / Innodb_os_log_written
Should, according to Baron, be under 3600.
(It is better to wait until the server has been running awhile before calculating that.)
http://forums.mysql.com/read.php?24,363408 (Unexplained drop in performance?)
http://forums.mysql.com/read.php?22,362644 (values in my.ini for 2GB RAM)
http://forums.mysql.com/read.php?22,302513 (Reset Root Password causing ib_logfile issues)
http://forums.mysql.com/read.php?10,267434 (Switch off InnoDB logging - Possible?)