Tony,
Your my.cnf file is set up for MyISAM, not InnoDB. MySQL allows you to
allocate memory and resources to any and all storage engines. Yours is
set up to give lots of resources to MyISAM, and none to InnoDB.
Reducing MyISAM
key_buffer = 384M - this is way too much - I'd set to to 2-16 meg,
assuming that the only MyISAM tables you have are in the mysql database.
query_cache_size = 32M - read up on the query cache - it's only useful
for oft-repeated queries that hit tables in which the data rarely
changes. We turn ours off
The big variable in InnoDB (that affects performance the most) is the
innodb_buffer_pool_size. Since you are running a xeon, I am guessing
it's a 32-bit architecture. There is a limit on the max size of the process
The amount of memory MySQL will use is:
innodb_buffer_pool_size + key_buffer +
max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) +
max_connections*2MB
You should make sure that stays under 2 gigabytes. If MySQL uses much
more memory, it will crash.
There are other tuning choices (including the thread-pool-cache). The
best resource is the page on innodb performance tuning, and it can be
found here:
http://dev.mysql.com/doc/mysql/en/innodb-configuration.html
You might also want to consider High Performance MySQL. There is lots of
good info in there on setup, tuning, replication, etc.
David
tony wrote:
Hi David,
On Fri, 2005-07-15 at 10:25 -0700, David Griffiths wrote:
Tony,
" - not sure what version you are using
4.1.11. Server is a duel xeon machine with 4gb or ram running mysql and
apache webserver and not much else.
You should have used
my-innodb-heavy-4G.cnf as the starting point for an InnoDB system.
I can use this instead if it's going to help.
If you want help, you'll need to post your my.cnf file,
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
log = /var/log/mysql/mysql.log
log-slow-queries = /var/log/mysql/mysql-slow.log
set-variable = max_connections=250
server-id = 1
innodb_data_home_dir = /var/lib/mysql/
innodb_log_group_home_dir = /var/lib/mysql/
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20
the full table
definition (try SHOW CREATE TABLE tblSoppingCart; and pasting the
results in here).
tblCart | CREATE TABLE `tblCart` (
`intCartUid` int(11) NOT NULL auto_increment,
`intUserUid` int(11) NOT NULL default '0',
`tsCartCreated` datetime NOT NULL default '0000-00-00 00:00:00',
`tsLastUpdated` datetime NOT NULL default '0000-00-00 00:00:00',
`strCartHash` varchar(32) NOT NULL default '',
PRIMARY KEY (`intCartUid`),
KEY `intUserUid` (`intUserUid`),
KEY `tsLastUpdated` (`tsLastUpdated`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
You'll need to also post the queries that are hitting the database while
you're having these issues.
# Query_time: 20 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SET insert_id=34475,timestamp=1121407309;
INSERT INTO
dbseThxWebOrders.tblCart
(intUserUid,tsCartCreated,strCartHash)
VALUES
(0,now(),'4e5d105f7cd34268e1a5e160d479ed91');
is an example from my slow query log. All of the offending queries today
were this same query.
Thanks for you help
Tony
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
