• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

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

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
John Account
Asked:
John Account
  • 7
  • 4
2 Solutions
 
Kim RyanIT ConsultantCommented:
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
 
John AccountAuthor Commented:
Teraplane, yes, well, I sorta know what query gets locked.  What configuration option is that please?
0
 
mukhtar2tCommented:
If your MYISAM table has huge inserts and updates you can convert it to INNODB
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
John AccountAuthor Commented:
How do I turn on the query logs?
0
 
mukhtar2tCommented:
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
 
John AccountAuthor Commented:
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
 
mukhtar2tCommented:
It will beterr because the INNODB table has  a row level lock while MYISAM table lock the whole table
0
 
John AccountAuthor Commented:
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
 
mukhtar2tCommented:
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
 
John AccountAuthor Commented:
Isn't cron associated with Linux / unix?  I'm on windows.
0
 
John AccountAuthor Commented:
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
 
John AccountAuthor Commented:
actually both terraplane and mukhtar2t:
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now