Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

A few advices , my.cnf as well

Infin1tyInfin1ty EntrantInactive User Role Beginner
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 <[EMAIL="major&#64;mhtx.net"][email protected][/EMAIL]>
>> 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)

Comments

  • gmousegmouse Mod Squad Inactive User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.