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
