MySQL Optimization - Dedicated Server

NEK4TE

Member
Aug 8, 2015
10
0
1
Canada
cPanel Access Level
Website Owner
Good evening ladies and gents,

Would like to ask MySQL Guru's to help me optimize MySQL / Server the best, possible.

Server's specs:
E3-1245v3
32GB DDR3
2x2TB

Current Output of MySQLTuner
Code:
>>  MySQLTuner 1.4.0 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.6.23
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 118M (Tables: 157)
[--] Data in InnoDB tables: 3G (Tables: 376)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 0B (Tables: 17)
[!!] Total fragmented tables: 33

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 4h 40m 0s (2M q [25.451 qps], 148K conn, TX: 7B, RX: 572M)
[--] Reads / Writes: 65% / 35%
[--] Total buffers: 11.0G global + 41.0M per thread (256 max threads)
[!!] Maximum possible memory usage: 21.3G (680% of installed RAM)
[OK] Slow queries: 0% (15/2M)
[OK] Highest usage of available connections: 8% (21/256)
[OK] Key buffer size / total MyISAM indexes: 1.0G/23.0M
[OK] Key buffer hit rate: 98.9% (2M cached / 21K reads)
[OK] Query cache efficiency: 73.7% (1M cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 309K sorts)
[!!] Joins performed without indexes: 865
[OK] Temporary tables created on disk: 5% (22K on disk / 397K total)
[OK] Thread cache hit rate: 99% (21 created / 148K connections)
[OK] Table cache hit rate: 89% (1K open / 1K opened)
[OK] Open file limit used: 0% (525/200K)
[OK] Table locks acquired immediately: 99% (661K immediate / 661K locks)
[OK] InnoDB buffer pool / data size: 8.0G/3.7G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Adjust your join queries to always utilize indexes
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 8.0M, or always use indexes with joins)
My.CNF
Code:
[myisamchk]
sort_buffer_size=1024M
write_buffer_size=256M
key_buffer_size=1024M
ft_min_word_len=3
read_buffer_size=256M

[mysqldump]
max_allowed_packet=512M
quick

[mysqld]
max_allowed_packet=268435456
query_prealloc_size=262144
query_cache_size=1024M
max_heap_table_size=1024M
transaction_alloc_block_size=8192
connect_timeout=15
innodb_file_per_table=1
back_log=50
socket="/var/lib/mysql/mysql.sock"
max_connect_errors=1000000
ft_min_word_len=3
thread_cache_size=500
join_buffer_size=8M
sort_buffer_size=16M
group_concat_max_len=1024
open_files_limit=50000
low_priority_updates=1
local-infile=0
query_cache_limit=30M
max_connections=256
myisam_max_sort_file_size=2048M
bulk_insert_buffer_size=8M
interactive_timeout=300
default-storage-engine=MyISAM
net_buffer_length=16384
myisam_sort_buffer_size=768M
max_seeks_for_key=1000
transaction_prealloc_size=4096
table_definition_cache=32000
range_alloc_block_size=4096
query_cache_type=1
wait_timeout=180
key_buffer_size=1024M
datadir="/var/lib/mysql"
query_alloc_block_size=65536
tmp_table_size=1024M
innodb_buffer_pool_size=8G
innodb_buffer_pool_instances=8
max_write_lock_count=4
table_open_cache=100000
read_rnd_buffer_size=786432
concurrent_insert=2
expire_logs_days=10
read_buffer_size=16M
max_length_for_sort_data=1024

[mysqlhotcopy]
interactive-timeout
For some reason, MySQLTuner is reporting weird MAX RAM Allocation, looks like it thinks its "32bit OS"?
Code:
[!!] Maximum possible memory usage: 21.3G (680% of installed RAM)
Code:
/usr/bin/perl: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, stripped
OS / Kernel Version
Code:
2.6.32-573.1.1.el6.x86_64 #1 SMP Sat Jul 25 17:05:50 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux (CentOS 6, Latest stable)
Memory Limits
Code:
core file size          (blocks, -c) 1000000
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 127927
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 4096
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 14335
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
For couple of days (before opening this thread) I was tweaking/monitoring server myself, it doesn't look "bad" in my opinion :) but, i am seeing pretty high CPU usage.

[Removed]

Anything i can do to make it perform better?

Thank you for your time and support.
 
Last edited by a moderator:

ModServ

Well-Known Member
Oct 17, 2006
332
5
168
Egypt
cPanel Access Level
Root Administrator
Of course you will suffer from high load, your MySQL only uses 5.6GB of ram let alone that you've assigned 21.3GB of ram to MySQL although you don't have this amount.

About ram given to MySQL, you can use this formula to calculate it:

MySQL Server Memory Usage = Sum of Global Buffers + (number of Connection * Per thread memory variables)

Global buffers include:
key_buffer_size
innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_buffer_size
query_cache_size

Per thread variables include:
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
join_buffer_size
thread_stack
net_buffer_length

or you could use MySQL tuning primer to do the heavy lifting for you (calculation and stuff):
https://launchpad.net/mysql-tuning-primer
 

NEK4TE

Member
Aug 8, 2015
10
0
1
Canada
cPanel Access Level
Website Owner
Thanks for responding.

What exactly do you mean by ' your MySQL only uses 5.6GB of ram let alone that you've assigned 21.3GB of ram to MySQL although you don't have this amount ' - i am a bit confused by this, i apologize.

The system has 32GB of RAM, currently there is around 24GB free

Code:
             total       used       free     shared    buffers     cached
Mem:         32007       7846      24161         81         63        712
-/+ buffers/cache:       7070      24936 
Swap:         8189       2336       5853
Tuning primer Output:

Code:
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 21 out of 610200 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

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.6/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 500
Current threads_cached = 13
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 256
Current threads_connected = 5
Historic max_used_connections = 18
The number of used connections is 7% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

INNODB STATUS
Current InnoDB index space = 722 M
Current InnoDB data space = 3.74 G
Current InnoDB buffer pool free = 53 %
Current innodb_buffer_pool_size = 8.00 G
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 : 10.73 G
Configured Max Per-thread Buffers : 10.25 G
Configured Max Global Buffers : 10.01 G
Configured Max Memory Limit : 20.26 G
Physical Memory : 31.25 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 23 M
Current key_buffer_size = 1.00 G
Key cache miss rate is 1 : 278
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 1.00 G
Current query_cache_used = 13 M
Current query_cache_limit = 30 M
Current Query cache Memory fill ratio = 1.36 %
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 = 16 M
Current read_rnd_buffer_size = 768 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 8.00 M
You have had 238 queries where a join could not use an index properly
join_buffer_size >= 4 M
This is not advised
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 = 200266 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 100000 tables
Current table_definition_cache = 32000 tables
You have a total of 667 tables
You have 1249 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 1.00 G
Current tmp_table_size = 1.00 G
Of 131200 temp tables, 5% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 16 M
Current table scan ratio = 802769 : 1
read_buffer_size is over 8 MB there is probably no need for such a large read_buffer

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

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,912
2,241
363
Hello :)

I just wanted to make a quick note that it's import to upload screenshots/images directly to the thread instead of linking to third-party image hosting websites. Feel free to upload the image from your initial post and continue to seek user-feedback on your MySQL configuration.

Thank you.