Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to tweak my server's my.cnf file because I have been receiving numerous "too many connection" warnings when doing database back ups among other things. I know I also need to tweak other aspects of my server as well.

Here is my my.cnf

[mysqld]
#bind-address=127.0.0.1
#skip-bdb
local-infile=0
max_connections=90
open_files_limit=2050
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
query_cache_limit = 12M
query_cache_size = 55M
join_buffer_size = 512K
read_buffer_size = 128K
tmp_table_size = 3024M
thread_cache_size = 4
table_cache = 6024
innodb_buffer_pool_size = 540M
key_buffer_size = 1024M
table_definition_cache = 4024
max_allowed_packet = 35M
max_heap_table_size = 3024M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Here is my basic server specs--

AMD Opteron™ 4274

8 Cores x 2.5 GHz (3.5 Turbo Core)

16 GB DDR3 ECC

Running Cent os and the latest Plesk Panel

Currently this server has 1 fairly busy Vbulletin site, 1 fairly small Vbulletin site and 4 other very small and not busy WordPress sites

Updated

[mysqld]
#bind-address=127.0.0.1
#skip-bdb
local-infile=0
max_connections=500
open_files_limit=9975
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
query_cache_limit = 12M
query_cache_size = 55M
join_buffer_size = 512K
read_buffer_size = 128K
thread_cache_size = 50
table_cache = 3024
innodb_buffer_pool_size = 6G
key_buffer_size = 32M
table_definition_cache = 4096
max_allowed_packet = 40M
tmp-table-size = 32M
max-heap-table-size = 32M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Here is mysql tuner output

 >>  MySQLTuner 1.2.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

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.69
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 3307)
[--] Data in InnoDB tables: 111M (Tables: 487)
[--] Data in MEMORY tables: 248K (Tables: 329)
[!!] Total fragmented tables: 653

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2m 30s (3K q [25.700 qps], 166 conn, TX: 106M, RX: 1M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 6.1G global + 3.1M per thread (500 max threads)
[OK] Maximum possible memory usage: 7.6G (48% of installed RAM)
[OK] Slow queries: 0% (0/3K)
[OK] Highest usage of available connections: 0% (4/500)
[OK] Key buffer size / total MyISAM indexes: 32.0M/526.3M
[OK] Key buffer hit rate: 98.3% (102K cached / 1K reads)
[OK] Query cache efficiency: 68.8% (2K cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 171 sorts)
[!!] Joins performed without indexes: 8
[!!] Temporary tables created on disk: 28% (33 on disk / 114 total)
[OK] Thread cache hit rate: 97% (4 created / 166 connections)
[OK] Table cache hit rate: 96% (182 open / 189 opened)
[OK] Open file limit used: 3% (337/9K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB data size / buffer pool: 111.6M/6.0G

-------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    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
Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)

Keep in mind I just ran this script after installing. It should probably run for 24 hours before a real output..

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.