Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > Server Operation

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 15th February 2011, 09:56
Dimass Dimass is offline
Junior Member
 
Join Date: Feb 2011
Location: Girona, Catalonia
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Question Tunning MySQL (mysqltuner.pl)

Hi,

I want to optimize a MySQL server with 2CPUs and 4Gb of RAM, shared with a Apache service.

The MySQL have abut 1500 tables (10% InnoDB).

The my.cnf settings:
Code:
innodb_buffer_pool_size = 64M
join_buffer_size        = 512K
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
#max_connections        = 100
table_cache            = 200
#thread_concurrency     = 10
query_cache_limit       = 1M
query_cache_size        = 32M
query_cache_type        = 1


The mysqltuner.pl script output this:

Code:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-1ubuntu8.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 200M (Tables: 1294)
[--] Data in InnoDB tables: 35M (Tables: 194)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 207

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4m 27s (30K q [114.536 qps], 304 conn, TX: 232M, RX: 5M)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 130.0M global + 3.1M per thread (151 max threads)
[OK] Maximum possible memory usage: 592.4M (14% of installed RAM)
[OK] Slow queries: 0% (169/30K)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/62.4M
[OK] Key buffer hit rate: 98.7% (765K cached / 9K reads)
[OK] Query cache efficiency: 93.6% (26K cached / 28K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 214 sorts)
[!!] Joins performed without indexes: 2
[OK] Temporary tables created on disk: 23% (67 on disk / 283 total)
[OK] Thread cache hit rate: 96% (12 created / 304 connections)
[!!] Table cache hit rate: 2% (200 open / 9K opened)
[OK] Open file limit used: 37% (385/1K)
[OK] Table locks acquired immediately: 100% (2K immediate / 2K locks)
[OK] InnoDB data size / buffer pool: 35.5M/64.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with joins)
    table_cache (> 200)
I've incremented join_buffer_size and table_cache but keep throwing these advices. I don't like the "Table cache hit rate: 2% (200 open / 9K opened)".

Any advice to optimize the MySQl config?

Thx
Reply With Quote
Sponsored Links
  #2  
Old 16th February 2011, 15:59
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,741 Times in 2,575 Posts
Default

Did you restart MySQL after your changes?
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #3  
Old 16th February 2011, 16:25
Dimass Dimass is offline
Junior Member
 
Join Date: Feb 2011
Location: Girona, Catalonia
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
 
Default

Every time...
Reply With Quote
Reply

Bookmarks

Tags
mysql tuning

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
CPU load locks up box. Apache or MYSQL related. crypted General 61 29th October 2010 22:16
Squirrelmail login failure Cracklefish Installation/Configuration 9 30th June 2010 20:38
ISPConfig3 Mail Warn Errors reason8 General 3 25th November 2009 13:58
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 09:11
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 16:04


All times are GMT +2. The time now is 21:02.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.