?
Solved

mysqltuner.sh results help

Posted on 2014-12-04
4
Medium Priority
?
236 Views
Last Modified: 2014-12-12
Hello,

Below are the results for my tuner script, I tried optimizing the tables but it just said that my innodb tables could not be updated.

Also can you please suggest the changes to the my.cnf.

Thank you for your time!

 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  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.1.73-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 12M (Tables: 168)
[--] Data in InnoDB tables: 4G (Tables: 2163)
[--] Data in MEMORY tables: 1M (Tables: 86)
[!!] Total fragmented tables: 2168

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 19h 23m 58s (43M q [179.459 qps], 414K conn, TX: 79B, RX: 16B)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 8.6G global + 34.2M per thread (512 max threads)
[OK] Maximum possible memory usage: 25.7G (40% of installed RAM)
[OK] Slow queries: 0% (74/43M)
[OK] Highest usage of available connections: 85% (436/512)
[OK] Key buffer size / total MyISAM indexes: 16.0M/3.4M
[OK] Key buffer hit rate: 100.0% (35M cached / 840 reads)
[OK] Query cache efficiency: 94.7% (36M cached / 38M selects)
[!!] Query cache prunes per day: 73060
[OK] Sorts requiring temporary tables: 1% (5K temp sorts / 462K sorts)
[!!] Joins performed without indexes: 2868
[!!] Temporary tables created on disk: 29% (488K on disk / 1M total)
[OK] Thread cache hit rate: 97% (10K created / 414K connections)
[!!] Table cache hit rate: 0% (256 open / 43K opened)
[OK] Open file limit used: 0% (9/2K)
[OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)
[OK] InnoDB buffer pool / data size: 8.0G/4.5G
[!!] InnoDB log waits: 3
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_size (> 512M) [see warning above]
    join_buffer_size (> 16.0M, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    table_open_cache (> 256)
    innodb_log_buffer_size (>= 8M)

Open in new window


[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
slow-query-log=/var/log/mysqld-slow.log

max_connections = 512

key_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
query_cache_size = 512M
query_cache_limit = 2M
thread_cache_size = 8
read_buffer_size = 8M
join_buffer_size = 16M
read_rnd_buffer_size = 8M
slow_query_log = 1
long_query_time = 2
thread_concurrency = 8
table_cache = 128
table_open_cache = 256
table_definition_cache = 512
max_heap_table_size = 32M
tmp_table_size = 32M

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 8G
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 8M
#innodb_log_file_size = 512M
#innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

max_allowed_packet=16M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Open in new window

0
Comment
Question by:movieprodw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 2000 total points
ID: 40480888
Hi!

You could start with these values
query_cache_size = 1024M
    join_buffer_size = 32M
    tmp_table_size  = 128M
    max_heap_table_size  = 128M
    table_open_cache = 1024
    innodb_log_buffer_size = 32M

Regards,
     Tomas Helgi
0
 
LVL 1

Author Comment

by:movieprodw
ID: 40481283
I have made those changes, thank you.
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40485823
Hi!

If you are satisfied with those settings that I provided then
please accept my comment as an answer. :)

Regards,
     Tomas Helgi
0
 
LVL 1

Author Closing Comment

by:movieprodw
ID: 40496733
Thanks
0

Featured Post

Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question