# A few advices , my.cnf as well **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [October 13, 2010, 6:41am UTC](https://forums.percona.com/t/a-few-advices-my-cnf-as-well/1530 "2010-10-13T06:41:59Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![Infin1ty](https://avatars.discourse-cdn.com/v4/letter/i/ecae2f/32.png) [@Infin1ty](https://forums.percona.com/u/Infin1ty)\ **Post date:** [October 13, 2010, 6:41am UTC](https://forums.percona.com/t/a-few-advices-my-cnf-as-well/1530/1 "2010-10-13T06:41:59Z")
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 \<[major@mhtx.net](mailto:major@mhtx.net)\> > > Bug reports, feature requests, and downloads at [http://mysqltuner.com/](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) ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [October 16, 2010, 6:54am UTC](https://forums.percona.com/t/a-few-advices-my-cnf-as-well/1530/2 "2010-10-16T06:54:54Z")
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.