Heikki,
----- Original Message ----- > Terry, > > ----- Original Message ----- > From: "Terry Riley" <[EMAIL PROTECTED]> > 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. I notice that are a lot of tmp_disk_tables created (about 25% of the total tmp_tables), and have been increasing the tmp_table_size (it now stands at 120Mb, with a total database of only 400Mb), with no difference shown in the number of disk_tables created. This is happening even on a light load. Also changed innodb_buffer_pool_size. From its default of 8M (which only shows as 512 in the MySQLAdmin status) to 16M (which showed as 1024) to 32Mb (which shows as 2048). The startup values show correctly as 8,16 or 32Mb respectively. Is this how it should be? The machine is a dual-CPU WinNT with ONLY 512Mb memory - yes, I know, and I've been telling the owners for months to increase that to at least 1GB to give us some operating leeway, as the ColdFusion server is on the same box, consuming at least 150Mb of the memory before MySQL gets to have its share! Should I perhaps reduce the innodb_thread_concurrency from its default of 8 down to 2 or 3 (on the basis of documentation - No of CPUs * No of disks)? All help appreciated, as always. 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 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
