mysql crashes

MySQL & MariaDB Other MySQL® Questions
1

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

2

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 ?
3

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

4

dude, paste those logs, please

5

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]), 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.

6

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.

7

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…)

8

You could set names only at one point: after You connect to the database, eg:

[B]Quote:[/B]
<? connect set names do all the things display page content disconnect ?>

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] - 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)
9

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

10

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: ).

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”.

11
[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…

12

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 ?

13

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.

14

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.

15

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;

16

Check both of them and decide which one is better.