Solved

MySql hangs on Windows server

Posted on 2013-01-28
13
515 Views
Last Modified: 2013-03-21
We have a Windows dedicated server that runs php and mysql as well as asp. When I access MySql using PhpMyadmin it can often hang for maybe a minute; I can open tables and view data fine and then the next click and it hangs. The admin section to the website is experiencing similar issues. Any ideas please?
0
Comment
Question by:ncw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 250 total points
ID: 38829250
Start with this:
http://phpmanager.codeplex.com/

see if it has any suggestions for your php install. It shows up as a management icon in IIS
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 38829465
It has been my experience that phpMyAdmin is more a tool of last resort than a functional day-to-day manager.  When these hangs happen, have you tried using a standard MySQL client to test the database server's response?

As far as your admin panel, it is most likely having unrelated issues of its own.  I recommend installing some traces into your code so you can see what is going on, and how long it takes to complete.
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 38829514
you can also install the mysql workbench and see if that works better. I was going with the same assumption as routinet that it may be an IIS/PHP/PHPMyAdmin issue, rather than an actual MySql issue.
0
Independent Software Vendors: 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!

 
LVL 1

Author Comment

by:ncw
ID: 38830177
@aarontomosky: phpmanager looks like a great tool, is it easy to install on an existing webserver environment or are you suggesting it may already be installed? Does it include a facility that checks your installation and suggests fixes? Workbench also looks like a great tool, I've downloaded it and will test it on my local server, thanks.

@routinet: No not tried a MySql client yet because the hanging is intermittent. I do believe the hanging in admin and PhpMyAdmin are related, but I guess it could be the PHP installation.
0
 
LVL 1

Author Comment

by:ncw
ID: 38830184
The Phpmanager looks easy to install, instructions are here. I also see it can validate the PHP installation and makes recommendations.
0
 
LVL 1

Author Comment

by:ncw
ID: 38830236
Unfortunately IIS is version 6 which is not supported by PHPManager. Looks like the server company may have installed MySql Tuner.
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 250 total points
ID: 38830247
First thing is to rule out the database service.  Use a standard MySQL client for that.  Just connect to the service when phpMyAdmin or your admin portal is exhibiting the hanging behavior.  

After that, I would focus on the performance of the admin portal.  Find out where the execution is stopping.  Some simple writes to the error log, with a timestamp, will suffice.  Once you figure out which part of the code is hanging, you can address that directly.

I very much doubt the two incidents are related, simply because phpMyAdmin is notorious for this type of behavior.  While it is possible your portal and phpMyAdmin are using the strategies, which results in the same behavior, but you at least have a chance to debug with your portal.
0
 
LVL 1

Author Comment

by:ncw
ID: 38884154
I've updated the MySql configuration settings in my.ini with the following from http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html

[mysqld]
skip-name-resolve
query_cache_size = 16M
log-slow-queries=C:\Program Files\Parallels\Plesk\MySQL\logs/slowqueries.log
long_query_time = 4
log-queries-not-using-indexes
table_cache = 512
tmp_table_size = 128M
max_heap_table_size = 128M
myisam_sort_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 256K
key_buffer = 128M

Open in new window


So far there have been no slow queries logged, but then I've no way of knowing if the logging will work if a slow query occurs.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 38885279
>>> I've no way of knowing if the logging will work if a slow query occurs.

What version of MySQL are you using?  See this page for the recommended configuration for your version.

There are several ways to test the slow query log:

1) Decrease the time to 1, and trigger a query that takes a minimum of 1 second.  Depending on your version, you may be able to set a microsecond threshold.

2) Create a large random table (say, 500000 rows), and self-cross-join it.  If it does not meet the minimum, add rows until it does, or adjust the minimum.

3) Include log_slow_admin_statements, and execute an ANALYZE or OPTIMIZE command on a large table.

Chances are, the slow query log will return nothing useful.  The problems exhibited by phpMyAdmin are not the result of slow running queries, but (I believe) of the application "losing" its connection or session.

I still maintain that the problems in phpMyAdmin are unrelated to the problems you are experiencing in your portal application.  I would still focus on debugging your application, removing phpMyAdmin from the equation as much as possible.
0
 
LVL 1

Author Comment

by:ncw
ID: 38929346
No slow queries logged. I added some time logging to some code that appeared to run slow and occasionally the logging detected that the code querying the database took 0.01second, otherwise it was something less. So I wonder if it might be a network issue at the host (the intermittent slowness occurs over several broadband connections on several PC's).
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 38931129
If you are satisfied that the database service is not the culprit, I would move on to benchmarking specific parts of your portal application.  Begin with a pair of timestamps at the start and finish of execution, and a pair of timestamps book-ending the section of code that exhibits this hanging behavior.  When you log the timestamps, be sure to include some relevant information about the request (remote IP, arguments passed, session id, etc.) to help with tracking.  Then attempt to reproduce the behavior.  Once you have reproduced it, the logs should show you where that extra time is going.  Adjust the location of the the inner pair of timestamps, or add more timestamps for better granularity, then repeat.  Eventually, you'll narrow it down to the exact source of the problem.

If the timestamps don't show the extra time being wasted in execution, you can start benchmarking other areas, such as Apache's processes, your network buffer, etc.
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 38931357
I'll also add that IIS 6 is:
1. old and unsupported
2. never really ran php that well

If you can find a win7 whatever laying around, try letting iis 7.5 with current php/mysql/phpmanager have a try at it.
0
 
LVL 1

Author Closing Comment

by:ncw
ID: 39007643
Seems like the performance issue may have gone away, no idea why.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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