Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

MySQL Slow Query Issue

Discussion in 'Database Discussion' started by Bashed, Jul 31, 2018.

  1. Bashed

    Bashed Well-Known Member

    Joined:
    Dec 18, 2013
    Messages:
    112
    Likes Received:
    3
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Upgraded to WHMCS 7.6 and now it's slow loading pages. Their support suggested enabling slow query log for MySQL which I tried, but it failed.

    Tried these steps:

    Code:
    Add following to following to /etc/my.cnf file on server.
    
    # vi /etc/my.cnf
    
    log-slow-queries=/var/lib/mysql/slow.log
    
    After that, do the following commands to create the file with the right ownership and file permissions
    
    # touch /var/lib/mysql/slow.log
    
    # chmod 660 /var/lib/mysql/slow.log
    
    # chown mysql:mysql /var/lib/mysql/slow.log
    
    Restart the mysql server and check the logs in /var/lib/mysql/slow.log.
    Looked like this first few lines...

    Code:
    [mysqld]
    performance-schema=0
    log-slow-queries=/var/log/mysql/log-slow-queries.log
    Code:
    [root@server log]# service mysql restart
    Restarting mysql (via systemctl):  Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.
                                                               [FAILED]
    Code:
    [root@server log]# systemctl status mariadb.service
    ● mariadb.service - MariaDB 10.2.16 database server
       Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
      Drop-In: /etc/systemd/system/mariadb.service.d
               └─migrated-from-my.cnf-settings.conf, protecthome.conf
       Active: active (running) since Tue 2018-07-31 17:17:43 EDT; 12s ago
         Docs: man:mysqld(8)
               https://mariadb.com/kb/en/library/systemd/
      Process: 26310 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
      Process: 26144 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
      Process: 26141 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
     Main PID: 26277 (mysqld)
       Status: "Taking your SQL requests now..."
       CGroup: /system.slice/mariadb.service
               └─26277 /usr/sbin/mysqld
    
    Jul 31 17:17:40 server.domain.com systemd[1]: Starting MariaDB 10.2.16 database server...
    Jul 31 17:17:41 server.domain.com mysqld[26277]: 2018-07-31 17:17:41 140651937720512 [Note] Using unique option prefix 'sort_buffer' is error-prone and ... instead.
    Jul 31 17:17:41 server.domain.com mysqld[26277]: 2018-07-31 17:17:41 140651937720512 [Note] Using unique option prefix 'read_buffer' is error-prone and ... instead.
    Jul 31 17:17:41 server.domain.com mysqld[26277]: 2018-07-31 17:17:41 140651937720512 [Note] /usr/sbin/mysqld (mysqld 10.2.16-MariaDB) starting as process 26277 ...
    Jul 31 17:17:43 server.domain.com systemd[1]: Started MariaDB 10.2.16 database server.
    Hint: Some lines were ellipsized, use -l to show in full.
    No one?
     
    #1 Bashed, Jul 31, 2018
    Last edited by a moderator: Aug 1, 2018
  2. cPanelLauren

    cPanelLauren Forums Analyst
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    2,194
    Likes Received:
    156
    Trophy Points:
    143
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hello @Bashed

    It appears that MariaDB is started based on the output you're showing. I should also point out that it is MariaDB you're running not MySQL so the proper command to run to restart it will be:

    Code:
    /scripts/restartsrv_mariadb
    Though it does still use /etc/my.cnf and MariaDB gives instructions on how to enable this: Slow Query Log Overview
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice