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