MySQL Optimization - Dedicated Server

NEK4TE

• August 08, 2015 21:56

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
>> MySQLTuner 1.4.0 - Major Hayden >> 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
[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"?
[!!] Maximum possible memory usage: 21.3G (680% of installed RAM)

/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
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
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.

• 

  ModServ

  • August 09, 2015 09:54

  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):

  0
• 

  NEK4TE

  • August 09, 2015 15:49

  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
  total used free shared buffers cached Mem: 32007 7846 24161 81 63 712 -/+ buffers/cache: 7070 24936 Swap: 8189 2336 5853
  Tuning primer Output:
  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

  0
• 

  cPanelMichael

  • August 10, 2015 14:23

  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.

  0
• 

  NEK4TE

  • August 10, 2015 14:39

  Hello Michael, I apologize for that. I will be waiting (for hopefully) more suggestions about this. Regards

  0

Please sign in to leave a comment.