Solved

MySql hangs on Windows server

Posted on 2013-01-28
13
498 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
  • 6
  • 4
  • 3
13 Comments
 
LVL 38

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 50

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 38

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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 50

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 50

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 50

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 38

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now