It turns out the problem is related to a well documented issue with mulit-core processors and MySQL running INNODB. We had to drop the thread concurrency to 1 in MySQL to get our load tests to finish properly. The low thread concurrency unfortunately causes issues when it comes to syncing off our existing system onto the new dbmail system; our times went from 8 hrs to over 15 hrs using IMAPSYNC. Through some trial and error, we determined that for optimal syncing, thread concurrency needs to be set at 5 and for standard use thread concurrency needs to be set to 1.
Here are some links documenting the Multi-Core Innodb MySQL issue. http://www.bigdbahead.com/?p=59 http://mysqlguy.net/blog/2008/07/16/innodb-multi-core-performance http://www.anandtech.com/IT/showdoc.aspx?i=2772&p=9 Unfortunately we didn't know this when we bought our 4XQuad-Core Server (16 cores). Thanks, Rob On Wed, Aug 20, 2008 at 7:38 AM, Paul J Stevens <[EMAIL PROTECTED]> wrote: > My guess is this is a table-locking issue, something you can easily > determine by > looking at mytop or 'mysqladmin processlist' during the slowdown. I can not > comment on your parameters since I don't know what kind of resources the > mysql > server has. > > David Suehring wrote: > > Hey everybody, > > > > We are running into a few issues when running some load tests on dbmail. > > As we are running the test, I've noticed that the following query goes > > from running nearly instantly, to taking anywhere from 10-20 seconds: > > > > SELECT distinct(mbx.name <http://mbx.name>), mbx.mailbox_idnr, > > mbx.owner_idnr FROM dbmail_mailboxes mbx LEFT JOIN dbmail_acl acl ON > > mbx.mailbox_idnr = acl.mailbox_id > > LEFT JOIN dbmail_users usr ON acl.user_id = usr.user_idnr WHERE > > ((mbx.owner_idnr = 2676) OR (acl.user_id = 2676 AND acl.lookup_flag = > > 1) OR (usr.userid = 'anyone' AND acl.lookup_flag = 1)) > > > > As far as our setup goes, we are using dbmail 2.2.10 with MySQL 5.0.26. > > <http://5.0.26.> Our dbmail_mailboxes table currently has about 47000 > > rows, dbmail_users has about 4000 rows, and dbmail_acl has 0 rows. The > > load test we are doing uses a web interface to connect to dbmail, read a > > message, send a message, and repeat. When we simulate 10 users doing > > this, we do not see any performance issues. Once we increase to 20 > > users, the query above begins to take longer and longer, reaching nearly > > 20 seconds in some situations. I realize that this is most likely a > > snowball effect, where once it hits a snag, the queries just pile up and > > become slower as a result. Does anyone have any configuration tips or > > thoughts on how to help speed this up and prevent the slowdown? > > > > Here is our my.cnf configuration: > > > > # The MySQL server > > [mysqld] > > port = 3306 > > socket = /var/lib/mysql/mysql.sock > > max_connections = 200 > > skip-locking > > key_buffer = 16M > > max_allowed_packet = 8M > > table_cache = 1024 > > sort_buffer_size = 2M > > read_buffer_size = 2M > > read_rnd_buffer_size = 8M > > myisam_sort_buffer_size = 8M > > thread_cache_size = 16 > > query_cache_size = 128M > > query_cache_limit = 2M > > long_query_time = 1 > > tmpdir = /tmp > > datadir = /var/lib/mysql > > thread_concurrency = 16 > > > > # InnoDB settings > > innodb_file_per_table > > innodb_data_home_dir = /var/lib/mysql/ > > innodb_data_file_path = ibdata1:10M:autoextend > > innodb_log_group_home_dir = /var/lib/mysql/ > > innodb_log_arch_dir = /var/lib/mysql/ > > innodb_log_files_in_group = 2 > > innodb_buffer_pool_size = 24576M > > innodb_additional_mem_pool_size = 20M > > innodb_log_file_size = 512M > > innodb_log_buffer_size = 16M > > innodb_flush_log_at_trx_commit = 1 > > innodb_lock_wait_timeout = 50 > > innodb_thread_concurrency = 8 > > > > [mysqldump] > > quick > > max_allowed_packet = 16M > > [mysql] > > no-auto-rehash > > # Remove the next comment character if you are not familiar with SQL > > #safe-updates > > [isamchk] > > key_buffer = 256M > > sort_buffer_size = 256M > > read_buffer = 2M > > write_buffer = 2M > > [myisamchk] > > key_buffer = 256M > > sort_buffer_size = 256M > > read_buffer = 2M > > write_buffer = 2M > > [mysqlhotcopy] > > interactive-timeout > > If there is any other information that would be helpful, please let me > > know. > > > > Thanks for any help! > > > > -David > > > > > > ------------------------------------------------------------------------ > > > > _______________________________________________ > > DBmail mailing list > > [email protected] > > https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > -- > ________________________________________________________________ > Paul Stevens paul at nfg.nl > NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 > The Netherlands________________________________http://www.nfg.nl > _______________________________________________ > DBmail mailing list > [email protected] > https://mailman.fastxs.nl/mailman/listinfo/dbmail >
_______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
