1

Firstly, I am a beginner. I have a server, which has 32 GB RAM and 16 CPUs.

I am working on MySQL optimization, but I have a problem.

I am trying to set the InnoDB buffer pool instances = 8. But it's not changing. It's still 1.

And the CPU usage is between 50% and 55%. I think it's a problem for my database.

My.cnf:

[mysqld]
performance-schema=1
sql_mode = ''

#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 900M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
 join_buffer_size = 12M
 sort_buffer_size = 1M
 read_rnd_buffer_size = 1M
 thread_cache_size = 128
 query_cache_type = 1
 query_cache_size = 128M
 query_cache_limit = 8M  
 max_connections = 1500
 tmp_table_size = 64M
 max_heap_table_size = 64M
 long_query_time = 2
 key_buffer_size = 360M

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

performance-schema=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

performance-schema=1
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000

Mysqltuner:

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 533.5M (Tables: 9)
[--] Data in InnoDB tables: 228.8M (Tables: 47)
[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
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 19h 5m 27s (9M q [133.395 qps], 1M conn, TX: 1G, RX: 565M)
[--] Reads / Writes: 86% / 14%
[--] Binary logging is disabled
[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 22.6G
[--] Other process memory: 1.2G
[--] Total buffers: 1.6G global + 14.4M per thread (1500 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.9G (6.17% of installed RAM)
[OK] Maximum possible memory usage: 22.6G (72.14% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/9M)
[OK] Highest usage of available connections: 1% (27/1500)
[OK] Aborted connections: 0.03%  (406/1315684)
[!!] 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.
[OK] Query cache efficiency: 26.5% (422K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (60 temp sorts / 22K sorts)
[!!] Joins performed without indexes: 245
[OK] Temporary tables created on disk: 2% (1K on disk / 60K total)
[OK] Thread cache hit rate: 99% (27 created / 1M connections)
[!!] Table cache hit rate: 9% (1K open / 19K opened)
[OK] Open file limit used: 2% (232/10K)
[!!] Table locks acquired immediately: 68%

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

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 33.8% (127M used / 377M cache)
[OK] Key buffer size / total MyISAM indexes: 360.0M/311.0M
[OK] Read Key buffer hit rate: 99.3% (4M cached / 34K reads)
[!!] Write Key buffer hit rate: 81.6% (1M cached / 1M writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 1.0G/228.8M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (9.375 %): 48.0M * 2/1.0G should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 8 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: 100.00% (1783827282 hits/ 1783833972 total)
[!!] InnoDB Write Log efficiency: 78.61% (138413 hits/ 176074 total)
[OK] InnoDB log waits: 0.00% (0 waits / 314487 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:
    /var/log/mysqld.log is > 32Mb, you should analyze why or implement a rotation log strategy such as logrotate!
    Control warning line(s) into /var/log/mysqld.log file
    Control error line(s) into /var/log/mysqld.log file
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: 
    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)
    Optimize queries and/or use InnoDB to reduce lock wait
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: 
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    join_buffer_size (> 12.0M, or always use indexes with JOINs)
    table_open_cache (> 2000)
    innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size
  • Additional information request, please. Post on pastebin.com or here. RAM size of your MySQL Host server Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, for server tuning analysis. – Wilson Hauck Oct 27 '18 at 19:16
0

Nope.

You can't "tune your way out of a performance problem".

High CPU is almost always caused by a query that needs a better index or that needs to be rewritten.

Please locate the slow query. You could try SHOW FULL PROCESSLIST; while it is running. And/or, you could set up the slowlog to catch it.

Once you find it, please provide SHOW CREATE TABLE and EXPLAIN SELECT ... -- We can probably get the CPU% down into the single-digits.

More on slowlog: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Since the innodb_buffer_pool_size is only 900M, there is no use in having more than one 'instance'. (The rule is 1 instance per gig.) Anyway, there is very little to be gained by increasing the number of instances.

You seem to be using MyISAM for some of your tables. Recommend switching to InnoDB.

0

Suggestions to consider for your OS Open files capacity. ulimit -a will reveal your current limit. ulimit -n 48000 we enable up to 48000 Open files.

For this change to be persistent across OS shutdown/restart's review this URL, please.
https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/

Your details may be slightly different for your version.

Rate Per Second = RPS

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

join_buffer_size=128K  # from 12M  for default to conserve WASTED RAM per connection
read_rnd_buffer_size=256K  # from 1M to reduce handler_read_rnd_next RPS
max_connections=256  # from 1500 to conserve RAM, only 27 used per Mysqltuner
thread_cache_size=100  # from 128 for CAP per 5.7 refman to avoid OOM
query_cache_type=0  # from 1 to turn OFF only 26% answered by QC
query_cache_size=0  # from 128M to conserve RAM and CPU cycles every second.
table_open_cache=3000  # from ~ 1K per Mysqltuner to reduce opened_tables
key_cache_age_threshold=7200  # from 300 to reduce key_reads
key_cache_division_limit=50  # from 100 for Hot/Warm cache
key_cache_block_size=16384  # from 1024 to reduce CPU use during age out

You will find these configuration changes will REDUCE CPU busy significantly.

For additional assistance, please view my profile, Network profile for contact info and get in touch by Skype, please.

  • @FBSA Were you able to implement any of the Suggestions above? If they were helpful and you can not Upvote or Accept any comment or Answer, please post a comment about the Suggestion being positive or negative for performance of your Instance. Thanks – Wilson Hauck Feb 27 at 10:50

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.