-1

I have a server that is giving me CPU spikes and and is slow in serving pages:

Server information

Processor:  X3440 (2.53GHz Quad Core + HT)
Memory Type:    8GB DDR3-1333 REG ECC

Memory information

root@core [/programas/tuning-primer]# free -m total used free shared buff/cache available Mem: 7814 1651 1770 342 4392 5517 Swap: 8064 391 7673

MySQL tuning primer results

root@core [/programas/tuning-primer]# ./tuning-primer.sh 
 -- MYSQL PERFORMANCE TUNING PRIMER 2.0.1-r1 --
          - By: Matthew Montgomery -
          - By: Markus Kohlmeyer   -

MySQL Version 5.7.29-log x86_64

Uptime = 2 days 14 hrs 50 min 22 sec
Avg. qps = 1111
Total Questions = 251486181
Threads Connected = 9

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 10.000000 sec.
You have 3939000 out of 251487114 that take longer than 10.000000 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 9
Current threads_cached = 2
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 8
Historic max_used_connections = 21
The number of used connections is 13% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 650 M
Current InnoDB data space = 1.03 G
Current InnoDB buffer pool free = 10 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 176 M
Configured Max Per-thread Buffers : 169 M
Configured Max Global Buffers : 153 M
Configured Max Memory Limit : 322 M
Plus 16 M per temporary table created
Physical Memory : 7.63 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 211 K
Current key_buffer_size = 8 M
Key cache miss rate is 1 : 69
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 1 M
Current query_cache_used = 16 K
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 1.59 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 256 K
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 260.00 K
You have had 363 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.

OPEN FILES LIMIT
Current open_files_limit = 10000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_open_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 2000 tables
Current table_definition_cache = 1400 tables
You have a total of 1865 tables
You have 2000 open tables.
Current table_open_cache hit rate is 0%
, while 100% of your table cache is in use
You should probably increase your table_open_cache
You should probably increase your table_definition_cache value.

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 123362 temp tables, 39% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your 
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 279 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 8031
Your table locking seems to be fine

MySQLTuner results

root@core [/programas]# ./mysqltuner.pl
 >>  MySQLTuner 1.7.15 - Major Hayden <major@mhtx.net>
 >>  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
[OK] Currently running supported MySQL version 5.7.29-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/mysql_error.log(1K)
[OK] Log file /var/log/mysql/mysql_error.log exists
[OK] Log file /var/log/mysql/mysql_error.log is readable.
[OK] Log file /var/log/mysql/mysql_error.log is not empty
[OK] Log file /var/log/mysql/mysql_error.log is smaller than 32 Mb
[!!] /var/log/mysql/mysql_error.log contains 5 warning(s).
[!!] /var/log/mysql/mysql_error.log contains 5 error(s).
[--] 0 start(s) detected in /var/log/mysql/mysql_error.log
[--] 0 shutdown(s) detected in /var/log/mysql/mysql_error.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in MyISAM tables: 352.7K (Tables: 12)
[--] Data in InnoDB tables: 1.7G (Tables: 1734)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (IF(plugin='mysql_native_password', authentication_string, password) = '' OR IF(plugin='mysql_native_password', authentication_string, password) IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket', 'auth_pam_compat')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE CAST(IF(plugin='mysql_native_password', authentication_string, password) as Binary) = PASSWORD(user) OR CAST(IF(plugin='mysql_native_password', authentication_string, password) as Binary) = PASSWORD(UPPER(user)) OR CAST(IF(plugin='mysql_native_password', authentication_string, password) as Binary) = PASSWORD(CONCAT(UPPER(LEFT(User, 1)), SUBSTRING(User, 2, LENGTH(User))))
[!!] FAIL Execute SQL / return code: 256
[!!] User 'sce01@%' does not specify hostname restrictions.
[!!] User 'sce01_cdmx@%' does not specify hostname restrictions.
[!!] User 'sce01_frap@%' does not specify hostname restrictions.
[!!] User 'sce01_rest@%' does not specify hostname restrictions.
[!!] User 'sce01_resumen@%' does not specify hostname restrictions.
[!!] User 'sce01_roberto@%' does not specify hostname restrictions.
[!!] User 'sce01_sce@%' does not specify hostname restrictions.
[!!] User 'sce06@%' does not specify hostname restrictions.
[!!] User 'sce06_mexico@%' does not specify hostname restrictions.
[!!] User 'sce09@%' does not specify hostname restrictions.
[!!] User 'sce09_chiapas@%' does not specify hostname restrictions.
[!!] User 'sce09_fraps@%' does not specify hostname restrictions.
[!!] User 'sce09_resumen@%' does not specify hostname restrictions.
[!!] User 'sce09_roberto@%' does not specify hostname restrictions.
[!!] User 'sce09_sce@%' does not specify hostname restrictions.
[!!] User 'sce13@%' does not specify hostname restrictions.
[!!] User 'sce13_coahuila@%' does not specify hostname restrictions.
[!!] User 'sce13_resumen@%' does not specify hostname restrictions.
[!!] User 'sce13_roberto@%' does not specify hostname restrictions.
[!!] User 'sce13_sce@%' does not specify hostname restrictions.
[!!] User 'sce13_usrDB1@%' does not specify hostname restrictions.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 15h 3m 34s (252M q [1K qps], 3M conn, TX: 348G, RX: 10G)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 7.6G
[--] Max MySQL memory    : 338.9M
[--] Other process memory: 0B
[--] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 192.6M (2.46% of installed RAM)
[OK] Maximum possible memory usage: 338.9M (4.34% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 1% (3M/252M)
[OK] Highest usage of available connections: 13% (21/151)
[OK] Aborted connections: 0.15%  (5155/3394076)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 109M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (6K temp sorts / 1M sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 64% (81K on disk / 125K total)
[OK] Thread cache hit rate: 99% (4K created / 3M connections)
[!!] Table cache hit rate: 0% (2K open / 421K opened)
[OK] Open file limit used: 0% (25/10K)
[OK] Table locks acquired immediately: 99% (16K immediate / 16K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.4% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/211.0K
[OK] Read Key buffer hit rate: 98.6% (22K cached / 315 reads)
[OK] Write Key buffer hit rate: 100.0% (6 cached / 6 writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/1.7G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.93% (16944063226 hits/ 16956171653 total)
[!!] InnoDB Write Log efficiency: 13.72% (357933 hits/ 2608008 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2250075 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

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

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

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

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysql/mysql_error.log file
    Control error line(s) into /var/log/mysql/mysql_error.log file
    Restrict Host for user@% to user@SpecificDNSorIp
    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
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: https://bit.ly/1mi7c4C
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (10000) variable 
    should be greater than table_open_cache (2000)
    Performance schema should be activated for better diagnostics
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 2000)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 1.7G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
root@core [/programas]# 

My.cnf

[mysqld]
performance-schema=0
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000

#Logueo
[mysqld_safe]
log_error=/var/log/mysql/mysql_error.log
[mysqld]
log_error=/var/log/mysql/mysql_error.log
general_log_file = /var/log/mysql/mysql.log
general_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log = 1
log-queries-not-using-indexes

What do I need to adjust in order to increase the performance of my MySQL instance?

  • You "can't tune your way out of a performance problem". And, "High CPU means poor indexing and/or poor query formulation". I like the slowlog. See mysql.rjweb.org/doc.php/mysql_analysis – Rick James yesterday
  • OK, innodb_buffer_pool_size does needs increasing. – Rick James yesterday
0

In addition to what you scanners say:

Remove default-storage-engine=MyISAM. Innodb is generally better overall.

Only set log-queries-not-using-indexes with min_examined_row_limit between 1 and 10k or the slow query log will be useless. Especially note long_query_time should be 1 (second) or less (10 seconds in undoubtedly unacceptable for your queries).

"innodb_buffer_pool_size (>= 1.7G) if possible." will give you probably the best gain.

After that look at the slow queries being generated and start fixing those. Include detail like SHOW CREATE TABLE {tablename}, EXPLAIN {query} on your next questions. You can't just tune yourself out of every problem.

  • So my.cnf will looks like this: [mysqld] performance-schema=0 default-storage-engine=MyISAM innodb_file_per_table=1 max_allowed_packet=268435456 open_files_limit=10000 innodb_buffer_pool_size=2G log_queries_not_using_indexes =1 Where i must configure the min_examined_row_limit? – samuelmf Feb 11 at 21:19
  • [mysqld] section (either, there only needs to be one). Also there no need to disable performance-schema either. – danblack Feb 11 at 22:11
0

Suggestions to consider for your my.cnf [mysqld] section

You should have ONLY ONE [mysqld] section - put your two pieces together.
REMOVE general_log=1   you only turn this on and off for brief periods when needed
as danblack suggested REMOVE default-storage-engine to allow innodb to be default
innodb_buffer_pool_size=3G  # from 128M default size you are running with today
innodb_open_files=20000  # to match table_open_cache
table_open_cache=20000  # from 10000 to reduce opened_tables count
table_definition_cache=2000  # to cover MyISAM and innodb table count
open_files_limit=30000  # from 10000 and we may have to go higher still

For free downloadable Utility Scripts to improve performance, view profile, Network profile. You will see findfragtables.sql and find-redundant-indexes.sql are available.

For your OS Command Prompt,

ulimit -n 36000 and press Enter before your stop/start MySQL to allow more File Open count.
ulimit -a to verify current OS limit.
  • @samuelmf Please note changes in past hour. Thanks, Wilson – Wilson Hauck Feb 12 at 0:46
  • excellent point about disabling general_log except when needed (should be rarely). mysqld_safe settings and ulimits can significantly change per distro and how it starts. Looking forward to seeing some query related questions. – danblack Feb 12 at 2:17
  • Thanks i will do all this modifications – samuelmf Feb 12 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.