Very slow queries on Cloud VPS

k2tec · Jan 10, 2014

One of my customers is using a Cloud VPS server from OVH with Cpanel ( WHM), but it's very slow when asking the products in the admin page. I tried to change the my.cnf, but when I restart mysql it won't.
This is the my.cnf on teh server

Code:

[mysqld]
innodb_file_per_table=1
bind-address=127.0.0.1
local-infile=0

Processes on server status:

Code:

/bin/rpm -vV MySQL51-client MySQL51-devel MySQL51-server MySQL51-shared MySQL51-test bandmin cpanel-ImageMagick cpanel-analog cpanel-angularjs cpanel-awstats cpanel-cairo cpanel-cgiemail cpanel-ckeditor cpanel-clamav cpanel-clamav-virusdefs cpanel-common-licenses cpanel-elfinder cpanel-fontconfig cpanel-freetype cpanel-git cpanel-glib cpanel-jquery cpanel-jquery-1.7.2 cpanel-jquery-ui-1.8.18 cpanel-jquery-ui-themes-1.8.18 cpanel-libmcrypt cpanel-libspf2 cpanel-libsrs_alt cpanel-libxml2 cpanel-mysql cpanel-mysql-libs cpanel-pango cpanel-pcre cpanel-perl-514 cpanel-perl-514-Acme-Bleach cpanel-perl-514-Acme-Damn cpanel-perl-514-Acme-Spork cpanel-perl-514-Algorithm-C3 cpanel-perl-514-Algorithm-Diff cpanel-perl-514-Any-Moose cpanel-perl-514-App-Cmd cpanel-perl-514-App-CmdDispatch cpanel-perl-514-App-Nopaste cpanel-perl-514-AppConfig cpanel-perl-514-Archive-Any cpanel-perl-514-Archive-Extract cpanel-perl-514-Archive-Tar cpanel-perl-514-Archive-Tar-Builder cpanel-perl-514-Archive-Tar-Streamed cpanel-perl-514-Archive-Zip cpanel-perl-514-Attribute-Handlers cpanel-perl-514-Authen-Libwrap cpanel-perl-514-Authen-SASL cpanel-perl-514-AutoLoader cpanel-perl-514-B-C cpanel-perl-514-B-Debug cpanel-perl-514-B-Flags cpanel-perl-514-B-Hooks-EndOfScope cpanel-perl-514-B-Keywords cpanel-perl-514-B-Lint cpanel-perl-514-B-Utils cpanel-perl-514-BSD-Resource cpanel-perl-514-Browser-Open cpanel-perl-514-Business-ISBN cpanel-perl-514-Business-ISBN-Data cpanel-perl-514-Business-OnlinePayment cpanel-perl-514-Business-OnlinePayment-AuthorizeNet cpanel-perl-514-Business-UPS cpanel-perl-514-CDB_File cpanel-perl-514-CGI-Session cpanel-perl-514-CGI-Simple cpanel-perl-514-CGI.pm cpanel-perl-514-CPAN cpanel-perl-514-CPAN-DistnameInfo cpanel-perl-514-CPAN-Meta cpanel-perl-514-CPAN-Meta-Requirements cpanel-perl-514-CPAN-Meta-YAML cpanel-perl-514-CPAN-SQLite cpanel-perl-514-CPAN-Uploader cpanel-perl-514-CPANPLUS cpanel-perl-514-CPANPLUS-Dist-Build cpanel-perl-514-Cache-FastMmap cpanel-perl-514-Capture-Tiny cpanel-perl-514-Carp-Clan cpanel-perl-514-Catalyst-Action-REST cpanel-perl-514-Catalyst-Action-RenderView cpanel-perl-514-Catalyst-ActionRole-ACL cpanel-perl-514-Catalyst-Authentication-Store-DBIx-Class cpanel-perl-514-Catalyst-Component-InstancePerContext cpanel-perl-514-Catalyst-Controller-ActionRole cpanel-perl-514-Catalyst-Devel cpanel-perl-514-Catalyst-Log-Log4perl cpanel-perl-514-Catalyst-Model-DBIC-Schema cpanel-perl-514-Catalyst-Plugin-Authentication cpanel-perl-514-Catalyst-Plugin-Authorization-Roles cpanel-perl-514-Catalyst-Plugin-AutoCRUD cpanel-perl-514-Catalyst-Plugin-Browser cpanel-perl-514-Catalyst-Plugin-Cache cpanel-perl-514-Catalyst-Plugin-Cache-FastMmap cpanel-perl-514-Catalyst-Plugin-ConfigLoader cpanel-perl-514-Catalyst-Plugin-HashedCookies cpanel-perl-514-Catalyst-Plugin-Redirect cpanel-perl-514-Catalyst-Plugin-Session cpanel-perl-514-Catalyst-Plugin-Session-State-Cookie cpanel-perl-514-Catalyst-Plugin-Session-Store-FastMmap cpanel-perl-514-Catalyst-Plugin-StackTrace cpanel-perl-514-Catalyst-Plugin-Static-Simple cpanel-perl-514-Catalyst-Plugin-Unicode-Encoding cpanel-perl-514-Catalyst-Plugin-UploadProgress cpanel-perl-514-Catalyst-Runtime cpanel-perl-514-Catalyst-TraitFor-Request-BrowserDetect cpanel-perl-514-Catalyst-View-JSON cpanel-perl-514-Catalyst-View-TT cpanel-perl-514-CatalystX-Component-Traits cpanel-perl-514-CatalystX-RoleApplicator cpanel-perl-514-Class-Accessor cpanel-perl-514-Class-Accessor-Chained cpanel-perl-514-Class-Accessor-Grouped cpanel-perl-514-Class-Base cpanel-perl-514-Class-C3 cpanel-perl-514-Class-C3-Adopt-NEXT cpanel-perl-514-Class-C3-Componentised cpanel-perl-514-Class-Data-Inheritable cpanel-perl-514-Class-ErrorHandler cpanel-perl-514-Class-Factory-Util cpanel-perl-514-Class-Inner cpanel-perl-514-Class-Inspector cpanel-perl-514-Class-Load cpanel-perl-514-Class-Load-XS cpanel-perl-514-Class-Loader cpanel-perl-514-Class-Method-Modifiers cpanel-perl-514-Class-Singleton cpanel-perl-514-Class-Std cpanel-perl-514-Class-Std-Utils cpanel-perl-514-Class-Unload cpanel-perl-514-Class-XSAccessor cpanel-perl-514-Clipboar

cPanelMichael · Jan 10, 2014

Hello

Do you notice any specific error messages in the MySQL error log when attempting to restart MySQL (/var/lib/mysql/$hostname.err)? Note that the following thread is useful if you want to optimize your MySQL configuration:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

k2tec · Jan 10, 2014

Hello,
Thanks fot the reply, but it is not my server. I have tried his site on my one shared cpanel server and it is running normale.
And because it is not my server I don't want to install any other software.

But this is a part of the error log:

Code:

Version: '5.1.70-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
131219  4:01:03 [Note] /usr/sbin/mysqld: Normal shutdown

131219  4:01:03 [Note] Event Scheduler: Purging the queue. 0 events
131219  4:01:04  InnoDB: Starting shutdown...
131219  4:01:07  InnoDB: Shutdown completed; log sequence number 0 86318954
131219  4:01:07 [Note] /usr/sbin/mysqld: Shutdown complete

131219 04:01:07 mysqld_safe mysqld from pid file /var/lib/mysql/vps34007.ovh.net.pid ended
131219 04:01:39 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
131219  4:01:39 [Note] Plugin 'FEDERATED' is disabled.
131219  4:01:40  InnoDB: Initializing buffer pool, size = 8.0M
131219  4:01:41  InnoDB: Completed initialization of buffer pool
131219  4:01:41  InnoDB: Started; log sequence number 0 86318954
131219  4:01:41 [Note] Event Scheduler: Loaded 0 events
131219  4:01:41 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.72-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPLv2)
131219  4:01:50 [Note] /usr/sbin/mysqld: Normal shutdown

131219  4:01:50 [Note] Event Scheduler: Purging the queue. 0 events
131219  4:01:50  InnoDB: Starting shutdown...
131219  4:01:52  InnoDB: Shutdown completed; log sequence number 0 86318990
131219  4:01:52 [Note] /usr/sbin/mysqld: Shutdown complete

131219 04:01:52 mysqld_safe mysqld from pid file /var/lib/mysql/vps34007.ovh.net.pid ended
131219 04:01:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
131219  4:01:53 [Note] Plugin 'FEDERATED' is disabled.
131219  4:01:53  InnoDB: Initializing buffer pool, size = 8.0M
131219  4:01:53  InnoDB: Completed initialization of buffer pool
131219  4:01:53  InnoDB: Started; log sequence number 0 86318990
131219  4:01:53 [Note] Event Scheduler: Loaded 0 events
131219  4:01:53 [Note] /usr/sbin/mysqld: ready for connections.

And this when I tried to change the my.cnf:

Code:

Version: '5.1.72-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPLv2)
140103 19:53:13 [Note] /usr/sbin/mysqld: Normal shutdown

140103 19:53:13 [Note] Event Scheduler: Purging the queue. 0 events
140103 19:53:13  InnoDB: Starting shutdown...
140103 19:53:16  InnoDB: Shutdown completed; log sequence number 0 117883472
140103 19:53:16 [Note] /usr/sbin/mysqld: Shutdown complete

140103 19:53:16 mysqld_safe mysqld from pid file /var/lib/mysql/vps34007.ovh.net.pid ended
140103 19:53:17 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140103 19:53:17 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
140103 19:53:17 [Note] Plugin 'FEDERATED' is disabled.
140103 19:53:17  InnoDB: Initializing buffer pool, size = 8.0M
140103 19:53:17  InnoDB: Completed initialization of buffer pool
140103 19:53:17  InnoDB: Started; log sequence number 0 117883472
140103 19:53:17 [ERROR] /usr/sbin/mysqld: unknown variable 'record_buffer_size=8M'
140103 19:53:17 [ERROR] Aborting

140103 19:53:17  InnoDB: Starting shutdown...
140103 19:53:23  InnoDB: Shutdown completed; log sequence number 0 117883472
140103 19:53:23 [Note] /usr/sbin/mysqld: Shutdown complete

140103 19:53:23 mysqld_safe mysqld from pid file /var/lib/mysql/vps34007.ovh.net.pid ended
140103 19:57:20 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140103 19:57:20 [Note] Plugin 'FEDERATED' is disabled.
140103 19:57:20  InnoDB: Initializing buffer pool, size = 8.0M
140103 19:57:20  InnoDB: Completed initialization of buffer pool
140103 19:57:20  InnoDB: Started; log sequence number 0 117883472
140103 19:57:20 [Note] Event Scheduler: Loaded 0 events
140103 19:57:20 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.72-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPLv2)
140103 20:51:14 [Note] /usr/sbin/mysqld: Normal shutdown

140103 20:51:14 [Note] Event Scheduler: Purging the queue. 0 events
140103 20:51:14  InnoDB: Starting shutdown...
140103 20:51:18  InnoDB: Shutdown completed; log sequence number 0 117945111
140103 20:51:18 [Note] /usr/sbin/mysqld: Shutdown complete

140103 20:51:18 mysqld_safe mysqld from pid file /var/lib/mysql/vps34007.ovh.net.pid ended
140103 20:51:20 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140103 20:51:20 [Warning] /usr/sbin/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.
140103 20:51:20 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
140103 20:51:20 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.
Unknown suffix '[' used for variable 'open_files_limit' (value '9994[mysqld]')
140103 20:51:20 [ERROR] /usr/sbin/mysqld: Error while setting value '9994[mysqld]' to 'open_files_limit'
140103 20:51:20 [ERROR] Aborting

140103 20:51:20 mysqld_safe mysqld from pid file /var/lib/mysql/vps34007.ovh.net.pid ended
140103 20:54:45 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140103 20:54:45 [Note] Plugin 'FEDERATED' is disabled.
140103 20:54:45  InnoDB: Initializing buffer pool, size = 8.0M
140103 20:54:45  InnoDB: Completed initialization of buffer pool
140103 20:54:45  InnoDB: Started; log sequence number 0 117945111
140103 20:54:45 [Note] Event Scheduler: Loaded 0 events
140103 20:54:45 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.72-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPLv2)
140103 21:01:26 [Note] /usr/sbin/mysqld: Normal shutdown

140103 21:01:26 [Note] Event Scheduler: Purging the queue. 0 events
140103 21:01:26  InnoDB: Starting shutdown...
140103 21:01:31  InnoDB: Shutdown completed; log sequence number 0 117954377
140103 21:01:31 [Note] /usr/sbin/mysqld: Shutdown complete

140103 21:01:31 mysqld_safe mysqld from pid file /var/lib/mysql/vps34007.ovh.net.pid ended
140103 21:01:32 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140103 21:01:32 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
140103 21:01:32 [Note] Plugin 'FEDERATED' is disabled.
140103 21:01:32  InnoDB: Initializing buffer pool, size = 8.0M
140103 21:01:32  InnoDB: Completed initialization of buffer pool
140103 21:01:32  InnoDB: Started; log sequence number 0 117954377
140103 21:01:32 [ERROR] /usr/sbin/mysqld: unknown variable 'record_buffer_size=8M'
140103 21:01:32 [ERROR] Aborting

140103 21:01:32  InnoDB: Starting shutdown...
140103 21:01:37  InnoDB: Shutdown completed; log sequence number 0 117954377
140103 21:01:37 [Note] /usr/sbin/mysqld: Shutdown complete

140103 21:01:37 mysqld_safe mysqld from pid file /var/lib/mysql/vps34007.ovh.net.pid ended
140103 21:04:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140103 21:04:08 [Note] Plugin 'FEDERATED' is disabled.
140103 21:04:08  InnoDB: Initializing buffer pool, size = 8.0M
140103 21:04:08  InnoDB: Completed initialization of buffer pool
140103 21:04:08  InnoDB: Started; log sequence number 0 117954377
140103 21:04:08 [Note] Event Scheduler: Loaded 0 events
140103 21:04:08 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.72-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPLv2)

cPanelPeter · Jan 10, 2014

Hello,

There are several errors in your /etc/my.cnf file. Try commenting them out or editting them accordingly.
The message says:

Code:

140103 20:51:20 [Warning] /usr/sbin/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.
140103 20:51:20 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
140103 20:51:20 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.
Unknown suffix '[' used for variable 'open_files_limit' (value '9994[mysqld]')
140103 20:51:20 [ERROR] /usr/sbin/mysqld: Error while setting value '9140103 21:01:32 [ERROR] /usr/sbin/mysqld: unknown variable 'record_buffer_size=8M'
140103 21:01:32 [ERROR] Aborting 994[mysqld]' to 'open_files_limit'

So change the log_slow_queries line to: slow_query_log
set-variable-name should be changed to the actual variable name
safe-show-database should be changed to grant show databases,
Also looks like you need a carriage return behind the 9994 so that the [mysqld] is on it's own line
and last but not least, the variable "record_buffer_size" seems to be invalid.

k2tec · Jan 10, 2014

Hello Peter,

Thanks for your reply, I willtry it after weekend. Because the site is very busy in the weekend.

thinkbot · Jan 10, 2014

run mysqltuner and post results

k2tec · Jan 11, 2014

On my dedicated server it was no problem to install Mysqlmymonlite.

But this VPS gives me this error when I run ./mysqlmymonlite.sh

Code:

PLEASE NOTE:

For 'make test' to run properly, you must ensure that the
database user 'root' can connect to your MySQL server
and has the proper privileges that these tests require such
as 'drop table', 'create table', 'drop procedure', 'create procedure'
as well as others.

mysql> grant all privileges on test.* to 'root'@'localhost' identified by 's3kr1                                                                            t';

You can also optionally set the user to run 'make test' with:

perl Makefile.PL --testuser=username

I will use the following settings for compiling and testing:

  cflags        (mysql_config) = -I/usr/include/mysql  -g   -DUNIV_LINUX -DUNIV_                                                                            LINUX
  embedded      (mysql_config) =
  ldflags       (mysql_config) = -rdynamic
  libs          (mysql_config) = -L/usr/lib64/mysql -lmysqlclient -lz -lcrypt -l                                                                            nsl -lm -lmygcc
  mysql_config  (guessed     ) = mysql_config
  nocatchstderr (default     ) = 0
  nofoundrows   (default     ) = 0
  ssl           (guessed     ) = 0
  testdb        (default     ) = test
  testhost      (default     ) =
  testpassword  (default     ) =
  testport      (default     ) =
  testsocket    (default     ) =
  testuser      (guessed     ) = root

To change these settings, see 'perl Makefile.PL --help' and
'perldoc DBD::mysql::INSTALL'.

Checking if your kit is complete...
Warning: the following files are missing in your kit:
        META.json
        t/mem_leak.pl
Please inform the author.
Using DBI 1.63 (for perl 5.010001 on x86_64-linux-thread-multi) installed in /us                                                                            r/local/lib64/perl5/auto/DBI/
Generating a Unix-style Makefile
Writing Makefile for DBD::mysql
Writing MYMETA.yml and MYMETA.json
cp lib/DBD/mysql/GetInfo.pm blib/lib/DBD/mysql/GetInfo.pm
cp lib/DBD/mysql.pm blib/lib/DBD/mysql.pm
cp README.pod blib/lib/DBD/README.pod
cp lib/DBD/mysql/INSTALL.pod blib/lib/DBD/mysql/INSTALL.pod
cp lib/Bundle/DBD/mysql.pm blib/lib/Bundle/DBD/mysql.pm
gcc -c  -I/usr/local/lib64/perl5/auto/DBI -I/usr/include/mysql  -g   -DUNIV_LINU                                                                            X -DUNIV_LINUX -DDBD_MYSQL_INSERT_ID_IS_GOOD -g  -D_REENTRANT -D_GNU_SOURCE -fno                                                                            -strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURC                                                                            E -D_FILE_OFFSET_BITS=64 -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions                                                                             -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic   -DVERSION=\"4                                                                            .025\" -DXS_VERSION=\"4.025\" -fPIC "-I/usr/lib64/perl5/CORE"   dbdimp.c
dbdimp.c: In function 'dbd_discon_all':
dbdimp.c:2229: warning: unused variable 'imp_xxh'
dbdimp.c: In function 'mysql_db_STORE_attrib':
dbdimp.c:2405: warning: value computed is not used
dbdimp.c: In function 'mysql_db_FETCH_attrib':
dbdimp.c:2518: warning: value computed is not used
dbdimp.c:2525: warning: value computed is not used
dbdimp.c: In function 'mysql_st_prepare':
dbdimp.c:2643: warning: unused variable 'limit_flag'
dbdimp.c: In function 'mysql_st_next_results':
dbdimp.c:3079: warning: value computed is not used
dbdimp.c:3080: warning: value computed is not used
dbdimp.c:3081: warning: value computed is not used
dbdimp.c:3082: warning: value computed is not used
dbdimp.c:3083: warning: value computed is not used
dbdimp.c:3084: warning: value computed is not used
dbdimp.c:3085: warning: value computed is not used
dbdimp.c:3086: warning: value computed is not used
dbdimp.c:3087: warning: value computed is not used
dbdimp.c:3088: warning: value computed is not used
dbdimp.c:3089: warning: value computed is not used
dbdimp.c:3090: warning: value computed is not used
dbdimp.c:3091: warning: value computed is not used
dbdimp.c:3092: warning: value computed is not used
dbdimp.c:3093: warning: value computed is not used
dbdimp.c:3094: warning: value computed is not used
dbdimp.c:3095: warning: value computed is not used
dbdimp.c:3096: warning: value computed is not used
dbdimp.c: In function 'mysql_st_FETCH_attrib':
dbdimp.c:4491: warning: value computed is not used
/usr/bin/perl -p -e "s/~DRIVER~/mysql/g" /usr/local/lib64/perl5/auto/DBI/Driver.xst > mysql.xsi
/usr/bin/perl /usr/share/perl5/ExtUtils/xsubpp  -typemap /usr/share/perl5/ExtUtils/typemap  mysql.xs > mysql.xsc && mv mysql.xsc mysql.c
Warning: duplicate function definition 'do' detected in mysql.xs, line 242
Warning: duplicate function definition 'rows' detected in mysql.xs, line 754
gcc -c  -I/usr/local/lib64/perl5/auto/DBI -I/usr/include/mysql  -g   -DUNIV_LINUX -DUNIV_LINUX -DDBD_MYSQL_INSERT_ID_IS_GOOD -g  -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic   -DVERSION=\"4.025\" -DXS_VERSION=\"4.025\" -fPIC "-I/usr/lib64/perl5/CORE"   mysql.c
mysql.xs: In function 'XS_DBD__mysql__db_do':
mysql.xs:309: warning: value computed is not used
mysql.xs:260: warning: unused variable 'statement_ptr'
mysql.xs: In function 'XS_DBD__mysql__st_more_results':
mysql.xs:691: warning: unused variable 'retval'
rm -f blib/arch/auto/DBD/mysql/mysql.so
LD_RUN_PATH="/usr/lib64/mysql" /usr/bin/perl myld gcc  -shared -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic dbdimp.o mysql.o  -o blib/arch/auto/DBD/mysql/mysql.so       \
           -L/usr/lib64/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -lmygcc       \

chmod 755 blib/arch/auto/DBD/mysql/mysql.so
/usr/bin/perl -MExtUtils::Command::MM -e 'cp_nonempty' -- mysql.bs blib/arch/auto/DBD/mysql/mysql.bs 644
Manifying blib/man3/DBD::README.3pm
Manifying blib/man3/Bundle::DBD::mysql.3pm
Manifying blib/man3/DBD::mysql.3pm
Manifying blib/man3/DBD::mysql::INSTALL.3pm
  CAPTTOFU/DBD-mysql-4.025.tar.gz
  /usr/bin/gmake -- OK
'YAML' not installed, will not store persistent state
Running make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-MTest::Harness" "-e" "undef *Test::Harness::Switches; test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/00base.t ........................... ok
t/10connect.t ........................ Bailout called.  Further testing stopped:  ERROR: Access denied for user 'root'@'localhost' (using password: NO)
FAILED--Further testing stopped: ERROR: Access denied for user 'root'@'localhost' (using password: NO)
gmake: *** [test_dynamic] Error 255
  CAPTTOFU/DBD-mysql-4.025.tar.gz
  /usr/bin/gmake test -- NOT OK
//hint// to see the cpan-testers results for installing this module, try:
  reports CAPTTOFU/DBD-mysql-4.025.tar.gz
Running make install
  make test had returned bad status, won't install without force
----------------------------------------------------------
Installation complete. Exiting script...
Please re-run ./mysqlmymonlite.sh again

thinkbot · Jan 11, 2014

Please run mysqltuner
It gives better info in your case

k2tec · Jan 11, 2014

Thinkbot I tried to install the module but give me the above errors. I think it has something todo with the perl script.

thinkbot · Jan 11, 2014

its becouse you didnt provide corret mysql root and pass

you can create a file /root/.my.cnf
with content
[client]
user=root
password=mysql_pass

and rerun mysqltuner and mysqlmonlite again

k2tec · Jan 11, 2014

Thinkbot there is a file .my.cnf with the user and password.

[client]
user=root
password=mypassword

I changed it with a new password.But still the same error when i run ./mysqlmymonlite.sh or ./mysqlmymonlite.sh mysqltuner

thinkbot · Jan 11, 2014

are you sure its correct ?
FAILED--Further testing stopped: ERROR: Access denied for user 'root'@'localhost' (using password: NO)
gmake: *** [test_dynamic] Error 255

above you tried running mysqlmonlite

try to run mysqltuner.pl (https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl)

k2tec · Jan 11, 2014

Okay Thinkbot, I installed the mysqltuner.pl and it was possible to run it. Strange that the mysqlmonlite won't run or install
But I can manage this with the mysqltuner.
This is my outcome:

Code:

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 15M (Tables: 174)
[--] Data in InnoDB tables: 256K (Tables: 16)
[!!] Total fragmented tables: 17

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7d 19h 8m 22s (2M q [3.787 qps], 50K conn, TX: 3B, RX: 720M)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 449.2M (9% of installed RAM)
[OK] Slow queries: 0% (1/2M)
[OK] Highest usage of available connections: 5% (8/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/5.7M
[OK] Key buffer hit rate: 100.0% (2B cached / 67K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (77 temp sorts / 1M sorts)
[!!] Temporary tables created on disk: 44% (695K on disk / 1M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 20K opened)
[OK] Open file limit used: 12% (125/1K)
[OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
[OK] InnoDB data size / buffer pool: 256.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)

Now I hope it accepts the changes in the /etc/my.cnf.
I will let you know the results

thinkbot · Jan 11, 2014

new my.cnf

[mysqld]
innodb_file_per_table=1
bind-address=127.0.0.1
local-infile=0

key_buffer_size = 50M
query_cache_type = 1
query_cache_size = 50M
query_cache_limit = 1M
thread_cache_size = 32
table_open_cache = 2000
tmp_table_size = 50M
max_heap_table_size = 50M

innodb_buffer_pool_size = 20M

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1

and save
after that, remove old slow log
rm -rf /var/lib/mysql/mysql-slow.log
an
service mysql restart

k2tec · Jan 11, 2014

Thanks, runs perfect.
Now I see the problems with some queries.
Have nice weekend

goodmove · May 25, 2014

Thinkbot, what changes would you advise for this? (VPS with 4 cores)

[email protected] [~]# perl mysqltuner.pl

>> MySQLTuner 1.3.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at MySQLTuner-perl by major
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.36-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 4G (Tables: 8793)
[--] Data in MRG_MYISAM tables: 19M (Tables: 63)
[--] Data in InnoDB tables: 161M (Tables: 2708)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 93)
[!!] Total fragmented tables: 2914

-------- Performance Metrics -------------------------------------------------
[--] Up for: 20d 6h 40m 53s (27M q [15.441 qps], 968K conn, TX: 95B, RX: 6B)
[--] Reads / Writes: 60% / 40%
[--] Total buffers: 320.0M global + 2.8M per thread (200 max threads)
[OK] Maximum possible memory usage: 870.0M (6% of installed RAM)
[OK] Slow queries: 0% (343/27M)
[OK] Highest usage of available connections: 6% (13/200)
[OK] Key buffer size / total MyISAM indexes: 128.0M/472.9M
[OK] Key buffer hit rate: 98.9% (125M cached / 1M reads)
[OK] Query cache efficiency: 81.6% (16M cached / 20M selects)
[!!] Query cache prunes per day: 73192
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 258302
[OK] Temporary tables created on disk: 19% (436K on disk / 2M total)
[OK] Thread cache hit rate: 99% (13 created / 968K connections)
[!!] Table cache hit rate: 1% (1K open / 65K opened)
[OK] Open file limit used: 3% (1K/50K)
[OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)
[!!] InnoDB buffer pool / data size: 128.0M/161.4M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Read this before increasing table_cache over 64: table_cache negative scalability - MySQL Performance Blog
Variables to adjust:
query_cache_size (> 32M)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 1024)
innodb_buffer_pool_size (>= 161M)

---

[email protected] [~]# cat /etc/my.cnf
[mysqld]
local-infile=0
skip-external-locking
max_connections = 200
key_buffer_size = 128M
table_cache = 1024
thread_cache_size = 32
wait_timeout = 15 # 30
connect_timeout = 10
interactive_timeout = 100
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
thread_concurrency = 2
long_query_time = 2
open_files_limit=50000
[mysql]
no-auto-rehash
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqld_safe]

thinkbot · May 25, 2014

innodb_buffer_pool_size = 200M
key_buffer_size = 500M
query_cache_size = 50M
table_cache = 6000

[!!] Joins performed without indexes: 258302
based on that we can say that there are many non optimized queries,
so after long_query_time add
log-queries-not-using-indexes
to track those queries

goodmove · May 25, 2014

thinkbot said:
innodb_buffer_pool_size = 200M
key_buffer_size = 500M
query_cache_size = 50M
table_cache = 6000

[!!] Joins performed without indexes: 258302
based on that we can say that there are many non optimized queries,
so after long_query_time add
log-queries-not-using-indexes
to track those queries

Thanks Thinkbot. Do you advise that I should just change those 4 values, add log-queries-not-using-indexes, and leave the rest of my.cnf untouched?

thinkbot · May 26, 2014

yes, you can also lower long_query_time to 0.2
to get more slow queries to review

goodmove · Jun 6, 2014

thinkbot said:
yes, you can also lower long_query_time to 0.2
to get more slow queries to review

Thanks, Thinkbot. Things seem to be looking up after applying your suggestions. Here are the reults about 10 days later. What settings do you advise that I should change or add next?

[email protected] [~]# perl /root/mysqltuner.pl

>> MySQLTuner 1.3.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at MySQLTuner-perl by major
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.36-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 4G (Tables: 8793)
[--] Data in MRG_MYISAM tables: 19M (Tables: 63)
[--] Data in InnoDB tables: 163M (Tables: 2840)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 97)
[!!] Total fragmented tables: 3039

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6d 8h 24m 57s (7M q [13.089 qps], 178K conn, TX: 32B, RX: 2B)
[--] Reads / Writes: 64% / 36%
[--] Total buffers: 782.0M global + 2.8M per thread (200 max threads)
[OK] Maximum possible memory usage: 1.3G (9% of installed RAM)
[OK] Slow queries: 0% (3/7M)
[OK] Highest usage of available connections: 5% (11/200)
[OK] Key buffer size / total MyISAM indexes: 500.0M/490.0M
[OK] Key buffer hit rate: 98.7% (35M cached / 469K reads
[OK] Query cache efficiency: 85.1% (5M cached / 5M selects)
[!!] Query cache prunes per day: 47911
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 342K sorts)
[!!] Joins performed without indexes: 85786
[OK] Temporary tables created on disk: 19% (133K on disk / 684K total)
[OK] Thread cache hit rate: 99% (11 created / 178K connections)
[OK] Table cache hit rate: 23% (2K open / 9K opened)
[OK] Open file limit used: 5% (2K/50K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB buffer pool / data size: 200.0M/163.8M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Variables to adjust:
query_cache_size (> 50M)
join_buffer_size (> 128.0K, or always use indexes with joins)

Thread starter	Similar threads	Forum	Replies	Date
T	Wordpress Admin Very Slow	Workarounds and Optimization	3	May 11, 2020
V	Site loading very slow	Workarounds and Optimization	2	Jun 17, 2018
K	Server Very Slow (Cpu, Load Fine)	Workarounds and Optimization	1	Aug 14, 2017
Y	Site very slow, High TTFB, what happened?	Workarounds and Optimization	3	Aug 11, 2017
H	Very slow new account	Workarounds and Optimization	2	Oct 9, 2016

Very slow queries on Cloud VPS

Well-Known Member

Administrator

Well-Known Member

Senior Technical Analyst

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member