From: Date: August 13 2004 7:17pm
Subject: Tuning InnoDB situation
List-Archive: http://lists.mysql.com/mysql/170940
Message-Id: <006201c48159$76a713b0$560aa8c0@boyd>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

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            =3D 3306
socket          =3D /var/run/mysqld/mysqld.sock
skip-locking
skip-bdb
set-variable    =3D key_buffer=3D16M
set-variable    =3D max_allowed_packet=3D10M
set-variable    =3D max_connections=3D1200
set-variable    =3D table_cache=3D256
set-variable    =3D sort_buffer=3D2M
set-variable    =3D net_buffer_length=3D64K
set-variable    =3D myisam_sort_buffer_size=3D32M
log-bin
server-id       =3D 2
pid-file        =3D /var/run/mysqld/mysqld.pid
#log             =3D /var/log/mysql/mysql.log
log-slow-queries
basedir         =3D /usr
datadir         =3D /var/lib/mysql
tmpdir          =3D /tmp
language        =3D /usr/share/mysql/english
default-table-type =3D innodb
query-cache-type =3D 1
query-cache-size =3D 20M
set-variable  =3D net_read_timeout=3D600
set-variable    =3D net_write_timeout=3D600

innodb_data_home_dir =3D /var/lib/mysql/innodb
innodb_log_group_home_dir =3D /var/lib/mysql/
innodb_log_arch_dir =3D /var/lib/mysql/
innodb_data_file_path =3D ibdata1:15G:autoextend
set-variable =3D innodb_mirrored_log_groups=3D1
set-variable =3D innodb_log_files_in_group=3D3
set-variable =3D innodb_log_file_size=3D1G
set-variable =3D innodb_log_buffer_size=3D16M
innodb_flush_log_at_trx_commit=3D1
innodb_log_archive=3D0
set-variable =3D innodb_buffer_pool_size=3D800M
set-variable =3D innodb_additional_mem_pool_size=3D20M
set-variable =3D innodb_file_io_threads=3D4
set-variable =3D innodb_lock_wait_timeout=3D50

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. =20
The DB contains about 160 tables.
The DB is about 50GB in size.

Thanks for your time.

Best Regards,
Boyd E. Hemphill
bhemphill@stripped
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!"