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]
