[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySql hangs on Windows server

Posted on 2013-01-28
13
Medium Priority
?
526 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 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 1000 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 14 hours left to enroll

834 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