Solved

Problem with MySql Server -- hanging on query -- status LOCKED and not being released

Posted on 2007-03-29
12
427 Views
Last Modified: 2012-06-22
I am having problems with a MySql Database installed on a dedicated server.  I'm running an older version, 4.1.7, as it is used for a messenger who requires this version.  My problem is that once in a while, not all the time, my database will hang.  When this occurs, I see a bunch of users in the user connections window, and all these users show a query, and a status LOCKED.  I'm having a hard time to troubbleshoot this because whenever this happens, I lose access to my server as it hangs, so I have to reboot, hence loose the process list causing the problem.  I've looked at my logs and found nothing of interest.  Any idea what might be happening here?
0
Comment
Question by:John Account
  • 7
  • 4
12 Comments
 
LVL 19

Accepted Solution

by:
Kim Ryan earned 250 total points
ID: 18821764
There could be some contetion/deadlock problems. Say a user gets a lock while doing a transaction. If the lock is held for too long other users are prevented from completing there queries. You could turn on the query logs to see what the quries are just before failure.

There are configuration options to control this situation.
0
 

Author Comment

by:John Account
ID: 18824263
Teraplane, yes, well, I sorta know what query gets locked.  What configuration option is that please?
0
 
LVL 4

Assisted Solution

by:mukhtar2t
mukhtar2t earned 250 total points
ID: 18824377
If your MYISAM table has huge inserts and updates you can convert it to INNODB
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:John Account
ID: 18825954
How do I turn on the query logs?
0
 
LVL 4

Expert Comment

by:mukhtar2t
ID: 18826731
There is error log file and slow log file
error log:
you can turn it by add this line under [mysqld_safe] section on /etc/my.conf
error_log = /var/log/mysqld.log # or any path as you like

slow log:
log-slow-queries=/var/log/mysql_slow.log # or any path as you like

and i advice you to view this link
http://dev.mysql.com/doc/refman/4.1/en/log-files.html
0
 

Author Comment

by:John Account
ID: 18828925
Ok, I did as mukhtar2t: said and changed my MyIsam to InnoDB engine and so far, so good... but I haven't had a lot of traffic yet.  So I'll let you know later if that took care of my problem.
0
 
LVL 4

Expert Comment

by:mukhtar2t
ID: 18829313
It will beterr because the INNODB table has  a row level lock while MYISAM table lock the whole table
0
 

Author Comment

by:John Account
ID: 18837488
ok, that helped a bit, but my server is still hanging... Is there a way to schedule a task to stop and start the mysql server once a day?
0
 
LVL 4

Expert Comment

by:mukhtar2t
ID: 18837632
You can make a cron tab job that restart mysql server, you can restart your server by this command:
/etc/init.d/mysql restart
but better if you solve the hanging reason instead of restarting the server
0
 

Author Comment

by:John Account
ID: 18840347
Isn't cron associated with Linux / unix?  I'm on windows.
0
 

Author Comment

by:John Account
ID: 18885006
ok, well I was able to setup a task to restart the server every night.  That is helping a lot.  The server is not hanging.  But I know this is just a quick fix.  Nonetheless, I will award the points to mukhtar2t for his help.
0
 

Author Comment

by:John Account
ID: 18885013
actually both terraplane and mukhtar2t:
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
server core and windows updates 3 40
Scheduled Tasks Tweak 5 31
Two "Pin to Start…" options in Windows 10 4 28
MySQL Query Using Up Memory 6 30
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question