List:General Discussion« Previous MessageNext Message »
From:Junior Ortis Date:April 10 2010 7:10am
Subject:InnoDB - 16GB Data
View as plain text  
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 <major@stripped>
 >>  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 !
Thread
InnoDB - 16GB DataJunior Ortis10 Apr
  • Re: InnoDB - 16GB DataRob Wultsch11 Apr
    • Re: InnoDB - 16GB DataKyong Kim14 Apr