Skip to main content

Very slow queries on Cloud VPS

k2tec
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 [mysqld] innodb_file_per_table=1 bind-address=127.0.0.1 local-infile=0
Processes on server status: /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

Comments

19 comments

Date Votes
  • cPanelMichael
    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.
    0
  • k2tec
    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: 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: 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)
    0
  • cPanelPeter cPanel Staff
    Hello, There are several errors in your /etc/my.cnf file. Try commenting them out or editting them accordingly. The message says: 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.
    0
  • k2tec
    Hello Peter, Thanks for your reply, I willtry it after weekend. Because the site is very busy in the weekend.
    0
  • thinkbot
    run mysqltuner and post results
    0
  • k2tec
    On my dedicated server it was no problem to install Mysqlmymonlite. But this VPS gives me this error when I run ./mysqlmymonlite.sh 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
    0
  • thinkbot
    Please run mysqltuner It gives better info in your case
    0
  • k2tec
    Thinkbot I tried to install the module but give me the above errors. I think it has something todo with the perl script.
    0
  • thinkbot
    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
    0
  • k2tec
    Thinkbot there is a file .my.cnf with the user and password. [QUOTE][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
    0
  • thinkbot
    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 (
    0
  • k2tec
    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: -------- 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
    0
  • thinkbot
    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
    0
  • k2tec
    Thanks, runs perfect. Now I see the problems with some queries. Have nice weekend
    0
  • goodmove
    Thinkbot, what changes would you advise for this? (VPS with 4 cores) root@srv1 [~]# perl mysqltuner.pl >> MySQLTuner 1.3.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]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: [url=http://bit.ly/1mi7c4C]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) --- root@srv1 [~]# 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]
    0
  • thinkbot
    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
    0
  • goodmove
    [quote="thinkbot, post: 1651561">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?
    0
  • thinkbot
    yes, you can also lower long_query_time to 0.2 to get more slow queries to review
    0
  • goodmove
    [quote="thinkbot, post: 1652171">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? root@srv1 [~]# perl /root/mysqltuner.pl >> MySQLTuner 1.3.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]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)
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post