MySql hangs on Windows server

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?
Who is Participating?
Steve BinkConnect With a Mentor Commented:
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.
Aaron TomoskyConnect With a Mentor SD-WAN SimplifiedCommented:
Start with this:

see if it has any suggestions for your php install. It shows up as a management icon in IIS
Steve BinkCommented:
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.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Aaron TomoskySD-WAN SimplifiedCommented:
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.
ncwAuthor Commented:
@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.
ncwAuthor Commented:
The Phpmanager looks easy to install, instructions are here. I also see it can validate the PHP installation and makes recommendations.
ncwAuthor Commented:
Unfortunately IIS is version 6 which is not supported by PHPManager. Looks like the server company may have installed MySql Tuner.
ncwAuthor Commented:
I've updated the MySql configuration settings in my.ini with the following from

query_cache_size = 16M
log-slow-queries=C:\Program Files\Parallels\Plesk\MySQL\logs/slowqueries.log
long_query_time = 4
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.
Steve BinkCommented:
>>> 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.
ncwAuthor Commented:
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).
Steve BinkCommented:
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.
Aaron TomoskySD-WAN SimplifiedCommented:
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.
ncwAuthor Commented:
Seems like the performance issue may have gone away, no idea why.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.