MySQL Slow Query Issue

[Bashed]

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:

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...

[mysqld]
performance-schema=0
log-slow-queries=/var/log/mysql/log-slow-queries.log

[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]

[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?

 

[cPanelLauren]

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:

/scripts/restartsrv_mariadb

Though it does still use /etc/my.cnf and MariaDB gives instructions on how to enable this: Slow Query Log Overview