Best 'Very-Heavy-write' DB config?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Best 'Very-Heavy-write' DB config?


    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??


  • #2

    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 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