We had done that, but are not really seeing any consistent issues with a long running query in the normal sense. What we are seeing is that when the system is running "well" (innodb_thread_concurrency at 1), we see little to nothing for slow queries. However, when the server hits whatever the source of the bottleneck is, every query being run slows to a crawl.
On Wed, Aug 27, 2008 at 8:00 PM, Jake Anderson <[EMAIL PROTECTED]> wrote: > Have you tried turning on the slow query log and seeing what queries are > causing the problem? > Depending on your disk layout you can try moving some of the tables to > different spindles which might improve performance. > > > Rob Beglinger wrote: > > I appreciate your observations, unfortunately I am stuck with Intel > Quad-Core processors. :) > > We are having significant issues handling IMAP requests in a timely manner > (12 seconds to load the Inbox in Thunderbird and an in-house Java-mail app) > on our dbmail implementation and I believe the problem resides with the > database configuration. Is there anyone out there that is running dbmail > 2.2.10 with a MySQL 5.0.X database with a large user group that can share > their my.cnf? Our user count is over 4000. This is our my.cnf: > > # You can copy this file to > # /etc/my.cnf to set global options, > # mysql-data-dir/my.cnf to set server-specific options (in this > # installation this directory is /usr/local/mysql/data) or > # ~/.my.cnf to set user-specific options. > # > # In this file, you can use all long options that a program supports. > # If you want to know which options a program supports, run the program > # with the "--help" option. > > # The following options will be passed to all MySQL clients > [client] > #password = your_password > port = 3306 > socket = /var/lib/mysql/mysql.sock > > # Here follows entries for some specific programs > > # The MySQL server > [mysqld] > port = 3306 > socket = /var/lib/mysql/mysql.sock > max_connections = 500 > max_connect_errors = 999999 > skip-locking > key_buffer = 384M > max_allowed_packet = 8M > table_cache = 1024 > sort_buffer_size = 16M > read_buffer_size = 16M > read_rnd_buffer_size = 8M > myisam_sort_buffer_size = 64M > thread_cache_size = 16 > query_cache_size = 128M > query_cache_limit = 2M > long_query_time = 10 > > # These variables determine the max size of a memory temp table > tmp_table_size = 32M > max_heap_table_size = 32M > > tmpdir = /tmp > datadir = /var/lib/mysql > > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 32 > > # Don't listen on a TCP/IP port at all. This can be a security enhancement, > # if all processes that need to connect to mysqld run on the same host. > # All interaction with mysqld must be made via Unix sockets or named pipes. > # Note that using this option without enabling named pipes on Windows > # (via the "enable-named-pipe" option) will render mysqld useless! > # > #skip-networking > > # MySQL General Query Log > #log=mysql.general.log > > > # MySQL Binary Log > log-bin=mysql-bin > #expire_logs_days = 1 > > > # MySQL Slow Query Log > #log-slow-queries=/var/lib/mysql/mysql.slow.log > #log-queries-not-using-indexes > > # required unique id between 1 and 2^32 - 1 > # defaults to 1 if master-host is not set > # but will not function as a master if omitted > server-id = 1 > > > # 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 = 256M > innodb_log_buffer_size = 16M > innodb_flush_log_at_trx_commit = 1 > innodb_lock_wait_timeout = 50 > innodb_thread_concurrency = 1 > innodb_thread_sleep_delay = 0 > innodb_flush_method = O_DIRECT > transaction-isolation = READ-COMMITTED > #innodb_sync_spin_loops = 20 > innodb_concurrency_tickets = 1500 > innodb_support_xa = 0 > innodb_open_files = 1000 > #skip_innodb_doublewrite > #skip_innodb_checksums > > [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 > > > We are running MySQL on SLES 10 SP2 kernel version is 2.6.16.60-0.21-smp > 32GB of RAM on a 4xQuad-Core Intel server. We've changed > innodb_thread_concurrency on the fly to see if we had any performance > increase, but the latest tests show no improvement with decreased > performance at innodb_thread_concurrency at 4 or higher. I have been given > until Friday (2 days) to resolve the issue before I am forced to go back to > a flat file email system, so I greatly appreciate any and all help. > > If there are any other areas that I should be looking at, I will gladly > take any/all suggestions. > > Thank you very much, > > Rob > > > > > > On Fri, Aug 22, 2008 at 5:15 AM, Vladimir Likhachev < > [EMAIL PROTECTED]> wrote: > >> Sorry, of course, Sun bought MySQL AB. >> Large server memory (more then 4 GB) usage details is the main reason of >> AMD64 usage and its suggestion by Oracle, I think... Not details of >> InnoDB format or MySQL server realisation... At whole, for each (heavy? >> strongly?) loaded server program large memory usage is very important. >> The next reason is non-independent level 2 cache memory in Intel Core Quad >> processors. >> Intel Em64T technology is (a very simple view) memory over 4 GB mapping in >> a window 128 or 256 MB width laying below 4 GB in UMA address space. >> >> All this is my opinion only, based on Intel SR/SH (4*Xeon-MP) and Intel >> platforms 4*Xeon 2*Core processors, 1*Intel CoreQuad workstations usage. No >> use Intel CoreQuad for "serious" servers in companies where I work. Up to >> 120 GB InnoDB bases in DBMail 2.xx. >> >> ------------------------------ >> >> Date: Fri, 22 Aug 2008 10:07:10 +1000 >> From: [EMAIL PROTECTED] >> To: [email protected] >> Subject: RE: [Dbmail] Query to show mailbox folders running slow >> >> >> On Thu, 21 Aug 2008 08:01:43 +0000, Vladimir Likhachev wrote: >> >> > Oracle (current owner of MySQL AB) suggests AMD64 processors. >> >> Minor correction. Sun bought MySQL AB. Oracle bought InnoDB. I'm not too >> impressed with either move frankly. How many people think Oracle bought >> InnoDB to incorporate their technology into the next version of Oracle? >> >> >> ------------------------------ >> Explore the seven wonders of the world Learn >> more!<http://search.msn.com/results.aspx?q=7+wonders+world&mkt=en-US&form=QBRE> >> >> _______________________________________________ >> DBmail mailing list >> [email protected] >> https://mailman.fastxs.nl/mailman/listinfo/dbmail >> >> > ------------------------------ > > _______________________________________________ > DBmail mailing [EMAIL PROTECTED]://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > -- > > Vapour Forge > > Jake Anderson > > Project Manager > > Mobile: 0412 897 125 > > Email: [EMAIL PROTECTED] > > Web Page: www.vapourforge.com > > Your source for custom IT services > > _______________________________________________ > DBmail mailing list > [email protected] > https://mailman.fastxs.nl/mailman/listinfo/dbmail > >
_______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
