0

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
  • 1
    Did you have problems like this during testing or it it just manifesting in prod? – Vérace Apr 17 at 10:17
  • Its all in production -- we had to upgrade the systems to Jessie because Wheezy has gone obsolete. many things have broken in wheezy – Nasir Mahmood Apr 17 at 10:30
0

Can I run the mysql_upgrade now, when the system upgrade is complete and things are back in production -- please suggest.

You can and you should, before going into any other debugging. However, note that mysql_upgrade does CHECK TABLES, which are considered DDLs, so if you have long running queries, they may block them (due to metadata locking) and cause contention- so make sure your monitor issues where it could get stuck.

I say that is the things you should do first, and then possibly restart - so I would suggest to do it during a maintenance window.

The slowdown, however, maybe unrelated- if you are using official Debian packages, you may have unknowingly migrated to MariaDB, and that is a huge change that requires a lot of preparation (not only migrating major version, but changing vendor, which may change a lot of defaults, configuration and features).

  • all systems are showing MySQL from Oracle -- rather than MariaDB -- the reversion is out of question here. – Nasir Mahmood Apr 17 at 10:47
  • reversion? Who talked about reversion? Indeed jessie still shipped mysql 5.5, which is unsupported by MySQL/Oracle, and will only have 1 year of "support" left by Debian LTS. – jynus Apr 17 at 10:57
  • by reversion I meant there are no chances of MariaDB at all. its all MySQL from Oracle. – Nasir Mahmood Apr 17 at 11:24
0

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.

Regards

  • 1
    set global innodb_stats_on_metadata=0; gives a slight improvement on information_Schema results – Nasir Mahmood Apr 17 at 11:30
  • SHOW VARIABLES LIKE '%open%'; SHOW GLOBAL STATUS LIKE '%open%'; How much RAM do you have? How many tables in the system? How many tables in DB_NAME? – Rick James Apr 26 at 4:39
0

Suggestion for some immediate relief using root login,

SET GLOBAL read_buffer_size=256K;

from 16M per conf info provided.

Remember to change in my.cnf for next stop/start or restart.

Disclaimer: I am the author of web content mentioned in my profile, Network profile and can provide additional suggestions / Utility scripts to improve your instance performance.

  • @Nasir Mahmood Have you had an opportunity to try this Suggestion? – Wilson Hauck May 4 at 16:51
  • @Nasir Mahmood Please view profile, Network profile for contact info. – Wilson Hauck May 6 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.