Do vmstat and top reveal anything about the server's performance? Can you post the show status output?
-----Original Message----- From: Boyd E. Hemphill To: [EMAIL PROTECTED] Sent: 8/13/04 12:17 PM Subject: Tuning InnoDB situation All: I have been nosing about for some time now and think I need some help. The Problem: Mytop is telling me that I am running no more than 1000 queries per second, and the key efficiency is 100%. But, some select and replace statements are taking an unusually long time. These seem to revolve around a couple of tables that are written to and read from very often. The queries normally take no longer than 5 seconds in a test environment on a slower machine. In production (where the problem is) they can last from 90 to 1400 seconds. A few of these bring our site to a crawl. Suspicions: I have seen this machine run at 3000 to 6000 qps and still move data out fast. Some of the queries it is now performing slow are were part of this performance in the past. So my first idea is that the server, rather than the query, needs to be tuned. Here is the my.cnf stuff: [mysqld] port = 3306 socket = /var/run/mysqld/mysqld.sock skip-locking skip-bdb set-variable = key_buffer=16M set-variable = max_allowed_packet=10M set-variable = max_connections=1200 set-variable = table_cache=256 set-variable = sort_buffer=2M set-variable = net_buffer_length=64K set-variable = myisam_sort_buffer_size=32M log-bin server-id = 2 pid-file = /var/run/mysqld/mysqld.pid #log = /var/log/mysql/mysql.log log-slow-queries basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english default-table-type = innodb query-cache-type = 1 query-cache-size = 20M set-variable = net_read_timeout=600 set-variable = net_write_timeout=600 innodb_data_home_dir = /var/lib/mysql/innodb innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:15G:autoextend set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=1G set-variable = innodb_log_buffer_size=16M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=800M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 I am considering raising the table_cache to 1500 and the innodb_buffer_pool_size to 2.5GB. Comments on this would be appreciated as well. If this is not the issue then I suspect there is contention in some of the busy tables. Where do I look at the SHOW INNODB STATUS output to detect this situation? What am I looking for? Can I schedule InnoDB transactions? We are running Gentoo Linux 2.6.4 on a dual AMD Opteron machine with 3.5 GB of memory and a RAID 1+0 disc array. We are using the InnoDB storage engine. The DB contains about 160 tables. The DB is about 50GB in size. Thanks for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: "WOW! What a ride!" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]