Announcement

Announcement Module
Collapse
No announcement yet.

mysql crashes

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql crashes

    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
    Quote:


    cat /var/log/mysql/mysql-new6.err


    shows ?

    Quote:


    less /var/log/syslog | grep mysql


    and this ?

    Comment


    • #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

      Comment


      • #4
        dude, paste those logs, please

        Comment


        • #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 (http://hackmysql.com/mysqlreportguid...ons_report:dtq), not a good thing.
          What is com_set_option ?

          Here someone has a similar issue:

          http://forum.percona.com/s/mv/tree/289/

          Comment


          • #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.

            Comment


            • #7
              januzi,

              Quote:

              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?

              Quote:


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

              Comment


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

                Quote:


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

                http://hackmysql.com/mysqlsla - 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)

                Comment


                • #9
                  thanks Januzi,

                  is it necessary to disconnect when working with php?

                  Quote:

                  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.


                  Quote:

                  http://hackmysql.com/mysqlsla - 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

                  Comment


                  • #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 ).

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

                    Comment


                    • #11
                      Quote:

                      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.

                      Quote:

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

                      Quote:

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

                      Comment


                      • #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 ?

                        Comment


                        • #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.

                          Comment


                          • #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.

                            Comment


                            • #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;

                              Comment

                              Working...
                              X