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), 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. 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
