# Performance issue with MySQL (failed or lost connection)

**URL:** <https://forums.percona.com/t/performance-issue-with-mysql-failed-or-lost-connection/1148>\
**Category:** Other MySQL® Questions\
**Created:** [April 16, 2009, 2:06pm UTC](https://forums.percona.com/t/performance-issue-with-mysql-failed-or-lost-connection/1148 "2009-04-16T14:06:17Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![pylem](https://avatars.discourse-cdn.com/v4/letter/p/a5b964/32.png) [@pylem](https://forums.percona.com/u/pylem)\
**Post date:** [April 16, 2009, 2:06pm UTC](https://forums.percona.com/t/performance-issue-with-mysql-failed-or-lost-connection/1148/1 "2009-04-16T14:06:17Z")

</div>

Hello,

We recently had many failed or lost connection problem to the mysql server. About 2-3 per hour during heavy peaks. We have a dedicated mysql server with 1 gig of RAM and 2 processor. The server is not swapping and the load is not a problem, 95% of the time under 0.05. We only have MyISAM tables.

I changed the default value of these 2 variables to:  
net\_read\_timeout=60  
connect\_timeout=15

And it solved the problem. But this is not a very good thing to do. I am sure there is a problem which I am not seeing here. If you could help me find it!

# my my.cnf is:

[mysqld]  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
user=mysql  
old\_passwords=1  
log-bin=/mysqlreplog/bin-log  
server\_id=1  
log\_slow\_queries=/mysqlreplog/slow-queries.log  
long\_query\_time=4

skip-locking  
key\_buffer = 256M  
max\_allowed\_packet = 1M  
table\_cache = 256  
net\_read\_timeout=60  
connect\_timeout=15  
sort\_buffer\_size = 1M  
read\_buffer\_size = 1M  
read\_rnd\_buffer\_size = 4M  
myisam\_sort\_buffer\_size = 64M  
thread\_cache\_size = 8  
query\_cache\_size= 16M  
#set-variable = max\_connections=500  
max\_connections=500  
ft\_min\_word\_len=3  
expire\_logs\_days = 7

# Try number of CPU’s\*2 for thread\_concurrency

thread\_concurrency = 4

# [mysqld\_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid

The current status of the server:  
±----------------------------------±-----------+  
| Variable\_name | Value |  
±----------------------------------±-----------+  
| Aborted\_clients | 1449 |  
| Aborted\_connects | 16 |  
| Binlog\_cache\_disk\_use | 0 |  
| Binlog\_cache\_use | 0 |  
| Bytes\_received | 4086824150 |  
| Bytes\_sent | 205346791 |  
| Com\_admin\_commands | 2699 |  
| Com\_alter\_db | 0 |  
| Com\_alter\_table | 2 |  
| Com\_analyze | 0 |  
| Com\_backup\_table | 0 |  
| Com\_begin | 0 |  
| Com\_call\_procedure | 0 |  
| Com\_change\_db | 506467 |  
| Com\_change\_master | 0 |  
| Com\_check | 0 |  
| Com\_checksum | 0 |  
| Com\_commit | 0 |  
| Com\_create\_db | 0 |  
| Com\_create\_function | 0 |  
| Com\_create\_index | 0 |  
| Com\_create\_table | 0 |  
| Com\_create\_user | 0 |  
| Com\_dealloc\_sql | 0 |  
| Com\_delete | 1852 |  
| Com\_delete\_multi | 0 |  
| Com\_do | 0 |  
| Com\_drop\_db | 0 |  
| Com\_drop\_function | 0 |  
| Com\_drop\_index | 0 |  
| Com\_drop\_table | 0 |  
| Com\_drop\_user | 0 |  
| Com\_execute\_sql | 0 |  
| Com\_flush | 0 |  
| Com\_grant | 0 |  
| Com\_ha\_close | 0 |  
| Com\_ha\_open | 0 |  
| Com\_ha\_read | 0 |  
| Com\_help | 0 |  
| Com\_insert | 22385 |  
| Com\_insert\_select | 13 |  
| Com\_kill | 0 |  
| Com\_kill | 0 |  
| Com\_load | 0 |  
| Com\_load\_master\_data | 0 |  
| Com\_load\_master\_table | 0 |  
| Com\_lock\_tables | 0 |  
| Com\_optimize | 7 |  
| Com\_preload\_keys | 0 |  
| Com\_prepare\_sql | 0 |  
| Com\_purge | 0 |  
| Com\_purge\_before\_date | 0 |  
| Com\_rename\_table | 0 |  
| Com\_repair | 0 |  
| Com\_replace | 98458 |  
| Com\_replace\_select | 0 |  
| Com\_reset | 0 |  
| Com\_restore\_table | 0 |  
| Com\_revoke | 0 |  
| Com\_revoke\_all | 0 |  
| Com\_rollback | 0 |  
| Com\_savepoint | 0 |  
| Com\_select | 317449 |  
| Com\_set\_option | 106257 |  
| Com\_show\_binlog\_events | 0 |  
| Com\_show\_binlogs | 14 |  
| Com\_show\_charsets | 1086 |  
| Com\_show\_collations | 1086 |  
| Com\_show\_column\_types | 0 |  
| Com\_show\_create\_db | 0 |  
| Com\_show\_create\_table | 383 |  
| Com\_show\_databases | 1086 |  
| Com\_show\_errors | 0 |  
| Com\_show\_fields | 1185 |  
| Com\_show\_grants | 563 |  
| Com\_show\_innodb\_status | 0 |  
| Com\_show\_keys | 491 |  
| Com\_show\_logs | 0 |  
| Com\_show\_master\_status | 0 |  
| Com\_show\_ndb\_status | 0 |  
| Com\_show\_new\_master | 0 |  
| Com\_show\_open\_tables | 0 |  
| Com\_show\_privileges | 0 |  
| Com\_show\_processlist | 0 |  
| Com\_show\_slave\_hosts | 0 |  
| Com\_show\_slave\_status | 0 |  
| Com\_show\_status | 1 |  
| Com\_show\_storage\_engines | 0 |  
| Com\_show\_tables | 4541 |  
| Com\_show\_triggers | 0 |  
| Com\_show\_variables | 3878 |  
| Com\_show\_warnings | 60 |  
| Com\_slave\_start | 0 |  
| Com\_slave\_stop | 0 |  
| Com\_stmt\_close | 0 |  
| Com\_stmt\_execute | 0 |  
| Com\_stmt\_fetch | 0 |  
| Com\_stmt\_prepare | 0 |  
| Com\_stmt\_reset | 0 |  
| Com\_stmt\_send\_long\_data | 0 |  
| Com\_truncate | 0 |  
| Com\_unlock\_tables | 0 |  
| Com\_update | 37100 |  
| Com\_update\_multi | 1 |  
| Com\_xa\_commit | 0 |  
| Com\_xa\_end | 0 |  
| Com\_xa\_prepare | 0 |  
| Com\_xa\_recover | 0 |  
| Com\_xa\_rollback | 0 |  
| Com\_xa\_start | 0 |  
| Compression | OFF |  
| Connections | 808474 |  
| Created\_tmp\_disk\_tables | 7460 |  
| Created\_tmp\_files | 119 |  
| Created\_tmp\_tables | 34222 |  
| Delayed\_errors | 0 |  
| Delayed\_insert\_threads | 0 |  
| Delayed\_writes | 0 |  
| Flush\_commands | 1 |  
| Handler\_commit | 0 |  
| Handler\_delete | 13597 |  
| Handler\_discover | 0 |  
| Handler\_prepare | 0 |  
| Handler\_read\_first | 189631 |  
| Handler\_read\_key | 26450608 |  
| Handler\_read\_next | 1130806215 |  
| Handler\_read\_prev | 2350760 |  
| Handler\_read\_rnd | 896052 |  
| Handler\_read\_rnd\_next | 1100564513 |  
| Handler\_rollback | 0 |  
| Handler\_savepoint | 0 |  
| Handler\_savepoint\_rollback | 0 |  
| Handler\_update | 560077 |  
| Handler\_write | 4616671 |  
| Innodb\_buffer\_pool\_pages\_data | 20 |  
| Innodb\_buffer\_pool\_pages\_dirty | 0 |  
| Innodb\_buffer\_pool\_pages\_flushed | 0 |  
| Innodb\_buffer\_pool\_pages\_free | 492 |  
| Innodb\_buffer\_pool\_pages\_misc | 0 |  
| Innodb\_buffer\_pool\_pages\_total | 512 |  
| Innodb\_buffer\_pool\_read\_ahead\_rnd | 1 |  
| Innodb\_buffer\_pool\_read\_ahead\_seq | 0 |  
| Innodb\_buffer\_pool\_read\_requests | 135 |  
| Innodb\_buffer\_pool\_reads | 13 |  
| Innodb\_buffer\_pool\_wait\_free | 0 |  
| Innodb\_buffer\_pool\_write\_requests | 0 |  
| Innodb\_data\_fsyncs | 3 |  
| Innodb\_data\_pending\_fsyncs | 0 |  
| Innodb\_data\_pending\_reads | 0 |  
| Innodb\_data\_pending\_writes | 0 |  
| Innodb\_data\_read | 2510848 |  
| Innodb\_data\_reads | 26 |  
| Innodb\_data\_writes | 3 |  
| Innodb\_data\_written | 1536 |  
| Innodb\_dblwr\_pages\_written | 0 |  
| Innodb\_dblwr\_writes | 0 |  
| Innodb\_log\_waits | 0 |  
| Innodb\_log\_write\_requests | 0 |  
| Innodb\_log\_writes | 1 |  
| Innodb\_os\_log\_fsyncs | 3 |  
| Innodb\_os\_log\_pending\_fsyncs | 0 |  
| Innodb\_os\_log\_pending\_writes | 0 |  
| Innodb\_os\_log\_written | 512 |  
| Innodb\_page\_size | 16384 |  
| Innodb\_pages\_created | 0 |  
| Innodb\_pages\_read | 20 |  
| Innodb\_pages\_written | 0 |  
| Innodb\_row\_lock\_current\_waits | 0 |  
| Innodb\_row\_lock\_time | 0 |  
| Innodb\_row\_lock\_time\_avg | 0 |  
| Innodb\_row\_lock\_time\_max | 0 |  
| Innodb\_row\_lock\_waits | 0 |  
| Innodb\_rows\_deleted | 0 |  
| Innodb\_rows\_inserted | 0 |  
| Innodb\_rows\_read | 0 |  
| Innodb\_rows\_updated | 0 |  
| Key\_blocks\_not\_flushed | 0 |  
| Key\_blocks\_unused | 226896 |  
| Key\_blocks\_used | 12952 |  
| Key\_read\_requests | 206683288 |  
| Key\_reads | 191859 |  
| Key\_write\_requests | 214773 |  
| Key\_writes | 104894 |  
| Last\_query\_cost | 0.000000 |  
| Max\_used\_connections | 200 |  
| Ndb\_cluster\_node\_id | 0 |  
| Ndb\_config\_from\_host | |  
| Ndb\_config\_from\_port | 0 |  
| Ndb\_number\_of\_data\_nodes | 0 |  
| Not\_flushed\_delayed\_rows | 0 |  
| Open\_files | 489 |  
| Open\_streams | 0 |  
| Open\_tables | 256 |  
| Opened\_tables | 18937 |  
| Prepared\_stmt\_count | 0 |  
| Qcache\_free\_blocks | 1565 |  
| Qcache\_free\_memory | 4547720 |  
| Qcache\_hits | 860770 |  
| Qcache\_inserts | 299052 |  
| Qcache\_lowmem\_prunes | 75791 |  
| Qcache\_not\_cached | 40669 |  
| Qcache\_queries\_in\_cache | 3828 |  
| Qcache\_total\_blocks | 9896 |  
| Questions | 2781187 |  
| Rpl\_status | NULL |  
| Select\_full\_join | 7195 |  
| Select\_full\_range\_join | 0 |  
| Select\_range | 2366 |  
| Select\_range\_check | 0 |  
| Select\_scan | 123291 |  
| Slave\_open\_temp\_tables | 0 |  
| Slave\_retried\_transactions | 0 |  
| Slave\_running | OFF |  
| Slow\_launch\_threads | 0 |  
| Slow\_queries | 5 |  
| Sort\_merge\_passes | 57 |  
| Sort\_range | 23450 |  
| Sort\_rows | 4872396 |  
| Sort\_scan | 45272 |  
| Ssl\_accept\_renegotiates | 0 |  
| Ssl\_accepts | 0 |  
| Ssl\_callback\_cache\_hits | 0 |  
| Ssl\_cipher | |  
| Ssl\_cipher\_list | |  
| Ssl\_client\_connects | 0 |  
| Ssl\_connect\_renegotiates | 0 |  
| Ssl\_ctx\_verify\_depth | 0 |  
| Ssl\_ctx\_verify\_mode | 0 |  
| Ssl\_default\_timeout | 0 |  
| Ssl\_finished\_accepts | 0 |  
| Ssl\_finished\_connects | 0 |  
| Ssl\_session\_cache\_hits | 0 |  
| Ssl\_session\_cache\_misses | 0 |  
| Ssl\_session\_cache\_mode | NONE |  
| Ssl\_session\_cache\_overflows | 0 |  
| Ssl\_session\_cache\_size | 0 |  
| Ssl\_session\_cache\_timeouts | 0 |  
| Ssl\_sessions\_reused | 0 |  
| Ssl\_used\_session\_cache\_entries | 0 |  
| Ssl\_verify\_depth | 0 |  
| Ssl\_verify\_mode | 0 |  
| Ssl\_version | |  
| Table\_locks\_immediate | 747608 |  
| Table\_locks\_waited | 1349 |  
| Tc\_log\_max\_pages\_used | 0 |  
| Tc\_log\_page\_size | 0 |  
| Tc\_log\_page\_waits | 0 |  
| Threads\_cached | 7 |  
| Threads\_connected | 22 |  
| Threads\_created | 55958 |  
| Threads\_running | 2 |  
| Uptime | 166654 |  
±----------------------------------±-----------+

I think I should try to improve table\_locking, but how?  
I should also increase the key\_buffer and table\_cache which seems a bit low.

Any advice?

Sincerely,

py

---

<div class="post-metadata">

**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\
**Post date:** [April 16, 2009, 2:55pm UTC](https://forums.percona.com/t/performance-issue-with-mysql-failed-or-lost-connection/1148/2 "2009-04-16T14:55:04Z")

</div>

Looking at these:

| Select\_full\_join | 7195 || Select\_scan | 123291 |

I would say that you have a couple of queries that could do with putting proper indexes in place.  
The select\_full\_join indicates that you have 7195 queries that can’t use an index to join two tables, this means that it has to scan the entire second table as many times as there are rows in the primary table.  
Turn on the slow\_query\_log and find the queries that causes this problem and create indexes to solve these.

| Created\_tmp\_disk\_tables | 7460 |

Indicates that you could probably increase the sort\_buffer\_size a bit to avoid that mysql needs to write the temporary table to disk when it doesn’t fit in the sort\_buffer.