Re: Locking Issue
Hello. Use SHOW PROCESSLIST and slow-query log to catch the query which locks tables for a long time. Upgrade to 4.1.13. If you use MyISAM, think about moving towards InnoDB. Aaron [EMAIL PROTECTED] wrote: Hi all , I have been experiencing intermittent locking issues with MYSQL. It appears that sometimes a query will lock reliease its lock, and is causing other queries to wait and wait until the connection limit is reached and i am locked out of the database. Has anyone ever had anything like this happen? The setup: Redhat 9.0 , Kernel 2,4,20-8smp mysql-standard-4.1.7-pc-linux-i686-icc-glibc23 MyISAM Tables (And unless InnoDB can support fulltext or some other equivalent , migrating isnt an option at present) ext2fs Our Datbase Activity: We have a somewhat active website. Things run fairly smoothly for the most part , although we do have some slow queries from time to time. We have far more selects than updates , but updates are still reasonably active. Frequently , an update will get locked while a slower query is running. Sometimes we can experience a large backup waiting for a slow query , but typically everything sorts out once the slow query finishes. Rarely , however , a query will be in a locked state and will not let go of its lock. Subsequent updates lock , and subsequent selects lock. Eventually , if the above has happened , the connection table will fill up. We dont have any scripts that explicitly LOCK TABLES , aside from our backup script which uses mysqlhotcopy. Is it possible that the mysqlhotcopy LOCK TABLES could interfere with the locking from the website activity? I apologise for the vagueness of this request , I really dont know what direction would be best to further diagnose this. If you have any advice , it would be greatly appreciated. thanks for your time! Aaron -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking Issue
The first thing I would do is to upgrade the Kernel, as per you r mail u said u were running 2.4.20-8, get the latest one for RH9 that is 2.4.20-31.9 SMP, and you might see a huge difference, if it doesn't work, then make sure you have properly indexed the colums, mytop is a great tool for diagnosis, also see the slow query log, play around with top and other OS tools, this should work if not switch to INNODB Kishore Jalleda On 8/11/05, Aaron [EMAIL PROTECTED] wrote: Hi all , I have been experiencing intermittent locking issues with MYSQL. It appears that sometimes a query will lock reliease its lock, and is causing other queries to wait and wait until the connection limit is reached and i am locked out of the database. Has anyone ever had anything like this happen? The setup: Redhat 9.0 , Kernel 2,4,20-8smp mysql-standard-4.1.7-pc-linux-i686-icc-glibc23 MyISAM Tables (And unless InnoDB can support fulltext or some other equivalent , migrating isnt an option at present) ext2fs Our Datbase Activity: We have a somewhat active website. Things run fairly smoothly for the most part , although we do have some slow queries from time to time. We have far more selects than updates , but updates are still reasonably active. Frequently , an update will get locked while a slower query is running. Sometimes we can experience a large backup waiting for a slow query , but typically everything sorts out once the slow query finishes. Rarely , however , a query will be in a locked state and will not let go of its lock. Subsequent updates lock , and subsequent selects lock. Eventually , if the above has happened , the connection table will fill up. We dont have any scripts that explicitly LOCK TABLES , aside from our backup script which uses mysqlhotcopy. Is it possible that the mysqlhotcopy LOCK TABLES could interfere with the locking from the website activity? I apologise for the vagueness of this request , I really dont know what direction would be best to further diagnose this. If you have any advice , it would be greatly appreciated. thanks for your time! Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking Issue?
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]
RE: Locking Issue?
-Original Message- From: Terry Riley [mailto:[EMAIL PROTECTED] Sent: Monday, December 06, 2004 10:12 AM To: [EMAIL PROTECTED] 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. [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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Locking Issue?
- Original Message - Thanks for those hints, Dathan (see below): -Original Message- From: Terry Riley [mailto:[EMAIL PROTECTED] Sent: Monday, December 06, 2004 10:12 AM To: [EMAIL PROTECTED] 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking Issue?
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. 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]
