seng fatt

Registered
Dec 11, 2016
2
0
1
iraq
cPanel Access Level
Root Administrator
hi
i have some serious issue with mysql
this is my.cnf file
Code:
[mysql]
#port                           = 3306
#socket                         = /var/lib/mysql/mysql.sock
[mysqld]
#user                           = mysql
innodb_lock_wait_timeout=2000
#local-infile=0
default-storage-engine=InnoDB
#socket                         = /var/lib/mysql/mysql.sock
key-buffer-size=32M
myisam-recover="FORCE,BACKUP"
thread_concurrency=16
max-allowed-packet=16M
#max-connect-errors=1000000
max-connect-errors=100
datadir="/var/lib/mysql/"
expire-logs-days=14
sync-binlog=1
tmp-table-size=50M
max-heap-table-size=50M
query-cache-type=0
query-cache-size=128M
max-connections=2000
thread-cache-size=150
open-files-limit=65535
table-definition-cache=4096
table-open-cache=1000
innodb-flush-method=O_DIRECT
innodb-log-files-in-group=2
innodb-flush-log-at-trx-commit=1
innodb_buffer_pool_instances=1
innodb-file-per-table=1
log-queries-not-using-indexes=1
innodb_file_per_table=1
max_allowed_packet=1073741824
open_files_limit=50000
#innodb_buffer_pool_size=3000M
key_buffer_size         = 350M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
max_connections = 50     #default is 150
wait_timeout = 30        #default is 28800
innodb_buffer_pool_size = 2000M
innodb_log_file_size = 8M
innodb_thread_concurrency = 8
innodb_file_per_table
query_cache_size        = 16M
log-queries-not-using-indexes
this mysqltunner.pl results:
Code:
 >>  MySQLTuner 1.7.0 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[!!] Currently running unsupported MySQL version 10.0.28-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ----------------------------------------------                                                                                        --------------------
[--] Log file: /var/lib/mysql/server.example.com.err(63K)
[OK] Log file /var/lib/mysql/server.example.com.err exists
[OK] Log file /var/lib/mysql/server.example.com.err is readable.
[OK] Log file /var/lib/mysql/server.example.com.err is not empty
[OK] Log file /var/lib/mysql/server.example.com.err is smaller than 32 Mb
[!!] /var/lib/mysql/server.example.com.err contains 72 warning(s).
[!!] /var/lib/mysql/server.example.com.err contains 31 error(s).
[--] 22 start(s) detected in /var/lib/mysql/server.example.com.err
[--] 1) 161211 23:48:20 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 161211 23:45:24 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 161211 23:44:46 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 161211 23:34:56 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 161211 23:25:28 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 161211 23:24:16 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 161211 23:07:13 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 161211 22:57:43 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 161211 22:56:10 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 161211 22:55:47 [Note] /usr/sbin/mysqld: ready for connections.
[--] 25 shutdown(s) detected in /var/lib/mysql/server.example.com.err
[--] 1) 161211 23:48:19 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 161211 23:45:23 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 161211 23:44:44 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 161211 23:34:55 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 161211 23:25:27 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 161211 23:06:35 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 161211 23:06:34 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 161211 22:57:23 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 161211 22:57:02 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 161211 22:56:58 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics ---------------------------------------------                                                                                        --------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyIS                                                                                        AM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 879M (Tables: 19)
[--] Data in InnoDB tables: 186M (Tables: 68)
[OK] Total fragmented tables: 0

-------- Security Recommendations ----------------------------------------------                                                                                        --------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User '[email protected]' has user name as password.
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations ------------------------------------------                                                                                        --------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics ---------------------------------------------------                                                                                        --------------------
[--] Up for: 29m 11s (214K q [122.612 qps], 3K conn, TX: 140G, RX: 17M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 9.6G
[--] Max MySQL memory    : 2.6G
[--] Other process memory: 1.4G
[--] Total buffers: 2.5G global + 2.7M per thread (50 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 2.5G (26.26% of installed RAM)
[OK] Maximum possible memory usage: 2.6G (27.35% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[!!] Slow queries: 11% (24K/214K)
[OK] Highest usage of available connections: 20% (10/50)
[OK] Aborted connections: 0.06%  (2/3470)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 16K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 60% (992 on disk / 1K total)
[OK] Thread cache hit rate: 99% (10 created / 3K connections)
[OK] Table cache hit rate: 152% (184 open / 121 opened)
[OK] Open file limit used: 0% (105/50K)
[OK] Table locks acquired immediately: 99% (212K immediate / 213K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 4 thread(s).
[--] Using default value is good enough for your version (10.0.28-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 31.5% (115M used / 367M cache)
[OK] Key buffer size / total MyISAM indexes: 350.0M/224.2M
[OK] Read Key buffer hit rate: 99.1% (5M cached / 44K reads)
[!!] Write Key buffer hit rate: 7.7% (22K cached / 20K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 8
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 2.0G/186.9M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.8 %): 8.0M * 2/2.0G should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.97% (24245691 hits/ 24253713 total)
[!!] InnoDB Write Log efficiency: 52.48% (3107 hits/ 5920 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2813 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.8% (425K cached / 913 reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/lib/mysql/server.example.com.err file
    Control error line(s) into /var/lib/mysql/server.example.com.err file
    Set up a Secure Password for [email protected] ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    MySQL started within last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
    tmp_table_size (> 50M)
    max_heap_table_size (> 50M)
    innodb_log_file_size should be equals to 1/4 of buffer pool size (=500M) if possible.
and this is my process list
Code:
[email protected] [~]# mysqladmin processlist
+------+-----------------+-----------+-----------------+---------+------+-------+------------------+----------+
| Id   | User            | Host      | db              | Command | Time | State | Info             | Progress |
+------+-----------------+-----------+-----------------+---------+------+-------+------------------+----------+
| 4332 | hubpre13_amnran | localhost | hubpre13_amnran | Sleep   | 0    |       |                  | 0.000    |
| 4333 | hubpre13_amnran | localhost | hubpre13_amnran | Sleep   | 2    |       |                  | 0.000    |
| 4334 | hubpre13_amnran | localhost | hubpre13_amnran | Sleep   | 1    |       |                  | 0.000    |
| 4335 | hubpre13_amnran | localhost | hubpre13_amnran | Sleep   | 1    |       |                  | 0.000    |
| 4336 | root            | localhost |                 | Query   | 0    | init  | show processlist | 0.000    |
+------+-----------------+-----------+-----------------+---------+------+-------+------------------+----------+
i dont have any heavy query according to my programer but mysql using cpu for nothing!
please help me
thank you

any suggestion?
 
Last edited by a moderator:

seng fatt

Registered
Dec 11, 2016
2
0
1
iraq
cPanel Access Level
Root Administrator
You can't get proper results from a system that's not been up at least 24 hours:


Once you do that, the section titled, General recommendations: in the output, might be a nice place to start. ;)
this is new log
Code:
[email protected] [~]# perl mysqltuner.pl
>>  MySQLTuner 1.7.0 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[!!] Currently running unsupported MySQL version 10.0.28-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ----------------------------------------------                                                                                        --------------------
[--] Log file: /var/lib/mysql/server.example.com.err(63K)
[OK] Log file /var/lib/mysql/server.example.com.err exists
[OK] Log file /var/lib/mysql/server.example.com.err is readable.
[OK] Log file /var/lib/mysql/server.example.com.err is not empty
[OK] Log file /var/lib/mysql/server.example.com.err is smaller than 32 Mb
[!!] /var/lib/mysql/server.example.com.err contains 72 warning(s).
[!!] /var/lib/mysql/server.example.com.err contains 31 error(s).
[--] 22 start(s) detected in /var/lib/mysql/server.eaglescity.com.err
[--] 1) 161211 23:48:20 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 161211 23:45:24 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 161211 23:44:46 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 161211 23:34:56 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 161211 23:25:28 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 161211 23:24:16 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 161211 23:07:13 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 161211 22:57:43 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 161211 22:56:10 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 161211 22:55:47 [Note] /usr/sbin/mysqld: ready for connections.
[--] 25 shutdown(s) detected in /var/lib/mysql/server.example.com.err
[--] 1) 161211 23:48:19 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 161211 23:45:23 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 161211 23:44:44 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 161211 23:34:55 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 161211 23:25:27 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 161211 23:06:35 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 161211 23:06:34 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 161211 22:57:23 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 161211 22:57:02 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 161211 22:56:58 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics ---------------------------------------------                                                                                        --------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyIS                                                                                        AM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 901M (Tables: 19)
[--] Data in InnoDB tables: 202M (Tables: 68)
[OK] Total fragmented tables: 0

-------- Security Recommendations ----------------------------------------------                                                                                        --------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User '[email protected]' has user name as password.
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 0h 23m 17s (9M q [106.656 qps], 128K conn, TX: 7651G, RX: 673M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 9.6G
[--] Max MySQL memory    : 2.6G
[--] Other process memory: 1.7G
[--] Total buffers: 2.5G global + 2.7M per thread (50 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 2.5G (26.31% of installed RAM)
[OK] Maximum possible memory usage: 2.6G (27.35% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[!!] Slow queries: 12% (1M/9M)
[OK] Highest usage of available connections: 24% (12/50)
[OK] Aborted connections: 0.01%  (9/128490)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (16 temp sorts / 677K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 63% (16K on disk / 26K total)
[OK] Thread cache hit rate: 99% (78 created / 128K connections)
[OK] Table cache hit rate: 135% (203 open / 150 opened)
[OK] Open file limit used: 0% (114/50K)
[OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 4 thread(s).
[--] Using default value is good enough for your version (10.0.28-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 58.3% (213M used / 367M cache)
[OK] Key buffer size / total MyISAM indexes: 350.0M/236.1M
[OK] Read Key buffer hit rate: 99.9% (195M cached / 128K reads)
[!!] Write Key buffer hit rate: 12.2% (732K cached / 643K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 8
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 2.0G/202.5M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.8 %): 8.0M * 2/2.0G should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (797716161 hits/ 797726689 total)
[!!] InnoDB Write Log efficiency: 53.07% (120023 hits/ 226147 total)
[OK] InnoDB log waits: 0.00% (0 waits / 106124 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.8% (5M cached / 13K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/lib/mysql/server.eaglescity.com.err file
    Control error line(s) into /var/lib/mysql/server.eaglescity.com.err file
    Set up a Secure Password for [email protected] ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
    tmp_table_size (> 50M)
    max_heap_table_size (> 50M)
    innodb_log_file_size should be equals to 1/4 of buffer pool size (=500M) if possible.
 
Last edited by a moderator:

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,908
2,218
463
Hello,

You can review the "Variables to adjust" section and make adjustments in your /etc/my.cnf to meet those suggestions. Also, you may want to use a command such as "mysqladmin processlist;" when the server resource usage is high to see if you can determine which databases are the culprit.

Thank you.