hi
i have some serious issue with mysql
this is my.cnf file
this mysqltunner.pl results:
and this is my process list
i dont have any heavy query according to my programer but mysql using cpu for nothing!
please help me
thank you
any suggestion?
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
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.
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 |
+------+-----------------+-----------+-----------------+---------+------+-------+------------------+----------+please help me
thank you
any suggestion?
Last edited by a moderator:
