# mysql crashes **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [January 8, 2009, 1:48pm UTC](https://forums.percona.com/t/mysql-crashes/1054 "2009-01-08T13:48:09Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![dude](https://avatars.discourse-cdn.com/v4/letter/d/f19dbf/32.png) [@dude](https://forums.percona.com/u/dude)\ **Post date:** [January 8, 2009, 1:48pm UTC](https://forums.percona.com/t/mysql-crashes/1054/1 "2009-01-08T13:48:09Z")
Hi all, for some reason my db refuses to work. sometimes it says: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (11). but a few seconds later it stands up and there’s no need to start the server. Nothing gets logged into the error log (enabled in my.cnf). and I’m really frustrated cuz I don’t have an idea how to check what’s going on. Please help. BTW, I’m on ubuntu, using MyISAM tables. This is my config file: # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe\_mysqld]. Both versions are currently parsed. [mysqld\_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 #open\_files\_limit =20000 [mysqld] # # \* Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking max\_connections=600 #skip-networking skip-name-resolve ft\_min\_word\_len=3 # # For compatibility to other Debian packages that still use # libmysqlclient10 and libmysqlclient12. old\_passwords = 1 # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 10.5.182.2 bind-address = 127.0.0.1 # \* Fine Tuning # key\_buffer = 128M max\_allowed\_packet = 16M thread\_stack = 128K # # \* Query Cache Configuration # query\_cache\_limit = 1048576 query\_cache\_size = 52428800 query\_cache\_type = 1 # # \* Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. #log = /var/log/mysql.log log-error = /var/log/mysql/mysql-new6.err # # Error logging goes to syslog. This is a Debian improvement ) # # Here you can see queries with especially long duration #log-slow-queries = /var/log/mysql/mysql-slow.log # # The following can be used as easy to replay backup logs or for replication. #server-id = 1 #log-bin = /var/log/mysql/mysql-bin.log #expire-logs-days = 20 #max\_binlog\_size = 104857600 #binlog-do-db = include\_database\_name #binlog-ignore-db = include\_database\_name # # \* BerkeleyDB # # According to an MySQL employee the use of BerkeleyDB is now discouraged # and support for it will probably cease in the next versions. skip-bdb skip-innodb [mysqldump] quick quote-names max\_allowed\_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key\_buffer = 16M ---
**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\ **Post date:** [January 8, 2009, 6:20pm UTC](https://forums.percona.com/t/mysql-crashes/1054/2 "2009-01-08T18:20:19Z")
Maybe You have got cron job that brings mysql back online ? What does | [B]Quote:[/B] | | cat /var/log/mysql/mysql-new6.err | shows ? | [B]Quote:[/B] | | less /var/log/syslog | grep mysql | and this ? ---
**Author:** ![dude](https://avatars.discourse-cdn.com/v4/letter/d/f19dbf/32.png) [@dude](https://forums.percona.com/u/dude)\ **Post date:** [January 10, 2009, 3:26pm UTC](https://forums.percona.com/t/mysql-crashes/1054/3 "2009-01-10T15:26:46Z")
The logs do not show much. But I’ve run mysqlreport, and here’s its output: MySQL 5.0.22-Debian\_0ub uptime 0 0:14:7 Sat Jan 10 15:56:51 2009 \_\_ Key \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_\_\_\_ Buffer used 779.00k of 384.00M %Used: 0.20 Current 44.04M %Usage: 11.47 Write hit 37.62% Read hit 99.88% \_\_ Questions \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Total 255.80k 302.0/s Com\_ 167.09k 197.3/s %Total: 65.32 QC Hits 52.68k 62.2/s 20.59 DMS 35.48k 41.9/s 13.87 COM\_QUIT 27.55k 32.5/s 10.77 -Unknown 27.00k 31.9/s 10.56 Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF DMS 35.48k 41.9/s 13.87 SELECT 31.04k 36.6/s 12.14 87.48 UPDATE 4.41k 5.2/s 1.72 12.43 INSERT 31 0.0/s 0.01 0.09 REPLACE 0 0/s 0.00 0.00 DELETE 0 0/s 0.00 0.00 Com\_ 167.09k 197.3/s 65.32 set\_option 54.76k 64.7/s 21.41 change\_db 27.42k 32.4/s 10.72 stmt\_prepar 27.00k 31.9/s 10.56 \_\_ SELECT and Sort \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Scan 29.33k 34.6/s %SELECT: 94.49 Range 18 0.0/s 0.06 Full join 12 0.0/s 0.04 Range check 0 0/s 0.00 Full rng join 0 0/s 0.00 Sort scan 936 1.1/s Sort range 12 0.0/s Sort mrg pass 4 0.0/s \_\_ Query Cache \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Memory usage 31.20M of 60.00M %Used: 52.00 Block Fragmnt 0.01% Hits 52.68k 62.2/s Inserts 4.03k 4.8/s Insrt:Prune 4.03k:1 4.8/s Hit:Insert 13.08:1 \_\_ Table Locks \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Waited 0 0/s %Total: 0.00 Immediate 67.39k 79.6/s \_\_ Tables \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_ Open 3822 of 1360 %Cache: 28.10 Opened 3.91k 4.6/s \_\_ Connections \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Max used 54 of 600 %Max: 9.00 Total 27.55k 32.5/s \_\_ Created Temp \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Disk table 11 0.0/s Table 53 0.1/s Size: 64.0M File 8 0.0/s \_\_ Threads \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_ Running 1 of 1 Cached 0 of 0 %Hit: 0 Created 27.55k 32.5/s Slow 27 0.0/s \_\_ Aborted \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_ Clients 0 0/s Connects 6 0.0/s \_\_ Bytes \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_\_ Sent 150.40M 177.6k/s Received 23.48M 27.7k/s ---
**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\ **Post date:** [January 10, 2009, 6:40pm UTC](https://forums.percona.com/t/mysql-crashes/1054/4 "2009-01-10T18:40:43Z")
dude, paste those logs, please ---
**Author:** ![dude](https://avatars.discourse-cdn.com/v4/letter/d/f19dbf/32.png) [@dude](https://forums.percona.com/u/dude)\ **Post date:** [January 11, 2009, 7:39am UTC](https://forums.percona.com/t/mysql-crashes/1054/5 "2009-01-11T07:39:54Z")
januzi, syslog and mysql logs show nothing. I think my mysql server was not configured well enough to handle the amount of traffic I have. Actually, I don’t have that much traffic, however I have a reciprocal buttons spread accross thousands of other sites, and the script displaying the button made mysql connections (which didn’t make much sense). Now disabled that and looks better. Do you see anything wrong in the mysql report? The COM\_ questions take most of total questions (65,32%) which is according to their guide ([URL][http://hackmysql.com/mysqlreportguide#questions\_report:dtq[/URL]](http://hackmysql.com/mysqlreportguide#questions_report:dtq%5B/URL%5D)), not a good thing. What is com\_set\_option ? Here someone has a similar issue: [URL][Percona Community Forum - Technical forum for help with Percona's open source software for MySQL®, PostgreSQL, and MongoDB® databases.](http://forum.percona.com/s/mv/tree/289/%5B/URL%5D) ---
**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\ **Post date:** [January 11, 2009, 8:24am UTC](https://forums.percona.com/t/mysql-crashes/1054/6 "2009-01-11T08:24:35Z")
I believe that this value comes from “set option” eg. set names ‘utf8’ You should also look at Your queries, some of them need indexes. ---
**Author:** ![dude](https://avatars.discourse-cdn.com/v4/letter/d/f19dbf/32.png) [@dude](https://forums.percona.com/u/dude)\ **Post date:** [January 11, 2009, 10:34am UTC](https://forums.percona.com/t/mysql-crashes/1054/7 "2009-01-11T10:34:37Z")
januzi, | [B]Quote:[/B] | | I believe that this value comes from "set option" eg. set names 'utf8' | you’re right. I do use it in my queries. But is it something really harmful? | [B]Quote:[/B] | | You should also look at Your queries, some of them need indexes. | I’m quessing you’re referring to: Scan 29.33k 34.6/s %SELECT: 94.49 is there a way to spot those queries in an easy way? (log-slow-queries doesn’t log anything) I have 3 websites running on that server, and one of them is pretty big (unfortuanately the code its not well organized, so it’s like looking for a niddle in a haystack…) ---
**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\ **Post date:** [January 11, 2009, 12:31pm UTC](https://forums.percona.com/t/mysql-crashes/1054/8 "2009-01-11T12:31:08Z")
You could set names only at one point: after You connect to the database, eg: | [B]Quote:[/B] | | \ | As for table scans: log-queries-not-using-indexes Queries without indexes will be written to the same file as log-slow-queries (so You need both lines). [URL][http://hackmysql.com/mysqlsla[/URL]](http://hackmysql.com/mysqlsla%5B/URL%5D) - this should be useful You should look for queries that: - scans whole table (or 100k+ records) and returns less than 20% of scanned records (eg. scanned 120k, returned 10) - are “fired” many times (eg. 30% of whole logged queries) ---
**Author:** ![dude](https://avatars.discourse-cdn.com/v4/letter/d/f19dbf/32.png) [@dude](https://forums.percona.com/u/dude)\ **Post date:** [January 15, 2009, 12:21pm UTC](https://forums.percona.com/t/mysql-crashes/1054/9 "2009-01-15T12:21:43Z")
thanks Januzi, is it necessary to disconnect when working with php? | [B]Quote:[/B] | | As for table scans: log-queries-not-using-indexes Queries without indexes will be written to the same file as log-slow-queries (so You need both lines). | been working with it, and it’s awesome. | [B]Quote:[/B] | | [URL="http://hackmysql.com/mysqlsla"]http://hackmysql.com/mysqlsla[/URL] - this should be useful You should look for queries that: - scans whole table (or 100k+ records) and returns less than 20% of scanned records (eg. scanned 120k, returned 10) - are “fired” many times (eg. 30% of whole logged queries) | I will take a closer look at it, but first need to optimized my queries. Actually, there’s just one query left, that I’m not sure if it’s optimized: explain select o.offer\_id from offers o left join companies c ON (c.company\_id = o.company\_fid) left join offers2regions o2r ON (o2r.offer\_fid = o.offer\_id ) left join countries co ON (co.country\_id = o.country\_fid) left join regions w ON (r.region\_id= o2r.region\_fid) WHERE 1=1 AND (o.offer\_token is not null or o.display\_toggle is not null) order by o.date\_publish desc; output: id select\_type table type possible\_keys key key\_len ref rows Extra 1 SIMPLE o ALL offer\_token\_idx,display\_idx NULL NULL NULL 2485 Using where; Using filesort 1 SIMPLE f eq\_ref PRIMARY PRIMARY 4 jobsdb.o.company\_fid 1 1 SIMPLE o2r ref oferta\_idx oferta\_idx 5 jobsdb.o.offer\_id 2 1 SIMPLE k eq\_ref PRIMARY PRIMARY 4 jobsdb.o.country\_fid 1 1 SIMPLE w eq\_ref PRIMARY PRIMARY 4 jobsdb.o2r.region\_fid 1 and in the logs I have: # Query\_time: 0 Lock\_time: 0 Rows\_sent: 4241 Rows\_examined: 20155 ---
**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\ **Post date:** [January 15, 2009, 1:49pm UTC](https://forums.percona.com/t/mysql-crashes/1054/10 "2009-01-15T13:49:20Z")
Php will d/c from mysql when script is done, but You should disconnect after all work with database is done (there is no need to keep connection and mysql will be happier 😉 ). Why “WHERE 1=1” ? You could also force one of the single indexes: offer\_token\_idx or display\_idx (that one which gives less row count in explain). (edit: wrong idea with multicol index) Or You could add new column, which will be 0 when offer\_token is null and display\_toggle is null, and 1 when one of them is not null. Then You could add index to that column and search rows with “where new\_column = 1”. ---
**Author:** ![dude](https://avatars.discourse-cdn.com/v4/letter/d/f19dbf/32.png) [@dude](https://forums.percona.com/u/dude)\ **Post date:** [January 16, 2009, 7:18am UTC](https://forums.percona.com/t/mysql-crashes/1054/11 "2009-01-16T07:18:53Z")
| [B]Quote:[/B] | | Php will d/c from mysql when script is done, but You should disconnect after all work with database is done (there is no need to keep connection and mysql will be happier Wink ). | got it. | [B]Quote:[/B] | | Why "WHERE 1=1" ? | the sql query is generated dynamically by php, and there might be 0 or more sub conditions in the where close which start with an “and”… | [B]Quote:[/B] | | You could also force one of the single indexes: offer\_token\_idx or display\_idx (that one which gives less row count in explain). | I went ahead and fully removed those two conditions from the where clause, however the result is identical, so I’m guessing the problem lies somewhere else… I double checked and indexes are there, the joining columns are of the same type… really not sure… ---
**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\ **Post date:** [January 16, 2009, 7:25am UTC](https://forums.percona.com/t/mysql-crashes/1054/12 "2009-01-16T07:25:57Z")
The problem is that mysql have to fetch all records from offers table (or records that match one of criteria: offer\_token/display\_offer not null). Are You sure that You need all those offer\_id ? What do You do with those records after You fetch them ? ---
**Author:** ![dude](https://avatars.discourse-cdn.com/v4/letter/d/f19dbf/32.png) [@dude](https://forums.percona.com/u/dude)\ **Post date:** [January 16, 2009, 7:35am UTC](https://forums.percona.com/t/mysql-crashes/1054/13 "2009-01-16T07:35:41Z")
I display the results as a list and the list is paginated. The reason I don’t use the LIMIT clause is that, I would have to fetch another (full) resultset for paginator. So, I prefer to fetch a full resultset once, and sort it with php, and I also have records count for paginator. ---
**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\ **Post date:** [January 16, 2009, 7:59am UTC](https://forums.percona.com/t/mysql-crashes/1054/14 "2009-01-16T07:59:22Z")
Well, You could fetch only offer\_id from offers table, without joining other tables. Could You post paginator part ? Maybe there is something to improve in code. ---
**Author:** ![dude](https://avatars.discourse-cdn.com/v4/letter/d/f19dbf/32.png) [@dude](https://forums.percona.com/u/dude)\ **Post date:** [January 16, 2009, 8:43am UTC](https://forums.percona.com/t/mysql-crashes/1054/15 "2009-01-16T08:43:57Z")
Paginator uses the same resultset. I think I resolved it. I added a new conditional: where date\_publish \> timestamp (date\_publish is of integer type) I only wonder if I should add it also in the first join? explain select o.offer\_idfrom offers oleft join companies c ON (c.company\_id = o.company\_fid and o.date\_publish desc \> timestamp)left join offers2regions o2r ON (o2r.offer\_fid = o.offer\_id )left join countries co ON (co.country\_id = o.country\_fid)left join regions w ON (r.region\_id= o2r.region\_fid)WHERE o.date\_publish \> timestamporder by o.date\_publish desc; ---
**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\ **Post date:** [January 16, 2009, 9:47am UTC](https://forums.percona.com/t/mysql-crashes/1054/16 "2009-01-16T09:47:52Z")
Check both of them and decide which one is better.