We found that lock contention and database size impacted performance once we had over 3-4k users per database instance. We split our user base into multiple MySQL instances, in our case by backend server. In other words, 10k users into 1 DB instance performed worse that 2 5k user DB instances on the same hardware. We could not get the InnoDB solution to scale well enough at the time we went production so we went with MyISAM DB instances. We anticipate moving to PostgreSQL 8.3 with the new HOT update functionality to give use the single instance performance that we need as well as replication to a fail-over DB. We tried the MySQL replication and it was not stable enough with the loads and DB sizes that the DSPAM instance incurred. We give every user their own training corpus. If you can use shared training and groups, you can dramatically cut you DB size. If you can get your working set size < memory you should be fine. Let me know if you have any further questions.
Regards, Ken On Wed, Nov 07, 2007 at 06:27:42PM -0300, ?talo Rossi wrote: > Ken, > > We already use greylisting and rbls, my max message size is set to 2MB. > > Recently we have disabled the shared group for all users, I'll activate > again... > > Can you explain how to partition users between myisam dbs? > > On Nov 7, 2007, at 5:56 PM, Kenneth Marshall wrote: > >> M. Rossi, >> >> It sounds like your DSPAM cannot keep up with your mail >> volume in your current configuration. We had to actually >> partition users between multiple MYISAM db instances to >> support out load. You could try reducing the max message >> size that will be processed to reduce the I/O as well as >> to make use of shared training groups to reduce your DB >> size. With a 2GB machine and a mediocre I/O subsystem, >> you will need to do something similar to keep mail from >> backing up. We also increased our level of anti-spam >> preprocessing to reduce the total load on the DSPAM >> system (greylisting and RBLs using policyd_weight). >> >> Good luck. >> >> Ken >> On Wed, Nov 07, 2007 at 05:37:24PM -0300, ?talo Rossi wrote: >>> We have more than 20000 active users, the postfix queue keeps >>> increasing.. >>> >>> My server is 4 Intel(R) Xeon(TM) CPU 3.00GHz with 2GB RAM >>> >>> SCSI hds RAID0 (hardware) and RAID1 (software). >>> >>> Can I change anything ? >>> >>> postfix queue TOTAL 148 and server load average: 3.60 . >>> >>> >>> My database (innodb) - >>> -rw-rw---- 1 mysql mysql 65 2007-11-06 00:20 db.opt >>> -rw-rw---- 1 mysql mysql 8.5K 2007-11-07 17:20 dspam_preferences.frm >>> -rw-rw---- 1 mysql mysql 96K 2007-11-06 00:25 dspam_preferences.ibd >>> -rw-rw---- 1 mysql mysql 8.5K 2007-11-07 17:20 dspam_signature_data.frm >>> -rw-rw---- 1 mysql mysql 1.3G 2007-11-07 17:28 dspam_signature_data.ibd >>> -rw-rw---- 1 mysql mysql 8.8K 2007-11-07 17:20 dspam_stats.frm >>> -rw-rw---- 1 mysql mysql 9.0M 2007-11-07 17:28 dspam_stats.ibd >>> -rw-rw---- 1 mysql mysql 8.6K 2007-11-07 17:20 dspam_token_data.frm >>> -rw-rw---- 1 mysql mysql 16G 2007-11-07 17:28 dspam_token_data.ibd >>> -rw-rw---- 1 mysql mysql 8.4K 2007-11-07 17:20 dspam_virtual_uids.frm >>> -rw-rw---- 1 mysql mysql 15M 2007-11-07 16:56 dspam_virtual_uids.ibd >>> >>> my.cnf: >>> >>> [mysqld] >>> user = mysql >>> pid-file = /var/run/mysqld/mysqld.pid >>> socket = /var/run/mysqld/mysqld.sock >>> port = 3306 >>> basedir = /usr >>> datadir = /var/lib/mysql >>> tmpdir = /tmp >>> language = /usr/share/mysql/english >>> innodb_file_per_table=1 >>> read_rnd_buffer_size=4M >>> read_buffer_size=2M >>> sort_buffer_size=32M >>> join_buffer_size=1M >>> key_buffer=256M >>> key_buffer_size = 256M >>> max_allowed_packet = 32M >>> thread_stack = 128K >>> thread_cache_size = 8 >>> table_cache = 1024 >>> query_cache_limit = 2M >>> query_cache_size = 128M >>> skip-bdb >>> [mysqld] >>> innodb_flush_log_at_trx_commit=2 >>> innodb_buffer_pool_size = 1600M >>> innodb_thread_concurrency=8 >>> innodb_flush_method=O_DSYNC >>> innodb_buffer_pool_size=70M >>> innodb_log_file_size=20M >>> innodb_log_buffer_size=8M >>> innodb_data_home_dir = /var/lib/mysql/ >>> innodb_data_file_path = ibdata1:800M:autoextend >>> set-variable = innodb_additional_mem_pool_size=10M >>> >>> >>> On Nov 6, 2007, at 2:52 AM, Tony Earnshaw wrote: >>> >>>> ?talo Rossi skrev, on 05-11-2007 16:35: >>>>> Tonni, >>>>> Can you post your mysql configuration? >>>>> My ibdata1 is too big: >>>>> -rw-rw---- 1 mysql mysql 64G 2007-11-05 12:32 ibdata1 >>>>> -rw-rw---- 1 mysql mysql 800M 2007-10-28 14:24 ibdata2 >>>>> 64GB it's bigger than my mysql tables... >>>> >>>> The site that runs the following is a high school with 1150+ users >>>> whereof >>>> around 350 active email users. It's a new install on RHEL5 x86_32 with >>>> all >>>> the databases populated from MySQL dumps on the old RHAS4 server. The >>>> old >>>> server had an ibdata1 of 16GB that I could never get to shrink in size. >>>> The only way of shrinking the size I can think of is converting every >>>> database to MyISAM and optimizing every table, then converting back to >>>> InnoDB - that's a horrible job on a running production server, so I >>>> never >>>> tried, just waited for the upgrade - more than 2 years.. >>>> >>>> I originally had dspam configured for each user, that resulted in the >>>> 16GB; half way through I swapped to a shared group. >>>> >>>> 1033 [root:mercurius.intern] /etc # du -sh /var/lib/mysql/* >>>> 96K /var/lib/mysql/dspamdb >>>> 21M /var/lib/mysql/ib_logfile0 >>>> 21M /var/lib/mysql/ib_logfile1 >>>> 101M /var/lib/mysql/ibdata1 >>>> 16K /var/lib/mysql/log.0000000001 >>>> 888K /var/lib/mysql/mysql >>>> 0 /var/lib/mysql/mysql.sock >>>> 28M /var/lib/mysql/policyd >>>> 64K /var/lib/mysql/squirrelmail >>>> 8.0K /var/lib/mysql/test >>>> >>>> [mysqld] >>>> socket=/var/lib/mysql/mysql.sock >>>> datadir=/var/lib/mysql >>>> old_passwords=1 >>>> default-table-type=innodb >>>> innodb_data_file_path=ibdata1:100M:autoextend >>>> key_buffer = 16M >>>> max_allowed_packet = 1M >>>> table_cache = 64 >>>> sort_buffer_size = 32M >>>> net_buffer_length = 8K >>>> max_allowed_packet=4096000 >>>> max_connections=500 >>>> skip-networking >>>> skip-external-locking >>>> max_connections=200 >>>> read_buffer_size=1M >>>> sort_buffer_size=1M >>>> innodb_buffer_pool_size = 70M >>>> innodb_additional_mem_pool_size = 10M >>>> innodb_lock_wait_timeout = 50 >>>> innodb_log_files_in_group=2 >>>> innodb_log_file_size = 20M >>>> innodb_log_buffer_size = 8M >>>> innodb_lock_wait_timeout=50 >>>> innodb_flush_log_at_trx_commit = 1 >>>> [mysql.server] >>>> user=mysql >>>> basedir=/var/lib >>>> socket=/var/lib/mysql/mysql.sock >>>> [mysqld_safe] >>>> err-log=/var/log/mysqld.log >>>> pid-file=/var/run/mysqld/mysqld.pid >>>> >>>> --Tonni >>>> >>>> -- >>>> Tony Earnshaw >>>> Email: tonni at hetnet dot nl >>>> >>> >>> -- >>> ?talo Rossi >>> Administra??o de redes >>> Diginet Brasil >>> [EMAIL PROTECTED] >>> (+55) 84 4008-9000 >>> >>> >>> Esta mensagem, incluindo seus anexos, pode conter informa??o confidencial >>> e/ou >>> privilegiada. Se voc? n?o for o destinat?rio ou a pessoa autorizada a >>> receber >>> esta mensagem, n?o pode usar, copiar ou divulgar as informa??es nela >>> contidas ou tomar qualquer a??o baseada nessas informa??es. Se voc? >>> recebeu esta mensagem por engano, por favor avise imediatamente o >>> remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua >>> coopera??o. >>> >>> This message, including its attachments, may contain confidential and/or >>> privileged information. If you are not the recipient or authorized person >>> to >>> receive this message, you must not use, copy, disclose or take any action >>> based >>> on this message or any information herein. If you received this message >>> by mistake, please advise the sender immediately by replying the e-mail >>> and >>> deleting this message. Thank you for your cooperation. >>> >>> >>> >>> >> >> > > -- > ?talo Rossi > Administra??o de redes > Diginet Brasil > [EMAIL PROTECTED] > (+55) 84 4008-9000 > > > Esta mensagem, incluindo seus anexos, pode conter informa??o confidencial > e/ou > privilegiada. Se voc? n?o for o destinat?rio ou a pessoa autorizada a > receber > esta mensagem, n?o pode usar, copiar ou divulgar as informa??es nela > contidas ou tomar qualquer a??o baseada nessas informa??es. Se voc? > recebeu esta mensagem por engano, por favor avise imediatamente o > remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua > coopera??o. > > This message, including its attachments, may contain confidential and/or > privileged information. If you are not the recipient or authorized person > to > receive this message, you must not use, copy, disclose or take any action > based > on this message or any information herein. If you received this message > by mistake, please advise the sender immediately by replying the e-mail and > deleting this message. Thank you for your cooperation. > > > >
