The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Very slow queries on Cloud VPS

Discussion in 'Workarounds and Optimization' started by k2tec, Jan 10, 2014.

  1. k2tec

    k2tec Well-Known Member

    Joined:
    Aug 26, 2011
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Netherlands
    cPanel Access Level:
    Root Administrator
    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
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. k2tec

    k2tec Well-Known Member

    Joined:
    Aug 26, 2011
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Netherlands
    cPanel Access Level:
    Root Administrator
    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)
     
  4. cPanelPeter

    cPanelPeter Technical Analyst III
    Staff Member

    Joined:
    Sep 23, 2013
    Messages:
    569
    Likes Received:
    15
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
  5. k2tec

    k2tec Well-Known Member

    Joined:
    Aug 26, 2011
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Netherlands
    cPanel Access Level:
    Root Administrator
    Hello Peter,

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

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    run mysqltuner and post results
     
  7. k2tec

    k2tec Well-Known Member

    Joined:
    Aug 26, 2011
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Netherlands
    cPanel Access Level:
    Root Administrator
    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
    
     
  8. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Please run mysqltuner
    It gives better info in your case
     
  9. k2tec

    k2tec Well-Known Member

    Joined:
    Aug 26, 2011
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Netherlands
    cPanel Access Level:
    Root Administrator
    Thinkbot I tried to install the module but give me the above errors. I think it has something todo with the perl script.
     
  10. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    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
     
  11. k2tec

    k2tec Well-Known Member

    Joined:
    Aug 26, 2011
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Netherlands
    cPanel Access Level:
    Root Administrator
    Thinkbot there is a file .my.cnf with the user and password.
    I changed it with a new password.But still the same error when i run ./mysqlmymonlite.sh or ./mysqlmymonlite.sh mysqltuner
     
  12. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
  13. k2tec

    k2tec Well-Known Member

    Joined:
    Aug 26, 2011
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Netherlands
    cPanel Access Level:
    Root Administrator
    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
     
  14. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    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
     
  15. k2tec

    k2tec Well-Known Member

    Joined:
    Aug 26, 2011
    Messages:
    81
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Netherlands
    cPanel Access Level:
    Root Administrator
    Thanks, runs perfect.
    Now I see the problems with some queries.
    Have nice weekend
     
  16. goodmove

    goodmove Well-Known Member

    Joined:
    May 12, 2003
    Messages:
    624
    Likes Received:
    0
    Trophy Points:
    16
    Thinkbot, what changes would you advise for this? (VPS with 4 cores)

    root@srv1 [~]# perl mysqltuner.pl

    >> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
    >> 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)

    ---

    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]
     
  17. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    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
     
  18. goodmove

    goodmove Well-Known Member

    Joined:
    May 12, 2003
    Messages:
    624
    Likes Received:
    0
    Trophy Points:
    16
    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?
     
  19. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    yes, you can also lower long_query_time to 0.2
    to get more slow queries to review
     
  20. goodmove

    goodmove Well-Known Member

    Joined:
    May 12, 2003
    Messages:
    624
    Likes Received:
    0
    Trophy Points:
    16
    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 <major@mhtx.net>
    >> 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)
     
Loading...

Share This Page