MySQL not connecting need to restart server to connect

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)
nisupportAsked:
Who is Participating?
 
Aleksandar BradarićSoftware DeveloperCommented:
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
 
SissonCommented:
Try upgrading your version of MySQL

what is the exact error you are getting?
0
 
Aleksandar BradarićSoftware DeveloperCommented:
I suppose you've checked your code and that it's closing all connections it opens?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nisupportAuthor Commented:
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
 
Aleksandar BradarićSoftware DeveloperCommented:
> 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
 
nisupportAuthor Commented:
I notice that my opened tables is high, would closing the connection with mysql_close solve this issue as well?
0
 
Aleksandar BradarićSoftware DeveloperCommented:
Yes, it should.
0
 
nisupportAuthor Commented:
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
 
nisupportAuthor Commented:
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
 
nisupportAuthor Commented:
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
 
Aleksandar BradarićSoftware DeveloperCommented:
Yes.
0
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.