Announcement

Announcement Module
Collapse
No announcement yet.

A few advices , my.cnf as well

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • A few advices , my.cnf as well

    Currently i have 2 servers in a master->slave configuration.

    The Master is running 8x256GB SSD Disks in Software RAID5 (on debian lenny), on ext3 file system with 32GB RAM.
    The ibdata1 file is 1.2TB in size, and is growing around 3gb per day.

    The Slave is running on 8x500GB 7.2k RPM disks on Hardware RAID10 (LSI SAS 2008), running debian lenny as well on ext3 with 16GB RAM.

    The slave is having hard time catching up, and is really slowing behind, i guess the reason is because the master has SSD disks and the slave not.

    It does look like, there's no concurrency in the slave by just watching iostat.

    Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
    sda 0.50 1967.00 81.50 22.50 8388.00 15916.00 233.69 2.79 26.79 9.44 98.20
    sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda3 0.50 1967.00 81.50 22.50 8388.00 15916.00 233.69 2.79 26.79 9.44 98.20

    /dev/sda3 is the one holding the data and log files.

    (81.50+22.50)*(9.44/1000)= 0.98176

    This is my.cnf file that is on the slave, it's the same as the master, except the master has a buffer pool size of 26G.

    [mysqld]
    skip-name-resolve
    skip-slave-start
    user = mysql
    pid-file = /var/run/mysqld/mysqld.pid
    socket = /var/run/mysqld/mysqld.sock
    port = 3306
    basedir = /usr
    datadir = /mysql-data/mysql
    tmpdir = /tmp
    language = /usr/share/mysql/english
    skip-external-locking
    bind-address = 0.0.0.0
    thread_stack = 192K
    myisam-recover = BACKUP
    general_log_file = /var/log/mysql/mysql.log
    general_log = 0
    log_slow_queries = /var/log/mysql/mysql-slow.log
    server-id = 125
    relay-log = slavedb1-relay-bin
    relay-log-index = slavedb1-relay-bin.index
    innodb_thread_concurrency = 64
    query_cache_type = 0
    query_cache_size = 0M
    innodb_io_capacity = 1000
    sync_binlog=0
    innodb_log_group_home_dir = /log/mysql-misc
    innodb_log_file_size = 512M
    innodb_log_buffer_size = 32M
    innodb_additional_mem_pool_size = 32M
    innodb_support_xa = 0
    skip-innodb_doublewrite
    skip-innodb_checksums
    join_buffer_size = 32M
    sort_buffer_size = 32M
    key_buffer_size = 1M
    transaction_isolation = READ-COMMITTED
    binlog_cache_size = 1M
    key_buffer = 1500M
    max_allowed_packet = 1G
    thread_cache_size = 800
    max_connections = 500
    innodb_buffer_pool_size = 12G
    innodb_flush_log_at_trx_commit = 0
    max_heap_table_size = 64M
    max_prepared_stmt_count = 1000000
    table_cache = 2048
    thread_concurrency = 16
    tmp_table_size = 64M
    read_buffer_size = 10M
    read_rnd_buffer_size = 7680K
    innodb_lock_wait_timeout = 1073741824

    This is the current configuration,we are about the purchase a new server and this is the setup i was thinking about:

    Hardware RAID1 for the root filesystem (2x80GB 7.2k RPM)
    Hardware RAID5, 10x256GB SSD Disks for mysql data only.
    Hardware RAID1, 2x160GB SSD Disks for log files and binary logs.
    The RAID Controller is Adaptec 52445
    This server will have 96GB of RAM.
    All those specs are for the Master, The slave will be the current master i have at the moment. (8x256GB SSD, 32GB RAM)
    New server will run CentOS 5.5 on ext3 and it looks like it will run Percona stable.

    Is there any other configurations i should change in my.cnf except the buffer pool size? any other filesystem i should look into XFS?

    Thanks!

    EDIT: I ran mysqltuner: (that's from the slave)
    >> MySQLTuner 1.1.1 - Major Hayden
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Run with '--help' for additional options and output filtering
    [!!] Successfully authenticated with no password - SECURITY RISK!

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.50-rel11.4-log
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 124M (Tables: 108)
    [--] Data in InnoDB tables: 919G (Tables: 15633)
    [--] Data in MEMORY tables: 0B (Tables: 2)
    [!!] Total fragmented tables: 15633

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 40m 48s (5K q [0.854 qps], 1K conn, TX: 664K, RX: 1B)
    [--] Reads / Writes: 43% / 57%
    [--] Total buffers: 13.6G global + 81.7M per thread (500 max threads)
    [!!] Maximum possible memory usage: 53.5G (340% of installed RAM)
    [OK] Slow queries: 0% (0/5K)
    [OK] Highest usage of available connections: 0% (4/500)
    [OK] Key buffer size / total MyISAM indexes: 1.5G/305.0M
    [!!] Key buffer hit rate: 93.9% (608 cached / 37 reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6 sorts)
    [OK] Temporary tables created on disk: 0% (0 on disk / 1K total)
    [OK] Thread cache hit rate: 99% (4 created / 1K connections)
    [OK] Table cache hit rate: 95% (144 open / 151 opened)
    [OK] Open file limit used: 0% (36/4K)
    [OK] Table locks acquired immediately: 100% (549K immediate / 549K locks)
    [!!] InnoDB data size / buffer pool: 919.6G/12.0G

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (>= 8M)
    innodb_buffer_pool_size (>= 919G)

  • #2
    Your 1.5 GB key_buffer seems a bit high, but the tool also reports that.


    The replication is indeed single-threaded, which you can overcome by spreading writes across your servers and have a master-master set-up.

    Comment

    Working...
    X