How to do MySQL Troubleshooting?

Having some trouble with your SQL Server? Here you can discuss anything related to SQL Server.
Posts: 48
Joined: Tue Aug 11, 2015 11:51 am

How to do MySQL Troubleshooting?

Postby MikeJames » Tue Aug 11, 2015 11:52 am

How to fix this error

Posts: 21
Joined: Mon Sep 07, 2015 12:04 pm

Re: How to do MySQL Troubleshooting?

Postby JonathanDelitu » Mon Sep 07, 2015 12:11 pm

Below is the MySQL troubleshooting process:

To find out if CPU or I/O is reason for most CPU usage, use top command via SSH to verify.
If mysqld process is near the top of the list, then you can assume that MySQL has an issue and needs help.
Check currently running queries. If you’re using Plesk, use command : [root@localhost]# mysql –uadmin –p`cat /etc/psa/.psa.shadow` –e “show full processlist”

and if you’re using cPanel or if MySQL doesn’t requires password, you can this command via SSH:

[root@localhost]# mysql –e “show full processlist”

MySQL should have 10s or 100s listed queries, and if it has more, they should be diagnosed further. Plus, “show full processlist” displays longest-running queries indicating queries cause overall slowness.

Activate slow query log. MySQL’s slow query log keeps a record of execution time for every query, logging any SQL query which executes longer than long_query_time command which controls logged queries, can be set using command:

[root@localhost]# mysql –e “set global long_query_time = 1.00”;
[root@localhost]# mysql –e “show variables like ‘slow_query_log*’”;

To read slow_query_log_file, you may use programs like vi, nano or less.

You can use programs like less, vi, or nano to read the slow_query_log_file. It helps you know the information about slowest queries, if any specific SQL query can be enhanced, or if any of the configuration values in /etc/my.cnf are optimizable.

To edit ‘my.cnf’ file:

When MySQL starts, my.cnf file reads loading specified configuration values. The my.cnf file can be relatively scarce depending on the way MySQL is installed.

It’s always advised to backup a configuration file before any major edit- [root@localhost]# cp /etc/my.cnf{,.bak}

Plus, all values shouldn’t be adjusted and a default value is used by MySQL if a variable isn’t listed in my.cnf- these are often accurate values. Workload analysis of executed queries is essential to make a fair decision but by default, few variables in MySQL are relatively low and they are as below:

key_buffer_size, or key_buffer:

Databases that use MyISAM storage engine store, indexes in .MYI files that can be cached by server for faster retrieval. To find out if you have large filesize or many indexes, you may run:

[root@localhost]# find /var/lib/mysql -name "*.MYI" -exec ls -lAsh {} \;

It’s secure to set the key_buffer_size to a larger value than all indexes’ size.


MyISAM tables get stored in individual files and but InnoDB tables get stored in single file ibdata1 by default. InnoDB storage engine runs faster when at least ibdata1 can be stored in memory; setting innodb_buffer_pool_size to a larger size is recommended. e.g. ls –lAsh /var/lib/mysql/ibdata1. Plus, the InnoDB buffer pool only allocates the memory it needs, i.e. you can set innodb_buffer_pool_size higher than necessary.

tmp_table_size, max_heap_table_size:

These parameters must be set to ‘16M’. Temporary tables which MySQL generates to complete a query will be larger than 16M unless app using MySQL has optimized indexing and query design. By default, 128M or 256M memory meets multiple server workloads; and if a query causing temporary tables needs multiple GBs of memory to complete quickly, it may need configuration itself.

query cache, query_cache_size:

When query cache is enabled, a query with its response are cached so that if exactly same query gets fetched, MySQL processes it much faster. But on INSERT or UPDATE statement, in case a cached query references an updated table, it’s nullified. So, a MySQL server performs better with more updates than retrievals w/o the query cache.

It is preferred to keep the query cache disabled if tables are often updated. Not using query cache and having frequently referenced tables can boost your performance. As data is and can be nullified often, the query_cache_size operates much faster at values smaller than expected, less than “20M-400M”.

And, for resolving such issue, you need to have a true Pentium greater 2.4 GHz as Celeron processors find it hard to process MySQL. In case you have problem with MySQL, you should go for server with better processor.

If you already have a server with Pentium processor or higher, to accelerate speed of your slow-running server, you need to change my.cnf file on your machine. You can do it as follows:

Backup the original ‘my.cnf’ file to the /root directory using command:
[root@localhost]# mv /etc/my.cnf /root
To copy a pre-optimized ‘my.cnf’ file from where it is read, use command:
[root@localhost]# cp /usr/share/doc/mysql-server-5.0.27/my-huge.cnf /etc/my.cnf
At last, you just have to restart the mySQL server using command:
[root@localhost]# service mysqld restart

Return to “SQL Server”

Who is online

Users browsing this forum: No registered users and 1 guest