Articles in this section

How to troubleshoot slow performance of MySQL/MariaDB on a Plesk server?

Vincent Lauton
Updated
Plesk ABT: Group B kb: auxiliary FR:PPM-1963

Applicable to:

  • Plesk

Question

How to troubleshoot slow performance of MySQL/MariaDB on a Plesk server?

Answer

Note: Database performance tuning is out of the scope of Plesk Free Technical Support. This serves as a general-purpose guide.

Plesk for Linux
  1. Log into Plesk
  2. Go to Tools & Settings > Performance Booster > Serverwide
  3. Once MariaDB appears (this may take around a minute), press show values to be optimized

  4. Press Apply

    Applying the changes will restart the database server. Make sure no long queries are ongoing first as MariaDB will need to finish all queries before it can restart.

  5. Monitor CPU usage.

    If CPU consumption increases again, consider reviewing the SQL code of a database that has slow queries.

  6. During a high level of CPU usage, find queries that are currently running and taking a lot of time:

    # plesk db "SHOW FULL PROCESSLIST"
    +-----+-------+-----------+------+---------+------+-------+-----------------------+ 
    | Id | User | Host | db | Command | Time | State | Info | 
    +-----+-------+-----------+------+---------+------+-------+-----------------------+ 
    | 12 | admin | localhost | psa | Sleep | 6763 | | NULL | 
    | 100 | admin | localhost | psa | Query | 0 | NULL | SHOW FULL PROCESSLIST | 
    +-----+-------+-----------+------+---------+------+-------+-----------------------+ 
    2 rows in set (0.00 sec)

  7. Check the MySQL/MariaDB error logfile /var/log/mysqld.log for errors.

  8. Check RAM and free disk space:

    # free -h
    # df -h

  9. Find databases that cause slow MySQL/MariaDB performance by enabling the MySQL slow query log.

    Note: It is possible to see the list of processes currently being handled by the local MySQL/MariaDB server. To do so, log into Plesk and go to Tools & Settings > MySQL Process List (Beta) (under Assistance and Troubleshooting).
    Click a MySQL/MariaDB process to see its details: what queries are being processed and how much resources they consume.

  10. Analyze and optimize all tables in all databases:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqlcheck -uadmin --optimize --all-databases

  11. Install and run the mysqltuner utility. This utility will analyze the current MySQL/MariaDB server instance and provide recommendations for adjusting appropriate parameters.
Plesk for Windows
  1. Connect to the Plesk server via RDP

  2. Open the MySQL/MariaDB configuration file my.cnf or my.ini in any text editor. Locations of the file are:

    %plesk\_dir%Databases\\MySQL\\my.ini

  3. Add the following directives under the [mysqld] section or increase the values if these directives are already defined:

    innodb_buffer_pool_size=1024M
    query_cache_size=64M

  4. Save the changes and close the file.
  5. Open Task Manager > Services and restart MySQL56 (or MySQL51) service:
    2017-12-26_22_06_31-mRemoteNG_-_confCons.xml.png

  6. Analyze and optimize all tables in all databases:

    %plesk_dir%\MySQL\bin\mysqlcheck.exe -uadmin -P3306 --optimize --all-databases

    Note: For accessing MySQL/MariaDB in Windows, it is required to add the line skip-grant-tables right under [mysqld] in %plesk_dir%Databases\MySQL\my.ini and restart MySQL service, as it is shown above. Do not forget to remove it and restart again after all is done.

  7. Find databases that cause slow MySQL/MariaDB performance by enabling the MySQL/MariaDB slow query log.

    With Repair Kit, it is possible to see the list of processes currently being handled by the local MySQL/MariaDB server. To do so, log into Plesk and go to Tools & Settings > Database Process List (under Assistance and Troubleshooting).
    Click a MySQL/MariaDB process to see its details: what queries are being processed and how much resources they consume.

  8. Install and run the mysqltuner utility. This utility will analyze the current MySQL/MariaDB server instance and provide recommendations for adjusting appropriate parameters.

    Note: This is a third party utility and should be used at one's own risk.

Was this article helpful?

Comments

3 comments
Date Votes
  • Avatar
    Pete Batin

    The link to mysqltuner no longer works

     

    1
  • Avatar
    Eamon DrTutor

    No link : Assistance and Troubleshooting

    1
  • Avatar
    Eamon DrTutor

    no link Assistance and Troubleshooting

    0

Please sign in to leave a comment.