Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MySQL not connecting need to restart server to connect

Posted on 2007-08-09
11
Medium Priority
?
920 Views
Last Modified: 2013-12-13
I am running MySQL and PHP on a Windows server. I am having an error that I cannot connect to the mysql server after a couple days of operation and I have to restart the windows server to connect again. I have tried increasing max connections, table_cache, adding indexes, performing explain queries, etc. I was trying to track the events, which none occurred until the lockup and also the status in phpmyadmin. I have stats from the phpmyadmin saved for the last three days, if that would help. I also don't  think it is clearing PHP sessions. The details of the mysql application events are:
Source: MySQL  
Event ID: 100
Changed limites: max_open_files: 2048  max_connections: 2038  table_cash: 64

Source: MySQL
Event ID: 100
C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections
Version: '5.0.24 - community-nt' socket:" port: 3306 MySQL Community Edition (GPL)
0
Comment
Question by:nisupport
  • 5
  • 5
11 Comments
 
LVL 3

Expert Comment

by:Sisson
ID: 19666231
Try upgrading your version of MySQL

what is the exact error you are getting?
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19668239
I suppose you've checked your code and that it's closing all connections it opens?
0
 

Author Comment

by:nisupport
ID: 19669685
It does close connections, but my pages are setup to include other pages, so I am not sure if it is closing all of them. I might need to restructure and open before each query and close after each query. Currently I include a page that has the connection script. What do you recommend?

Do you think it could be an issue with the version since it is community?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19669763
> I might need to restructure and open before each query and close after each query.

That should be the best way to go.

> Do you think it could be an issue with the version since it is community?

No, being community has nothing to do with handling connections properly.
0
 

Author Comment

by:nisupport
ID: 19675908
I notice that my opened tables is high, would closing the connection with mysql_close solve this issue as well?
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19675928
Yes, it should.
0
 

Author Comment

by:nisupport
ID: 19697317
Now that I have setup the pages to connect at the beginning of each page and close at the end of each page, the Handler_read_rnd_next has become very big. The following items show up in red in phpmyadmin runtime information and the opened tables is still in red.

Innodb_buffer_pool_reads 26  The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.  

Handler_read_rnd 60  The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.  

Handler_read_rnd_next 5,268  The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.  

Created_tmp_disk_tables 12  The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.  

Opened_tables 32  The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.  

Do you have any other suggestions?
0
 
LVL 17

Accepted Solution

by:
Aleksandar Bradarić earned 2000 total points
ID: 19697677
As far as I can tell, you'll need to analyze your queries a bit and see which ones do not use any indexes. A very usefull command for doing this kind of work is `EXPLAIN SELECT ...`. Among other things it will return `possible_keys` and `key`, thus telling you if any index was considered for your `SELECT` query and which one (if any) was actually used.

Once you find queries queries that do not use indexes (e.g. do not have a value in the `key` column), you will want to add (or modify) some indexes to the tables involved. Here's an example:
---
mysql> EXPLAIN SELECT number, group_concat(output SEPARATOR ' + ') FROM yourTable GROUP BY number;
+----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | yourTable | ALL  | NULL          | NULL |    NULL | NULL |   5 | Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.01 sec)

mysql> ALTER TABLE yourTable ADD INDEX idxSpeedUp2(number, output);
Query OK, 5 rows affected (0.09 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT number, group_concat(output SEPARATOR ' + ') FROM yourTable GROUP BY number;
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | yourTable | index | idxSpeedUp2   | idxSpeedUp2 |      36 | NULL |    5 | Using index |
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)
---

Apart from that, you might want to increase the value for `tmp_table_size`, but it's not that much of a problem.
0
 

Author Comment

by:nisupport
ID: 19727419
I have run explains, but I will do it again to make sure I didn't miss anything. .I am getting the same event viewer messages Changed limits: max_open_files: 2048  max_connections: 2038  table_cache: 64 which is followed by a message that says the community edition is ready to accept connections. The max connections number is 10000. Would the explain query solve this, if something is not using an index? I feel like I am running out of options.
0
 

Author Comment

by:nisupport
ID: 19727459
I keep doing searches and they reference making changes to a my.cnf. I cannot find a my.cnf, but have a my.ini. Is that the same thing?
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19728739
Yes.
0

Featured Post

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!

Question has a verified solution.

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

This article discusses how to implement server side field validation and display customized error messages to the client.
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…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

581 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