Best 'Very-Heavy-write' DB config?

MySQL & MariaDB Other MySQL® Questions
1

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.

2

Hi,

I would suggest the following:

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