We just upgraded the Debian Wheezy(7) to Jessie(8).
All upgrades went smooth except MySQL which I think is causing some or exhaustive slowness specially in the MySQL's information Schema.
the more lively example that is showing this is from PHPMyAdmin --- when we try to update or change a column type, the query takes more than 100 seconds to return back. something similar is happening with other changes on MyISM database tables.
Probable suspects: During Upgrade, we rebooted the server immediately after the upgrade procedure was complete and mysql_upgrade could not complete. This is my own perception as the mysql DB size is more than 400 GB and it might have not completed at all.
Now, the MySQL is behaving insanely on most parts and slowness is observed generally.
Can someone please recommend what would be best option to make things more reliable in terms of the MySQL
Please understand that I have eliminated the risks of Apache2/PHP by completely re-installing them, only left over is the MySQL which is not returning properly.
Can I run the mysql_upgrade now, when the system upgrade is complete and things are back in production -- please suggest.
here is an abstract of my.cnf -- this is a 130 Gigs to RAM Server, with 1000 connections at a given time.
[client] port = 3333 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3333 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking old_passwords=1 ft_min_word_len = 2 skip-name-resolve bind-address = 0.0.0.0 key_buffer = 512M key-buffer-size = 20G sort_buffer_size = 32M read_buffer_size = 16M max_allowed_packet = 1500M thread_stack = 256K thread_cache_size = 300 myisam-recover = BACKUP max_connections = 1000 table_cache = 4096 tmp_table_size = 512M query_cache_limit = 16M query_cache_size = 128M join_buffer_size = 2M innodb_buffer_pool_size = 1000M server-id = 1 log_bin = mysql-bin expire_logs_days = 10 max_binlog_size = 100M [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M [mysqld_safe] syslog
Here is a slow query log for one of the Information_Schema queries.
# Query_time: 101.716579 Lock_time: 0.000101 Rows_sent: 0 Rows_examined: 101049 SET timestamp=1555429414; SELECT `column_name`, `table_name`,`table_schema` FROM `information_schema`.`key_column_usage` WHERE `referenced_column_name` = 'tnc_name' AND `referenced_table_name` = 'tnc' AND `referenced_table_schema` = 'DB_NAME';
See the Query took more than 100 seconds.. which was not taking more than 5 seconds before upgrade.