# Best 'Very-Heavy-write' DB config? **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [January 19, 2008, 7:24am UTC](https://forums.percona.com/t/best-very-heavy-write-db-config/603 "2008-01-19T07:24:34Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![nickh](https://avatars.discourse-cdn.com/v4/letter/n/7ea924/32.png) [@nickh](https://forums.percona.com/u/nickh)\ **Post date:** [January 19, 2008, 7:24am UTC](https://forums.percona.com/t/best-very-heavy-write-db-config/603/1 "2008-01-19T07:24:34Z")
Hi, Were building an online chat-style application, which has already been through a closed-beta using a mostly myisam tables and a few innodb set up. Weve got a pretty heavy write-read ratio - the following is output from mysqlreport: \_\_ Questions \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\__Total 6.71M 1.2/s DMS 3.01M 0.5/s %Total: 44.89 Com 1.62M 0.3/s 24.17 QC Hits 1.05M 0.2/s 15.61 COM\_QUIT 1.02M 0.2/s 15.15 +Unknown 12.66k 0.0/s 0.19Slow 26 0.0/s 0.00 %DMS: 0.00DMS 3.01M 0.5/s 44.89 INSERT 1.63M 0.3/s 24.34 54.23 SELECT 1.05M 0.2/s 15.70 34.99 UPDATE 301.12k 0.1/s 4.48 9.99 DELETE 23.91k 0.0/s 0.36 0.79 REPLACE 0 0/s 0.00 0.00Com_ 1.62M 0.3/s 24.17 show\_status 769.19k 0.1/s 11.46 show\_innodb 755.78k 0.1/s 11.26 change\_db 27.15k 0.0/s 0.40 Its turned out to be very heavy (certainly the heaviest I have ever seen) on the WRITE side because _ALL_ chat messages are logged/stored for audit purposes. While Im not uncomfortable with this ratio at the moment it will have profound consequences at a later date, when the site gets very heavy traffic. As a result, I can already smell slaves failing to keep up with the master, which has lead us to consider sharding the logs off into their own DB - just a DB taking inserts. My question to all is, what is the best/most-efficient way of storing _just logs_ DB-wise?? we could keep them as part of a larger central DB, but Im not convinced this is the best option at all. My thoughts to date have been: - Separate DB - InnoDB table types - no complex keys - Much RAM - RAID10 disks Can anyone offer any advice/deeper thinking?? Thanks Nick. ---
**Author:** ![chriswest](https://avatars.discourse-cdn.com/v4/letter/c/b9bd4f/32.png) [@chriswest](https://forums.percona.com/u/chriswest)\ **Post date:** [January 21, 2008, 4:20pm UTC](https://forums.percona.com/t/best-very-heavy-write-db-config/603/2 "2008-01-21T16:20:48Z")
Hi, I would suggest the following: - either write the log data to files (skipping mysql completely) and use some disk replication like DRBD (or a cronjob with rsync if some data may get lost ;)) - use the archive storage engine: [http://dev.mysql.com/doc/refman/5.0/en/archive-storage-engin](http://dev.mysql.com/doc/refman/5.0/en/archive-storage-engin) e.html We have a high write / read ratio on our dual master replication: \_\_ Questions \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_Total 4.83G 5925.8/sSlow 7.19k 0.01/s %Total: 0.00 %DMS: 0.00DMS 2.76G 3388.0/s 57.17 UPDATE 2.24G 2746.6/s 46.35 81.07 INSERT 324.83M 398.32/s 6.72 11.76 SELECT 131.57M 161.34/s 2.72 4.76 REPLACE 66.48M 81.52/s 1.38 2.41 DELETE 171.68k 0.21/s 0.00 0.01 - we use innodb only for tables which are mostely INSERTED / UPDATED - the following config part might be useful (needs some tweaking for your server of course), mysql 5.0, 16 GB RAM and 2 x QuadCore innodb\_data\_home\_dir = /var/lib/mysqlinnodb\_data\_file\_path = ibdata1:40Ginnodb\_log\_file\_size = 1900Minnodb\_log\_buffer\_size = 8Minnodb\_buffer\_pool\_size = 11000Minnodb\_additional\_mem\_pool\_size = 16Minnodb\_file\_io\_threads = 4innodb\_lock\_wait\_timeout = 50innodb\_flush\_log\_at\_trx\_commit = 0innodb\_flush\_method = O\_DIRECTinnodb\_file\_per\_table = 1innodb\_thread\_concurrency = 20innodb\_open\_files = 4096innodb\_doublewrite = 0