From: Junior Ortis (jrortisgmail.com)
Date: Sat Apr 10 2010 - 02:10:24 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hi Guys i have a dedicated mysql-server and neeed tips and sugestion
to optimize its to a better performance.

1-) Here i have results from mysqltunner

>> MySQLTuner 1.0.1 - Major Hayden <majormhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: toscaoSo
Please enter your MySQL administrative password:

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

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 29d 12h 8m 1s (334M q [131.330 qps], 153K conn, TX: 194B, RX: 77B)
[--] Reads / Writes: 31% / 69%
[--] Total buffers: 15.5G global + 16.2M per thread (50 max threads)
[OK] Maximum possible memory usage: 16.3G (69% of installed RAM)
[OK] Slow queries: 0% (386/334M)
[OK] Highest usage of available connections: 46% (23/50)
[OK] Key buffer size / total MyISAM indexes: 300.0M/87.3M
[OK] Key buffer hit rate: 100.0% (78M cached / 22K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4M sorts)
[OK] Temporary tables created on disk: 0% (548 on disk / 1M total)
[OK] Thread cache hit rate: 99% (23 created / 153K connections)
[OK] Table cache hit rate: 44% (467 open / 1K opened)
[OK] Open file limit used: 1% (684/65K)
[OK] Table locks acquired immediately: 99% (320M immediate / 320M locks)
[!!] InnoDB data size / buffer pool: 15.5G/15.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
Variables to adjust:
    query_cache_size (>= 8M)
    innodb_buffer_pool_size (>= 15G)

2-) And here is my dedicate server i have (24GB ):

  1 [
         0.0%] Tasks: 71 total, 2 running
  2 [|||||||
         7.8%] Load average: 0.11 0.18 0.19
  3 [|
         0.7%] Uptime: 62 days, 19:24:09
  4 [|
         0.7%]
  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||16878/24165MB]
  Swp[|
     0/5122MB]

3-) And my.cnf

vim .my.cnf
[client]
#password = [your_password]
port = 3306
socket = /tmp/mysql.sock

# *** Application-specific options follow here ***

#
# The MySQL server
#
[mysqld]
#large-pages

# generic configuration options
port = 3306
socket = /tmp/mysql.sock
skip-locking
skip-external-locking
datadir = /disk3/Datareal/oficial/mysql
net_buffer_length = 1024K
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
table_cache = 1000
max_allowed_packet = 160M

max_connections=50
max_user_connections=200

key_buffer = 300M
key_buffer_size = 300M
#thread_cache = 400
thread_stack = 192K
thread_cache_size = 96
thread_concurrency = 8
#thread_stack = 128K

default-character-set = utf8
innodb_flush_method=O_DSYNC
innodb_buffer_pool_size= 15G
innodb_additional_mem_pool_size=128M
innodb_log_file_size= 256M
innodb_log_buffer_size=72M
innodb_flush_log_at_trx_commit=0
innodb_thread_concurrency=8
innodb_file_per_table=1
innodb_log_files_in_group=2
innodb_table_locks=0
innodb_lock_wait_timeout = 50

"~/.my.cnf" 72L, 1570C

Thanks guys for any tips/suggestion !

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]