Hello Spamassassins! ;) A few weeks ago I had problems with a capacity of my MySQL 5.0.38 server with sa_bayes database stored in MyISAM when it was handling a lot of SQL queries from my Spamassassin cluster. The only one solution was to disable using Bayes.
I wrote about my problems here and I heard many useful advices. One of them was to convert my sa_bayes database from MyISAM to InnoDB storage engine. I didn't have any experiences with InnoDB, so I had to learn it. Now I know more about it, but I still have a few doubts... Below you can see details about a copy of my old sa_bayes database with MyISAM: # ls -lh sa_bayes/ total 809M -rw-r----- 1 ptecza ptecza 8,5K 2007-08-16 15:29 awl.frm -rw-r----- 1 ptecza ptecza 139M 2007-08-16 15:29 awl.MYD -rw-r----- 1 ptecza ptecza 112M 2007-08-16 15:29 awl.MYI -rw-r----- 1 ptecza ptecza 8,4K 2007-08-16 15:29 bayes_expire.frm -rw-r----- 1 ptecza ptecza 207 2007-08-16 15:29 bayes_expire.MYD -rw-r----- 1 ptecza ptecza 2,0K 2007-08-16 15:29 bayes_expire.MYI -rw-r----- 1 ptecza ptecza 8,4K 2007-08-16 15:29 bayes_global_vars.frm -rw-r----- 1 ptecza ptecza 20 2007-08-16 15:29 bayes_global_vars.MYD -rw-r----- 1 ptecza ptecza 2,0K 2007-08-16 15:29 bayes_global_vars.MYI -rw-r----- 1 ptecza ptecza 8,5K 2007-08-16 15:29 bayes_seen.frm -rw-r----- 1 ptecza ptecza 213M 2007-08-16 15:29 bayes_seen.MYD -rw-r----- 1 ptecza ptecza 278M 2007-08-16 15:29 bayes_seen.MYI -rw-r----- 1 ptecza ptecza 8,5K 2007-08-16 15:29 bayes_token.frm -rw-r----- 1 ptecza ptecza 24M 2007-08-16 15:29 bayes_token.MYD -rw-r----- 1 ptecza ptecza 44M 2007-08-16 15:29 bayes_token.MYI -rw-r----- 1 ptecza ptecza 8,8K 2007-08-16 15:29 bayes_vars.frm -rw-r----- 1 ptecza ptecza 52 2007-08-16 15:29 bayes_vars.MYD -rw-r----- 1 ptecza ptecza 3,0K 2007-08-16 15:29 bayes_vars.MYI -rw-r----- 1 ptecza ptecza 65 2007-08-16 15:29 db.opt Here are details about a new sa_bayes database with InnoDB: ls -lh ib* -rw-rw---- 1 mysql mysql 10M Aug 28 08:25 ib_logfile0 -rw-rw---- 1 mysql mysql 10M Aug 27 21:42 ib_logfile1 -rw-rw---- 1 mysql mysql 1010M Aug 28 08:25 ibdata1 # ls -lh sa_bayes/ total 882M -rw-rw---- 1 mysql mysql 8.5K Aug 27 15:28 awl.frm -rw-rw---- 1 mysql mysql 264M Aug 27 17:09 awl.ibd -rw-rw---- 1 mysql mysql 8.4K Aug 27 17:08 bayes_expire.frm -rw-rw---- 1 mysql mysql 112K Aug 28 08:25 bayes_expire.ibd -rw-rw---- 1 mysql mysql 8.4K Aug 27 17:08 bayes_global_vars.frm -rw-rw---- 1 mysql mysql 96K Aug 27 17:09 bayes_global_vars.ibd -rw-rw---- 1 mysql mysql 8.5K Aug 27 17:08 bayes_seen.frm -rw-rw---- 1 mysql mysql 468M Aug 27 21:11 bayes_seen.ibd -rw-rw---- 1 mysql mysql 8.5K Aug 27 21:09 bayes_token.frm -rw-rw---- 1 mysql mysql 148M Aug 27 21:43 bayes_token.ibd -rw-rw---- 1 mysql mysql 8.8K Aug 27 21:42 bayes_vars.frm -rw-rw---- 1 mysql mysql 112K Aug 28 08:25 bayes_vars.ibd -rw-rw---- 1 mysql mysql 65 Aug 27 15:23 db.opt It has exactly the same content like old database and it was simply injected from MySQL dump. As you can see above, the new storage engine consumed 2 times bigger diskspace then the old. Is it a good behave or I should feel worried? Could you please tell me what the size of your sa_bayes database with InnoDB is? What diskspace should I reserve? Probably you would like to know my InnoDB settings too: # grep ^innodb /etc/mysql/my.cnf innodb_data_file_path=ibdata1:10M:autoextend innodb_autoextend_increment=10M innodb_file_per_table innodb_buffer_pool_size=60M innodb_additional_mem_pool_size=5M innodb_log_files_in_group=2 innodb_fast_shutdown=1 innodb_log_file_size=10M innodb_log_buffer_size=5M innodb_flush_log_at_trx_commit=1 innodb_lock_wait_timeout=25 I agree that a size of buffers is not too big, but it's only my testing box, not a production machine. My best regards, Pawel
