RE: Locking Issue?

[Terry Riley]

----- Original Message -----

Thanks for those hints, Dathan (see below):

> 
> -----Original Message-----
> From: Terry Riley [mailto:moscow@stripped] 
> Sent: Monday, December 06, 2004 10:12 AM
> To: mysql@stripped
> 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, 
> 
> 
> Sending Data means stream the result set back, mysql found the rows and
> is still searching.
> Copying to tmp table means that it's using the tmp_table_size variable
> and if it busts past that will write to a temp table.
> 
> Since you using innodb you need to increase your innodb buffer pool.
> Additionaly increase your tmp_table_size buffer, and verify your
> queries. You might need to tweak innodb_io_threads a feature specific
> for windows, and the awe memory setting.
> 
> You might be system bound.

I've already increased the tmp_table_size a little, but now that hits have 
trailed off (it's 8pm here), I'll have to wait till tomorrow to test this 
and other suggestions you've made.

> 
> 
> [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.
> 

Cheers
Terry Riley