homepage Welcome to WebmasterWorld Guest from 54.237.213.31
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Best mysql my.cnf setup for my site
Best mysql my.cnf setup for my site
hp_solomon

5+ Year Member



 
Msg#: 4515796 posted 8:45 am on Nov 5, 2012 (gmt 0)

Hi,

I need help on optimizing my.cnf of my site. my site is so slow, and seems response and connection is too long or slow.

website is a phpfox website with database members of 80,000+.

Logged-in Online users: 50+, + bots hundreds to thousands.

here's my server setup.
8GB DDR ECC RAM
CPanel/whm
Dual Xeon E5335 Quad Core Clovertown
4 x 120GB SATA Raid 5
Mysql version 5.0


mysqltuner.pl results

>> MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.96-community-log
[!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 1218)
[--] Data in InnoDB tables: 47M (Tables: 27)
[!] Total fragmented tables: 15

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12m 35s (35K q [46.934 qps], 3K conn, TX: 1B, RX: 7M)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 2.1G global + 2.7M per thread (3000 max threads)
[!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!] Maximum possible memory usage: 9.9G (125% of installed RAM)
[OK] Slow queries: 0% (328/35K)
[OK] Highest usage of available connections: 6% (200/3000)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.4G
[OK] Key buffer hit rate: 98.5% (8M cached / 131K reads)
[OK] Query cache efficiency: 37.7% (9K cached / 24K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 3% (185 temp sorts / 4K sorts)
[!] Joins performed without indexes: 895
[!] Temporary tables created on disk: 29% (1K on disk / 5K total)
[OK] Thread cache hit rate: 93% (200 created / 3K connections)
[!] Table cache hit rate: 3% (782 open / 20K opened)
[OK] Open file limit used: 3% (589/15K)
[OK] Table locks acquired immediately: 99% (48K immediate / 48K locks)
[OK] InnoDB data size / buffer pool: 47.0M/2.0G

-------- 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
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 64)


current my.cnf configurations:
[mysqld]
#bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql


innodb_buffer_pool_size=2G
innodb_additional_mem_pool_size=20M


query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=3000
max_user_connections=600
interactive_timeout=100
wait_timeout=100
connect_timeout=20
thread_cache_size=128


log-bin=/var/log/mysql/bin.log
#log-bin-index=/var/log/mysql/log-bin.index
log-bin-index = /var/log/mysql/log-bin.index
log-error=/var/log/mysql/error.log
relay-log=/var/log/mysql/relay.log
relay-log-info-file=/var/log/mysql/relay-log.info
relay-log-index=/var/log/mysql/relay-log.index
binlog-do-db=rakista_radio
log_slow_queries = /var/log/mysql/mysql-slow.log




[mysqld_safe]
#log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
key_buffer=256M
# 64M for 1GB, 128M for 2GB, 256 for 4GB

join_buffer_size=4M
# 1M for 1GB, 2M for 2GB, 4M for 4GB

max_allowed_packet=32M
table_cache=1024
sort_buffer_size=4M
# 1M for 1GB, 2M for 2GB, 4M for 4GB

read_buffer_size=4M
# 1M for 1GB, 2M for 2GB, 4Mfor 4GB

read_rnd_buffer_size=3072K
# 768K for 1GB, 1536K for 2GB, 3072Kfor 4GB

max_connect_errors=10
thread_concurrency=4
myisam_sort_buffer_size=128M
# 32M for 1GB, 64M for 2GB, 128 for 4GB

skip-locking
server-id=1

[safe_mysqld]
open_files_limit=8192

[isamchk]
key_buffer=256M
# 64M for 1GB, 128M for 2GB, 256M for 4GB

sort_buffer=256M
# 64M for 1GB, 128M for 2GB, 256M for 4GB

read_buffer=64M
# 16M for 1GB, 32M for 2GB, 64M for 4GB

write_buffer=64M
# 16M for 1GB, 32M for 2GB,64M for 4GB

[myisamchk]
key_buffer=256M
# 64M for 1GB, 128M for 2GB, 256M for 4GB

sort_buffer=256M
# 64M for 1GB, 128M for 2GB, 256M for 4GB

read_buffer=64M
# 16M for 1GB, 32M for 2GB, 64M for 4GB

write_buffer=64M
# 16M for 1GB, 32M for 2GB, 64M for 4GB







Please help me. Thanks in advance.

 

phranque

WebmasterWorld Administrator phranque us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



 
Msg#: 4515796 posted 10:18 am on Nov 5, 2012 (gmt 0)

Up for: 12m 35s

your server was only running for 12 minutes and that's probably not long enough to be statistically significant for your typical server load.

if your server is slow due to the database this is the first place i would look:
Adjust your join queries to always utilize indexes

hp_solomon

5+ Year Member



 
Msg#: 4515796 posted 10:57 am on Nov 5, 2012 (gmt 0)

i restarted the server when i generated the report. that's the 12 minutes after..

any advice on the my.cnf based on my server setup and report?

phranque

WebmasterWorld Administrator phranque us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



 
Msg#: 4515796 posted 11:14 am on Nov 5, 2012 (gmt 0)

right now you are using unnecessary resources because your queries are inefficient.

fix your queries and/or your indexes first.

hp_solomon

5+ Year Member



 
Msg#: 4515796 posted 11:51 am on Nov 5, 2012 (gmt 0)

hello,

i already have indexes on my tables. it is the default phpfox tables running on most of their bigger clients. enabled mysqlquery log and right now it is empty.

Any help greatly appreciated. I'm not an expert in mysql optimization.

thanks.

brotherhood of LAN

WebmasterWorld Administrator brotherhood_of_lan us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



 
Msg#: 4515796 posted 11:54 am on Nov 5, 2012 (gmt 0)

Optimising my.cnf depends a lot on whether you are primarily using InnoDB, MyISAM or a mix of these tables. It looks like you have allocated a sizeable amount of memory for both. Is that intended?

To follow on phraque's advice, inevitably it is one or more queries that are likely causing a slowness due to the nature of them (or the tables), as much as it can just be sheer volume of queries. You have a slow query log and a mysql log that can give you hints where things are going wrong.

Frank_Rizzo

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4515796 posted 1:07 pm on Nov 5, 2012 (gmt 0)

RAM
First put more RAM in there. RAM prices are dirt cheap and that will give you the biggest instant boost in performance.

RAM is not just for mysql but for the webserver and file caching. Up it to 16GB or even 12GB and you will notice a difference.

But it looks as if you are runing 32-bit MySQL. This could be a problem. Check with your hoster what type of O/S you have.

MySQL
[--] Data in MyISAM tables: 3G (Tables: 1218)
[--] Data in InnoDB tables: 47M (Tables: 27)

Do you really need InnoDB? What are the 27 tables that are using it? If this is just the default MySQL install with the performance_schema, test tables etc then convert them to myiasm and disable innodb om startup.


[!] Allocating > 2GB RAM on 32-bit systems can cause system instability

That's what I mean about the 32-bit / more RAM

[OK] Highest usage of available connections: 6% (200/3000)

Do you ever hit 3000 connections? If not lower it.

[OK] Key buffer size / total MyISAM indexes: 8.0M/1.4G

That is so wrong! The Key buffer size should match the total MyISAM indexes. Ideally you need to make this 1.4G.

Other Tweaks
Are you using swap memory?

Can you tune the webserver memory?

Is the webserver logging all hits? Do you really need to log images, css and javascript accesses?

General tweaks and optimisations for the O/S? e.g. set noatime on the partitions

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved