Re: Locking Issue?

[Heikki Tuuri]

Terry,

----- Original Message ----- 
From: "Terry Riley" <moscow@stripped>
Newsgroups: mailing.database.myodbc
Sent: Monday, December 06, 2004 8:15 PM
Subject: Locking Issue?


> Can someone help, please?
>
> We set up a server to handle a coldfusion web application (CFMX 6.1)
> running against MySQL 4.1.3b-beta on WinNT.
>
> When it is a little stretched, we are finding many instances of queries
> listed as either 'Sending...' or 'Copying...' in the processlist, with
> the time going ever upwards (last check was at 1000 seconds and rising).
> All the tables in the database concerned are InnoDB, and none of the
> queries concerned are, as far as I know, involved in any transaction -
> they are straight selects (albeit complex ones, perhaps).
>
> When this happens, the other requests to the server are inevitably slow,
> and these seem never to be cleared unless I kill the threads - and I'm not
> 100% sure how much damage I'm doing in that action.
>
> Is this a possible locking issue? If so, how do I get around it. The
> settings for the server are at default, except where noted.
>
> This is the my.ini file:
>
> [mysqld]
>
> max_connections=1000
>
> basedir=e:/mysql
> datadir=e:/mysql/data
>
> wait_timeout=60
>
> # TR added next 6 lines on 27/07/04, after instal of v4.1.3b
> old-passwords
> local-infile
> query_cache_size=25M
> query_cache_type=1
> set-variable=max_allowed_packet=16M
> set-variable=key_buffer=8M
>
> log-bin=
> log_slow_queries=
>
>
> [mysql]
> local-infile=1
>
>
> Any pointers as to what I may be doing wrong? Please?
>
> Yes, I know we should upgrade to 4.1.7, and we will - soon.

try tuning InnoDB. Your workload may be seriously disk-bound.

> Cheers
> Terry Riley

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php