SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 48
  1. #1
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql keep restarting

    Dear All,
    I have dedicated machine with 32G Ram and 16cpus and only running mysql.Below is my top during the day which is peak time and below is also the mysql config file. What I notice in between sometimes there is quite a number restarts happening. I dont get it why when I have so much of dedicated resources why does mysql goes down? Any reason for it? I am also looking into the slow query but I do have enough resources right?

    Top.

    top - 12:54:21 up 1 day, 23:31, 2 users, load average: 2.36, 2.00, 2.34
    Tasks: 278 total, 3 running, 275 sleeping, 0 stopped, 0 zombie
    Cpu(s): 2.0%us, 0.8%sy, 0.0%ni, 94.6%id, 0.2%wa, 0.6%hi, 1.8%si, 0.0%st
    Mem: 33009800k total, 22447692k used, 10562108k free, 200920k buffers
    Swap: 35061752k total, 0k used, 35061752k free, 18498676k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    3011 mysql 20 0 9899m 1.8g 4148 R 127.3 5.8 105:13.53 mysqld
    8763 root 20 0 14876 1176 776 R 2.0 0.0 0:00.01 top
    1 root 20 0 4080 856 608 S 0.0 0.0 0:01.58 init

    My.cnf

    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-innodb
    skip-bdb
    max_connections = 1000
    key_buffer = 8192M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 32M
    read_buffer_size = 2M
    sort_buffer_size = 4M
    table_cache = 2048
    thread_cache_size = 32
    wait_timeout = 200
    connect_timeout = 10
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 4096M
    query_cache_size = 1G
    query_cache_type = 1
    server-id=1283835628
    log-bin=mysql-bin
    log-error=mysql-bin.err
    binlog_do_db=fms,sms
    log-slow-queries = /var/log/mysql/mysql-slow.log
    long_query_time = 10
    log-queries-not-using-indexes
    log_warnings = 2

    [mysqld_safe]
    err-log=/var/log/mysqld.log
    open_files_limit = 10000

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [isamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M

    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M

    [mysql.server]
    #user=mysql

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Do you have any single particular query running each time MySQL is restarting?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Space,
    The problem here I have one web application and around 3000 gps devices all using this db. So I know from .err file when the times the db gets restarted and unfortunately the slow query log does not have the time stamp for me to further analyse and know which query is causing it. So any idea how to get to the query? Thank you.

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Do you have any cron jobs (scheduled tasks) due to run at the times that MySQL has been restarting?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Space,
    No I already check there is no cron job. So how to get to the root cause of this behavior?

  6. #6
    Twitter: @TimIgoe silver trophy TimIgoe's Avatar
    Join Date
    Feb 2005
    Location
    Blackpool, UK
    Posts
    1,058
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)
    Look into all the system logs - /var/log/messages or /var/log/syslog - the master logs in there may give more of a clue if something specifically is restrting mysql.

    It could be crashing, but you'd expect an error to be thrown in its error log (does /var/log/mysqld/ contain anything useful?)

    Top shows you aren't using all the ram by a long shot, so its not that you are running out of memory. CPU load is low too.

  7. #7
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Tim,
    Below is the latest contenr from /var/log/message and var/lib/mysql. I cant find syslog only the message. As you can see for 16/2/11 itself there is few restarts? I dont find anything in the message log to guide us further. How can I enable such that the mysql error log file to give more information rather than just say restart any idea?

    /var/log/message
    Feb 15 04:02:06 dbserver pcscd: winscard.c:302:SCardConnect() Reader E-Gate 0 0 Not Found
    Feb 15 04:02:06 dbserver pcscd: winscard.c:302:SCardConnect() Reader E-Gate 0 0 Not Found
    Feb 15 04:02:06 dbserver pcscd: winscard.c:302:SCardConnect() Reader E-Gate 0 0 Not Found
    Feb 15 04:02:06 dbserver pcscd: winscard.c:302:SCardConnect() Reader E-Gate 0 0 Not Found
    Feb 16 04:02:03 dbserver pcscd: winscard.c:302:SCardConnect() Reader E-Gate 0 0 Not Found
    Feb 16 04:02:03 dbserver pcscd: winscard.c:302:SCardConnect() Reader E-Gate 0 0 Not Found
    Feb 16 04:02:03 dbserver pcscd: winscard.c:302:SCardConnect() Reader E-Gate 0 0 Not Found
    Feb 16 04:02:03 dbserver pcscd: winscard.c:302:SCardConnect() Reader E-Gate 0 0 Not Found


    /var/lib/mysql

    110215 04:25:01 mysqld started
    110215 04:40:06 mysqld ended

    110215 04:40:07 mysqld started
    110216 00:47:54 mysqld ended

    110216 00:47:55 mysqld started
    110216 03:02:18 mysqld ended

    110216 03:02:19 mysqld started
    110216 03:31:49 mysqld ended

    110216 03:31:50 mysqld started
    110216 08:56:22 mysqld ended

    110216 08:56:22 mysqld started
    110216 11:32:05 mysqld ended

    110216 11:32:05 mysqld started
    110216 11:37:01 mysqld ended

    110216 11:37:02 mysqld started
    110216 17:45:20 mysqld ended

    110216 17:45:21 mysqld started

  8. #8
    Twitter: @TimIgoe silver trophy TimIgoe's Avatar
    Join Date
    Feb 2005
    Location
    Blackpool, UK
    Posts
    1,058
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)
    the pscsd errors are something else and,, assuming you aren't using it, can be ignored.

    the mysql log shows the service is being restarted and not crashing, you don't have any system monitoring or similar that might be killing mysql for you ? or a cron (scheduled event) that runs and restarts mysql when it shouldn't?

  9. #9
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Tom,
    Nope I don't any cron or monitoring utility running.so I would like to know why these
    Restarts is happening. Thx.

  10. #10
    Twitter: @TimIgoe silver trophy TimIgoe's Avatar
    Join Date
    Feb 2005
    Location
    Blackpool, UK
    Posts
    1,058
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)
    You really need to find out what is actually restarting the server, to me, from the logs pasted, it looks like its a genuine restart and not a crash restart.

  11. #11
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Tim,
    Just to share with you at times the web application which show "too many connections". So only at those instances we do restart db. How to know whether the too many connection is due to db or apache itself? Because in my mysql log file I dont notice this error too.

  12. #12
    Twitter: @TimIgoe silver trophy TimIgoe's Avatar
    Join Date
    Feb 2005
    Location
    Blackpool, UK
    Posts
    1,058
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)
    too many connections would imply that the server is under load and not processing the queries quick enough.

    Is the database stored on a raid array, single drive? Could this be a bottleneck?

    Have you checked the slow query log to find which queries need work to improve their performance? Have you tried tuning mysql to help with any queries / checking indexes on tables etc.

  13. #13
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Tim,
    I am not too sure about raid but I know it is stored only on a single drive. Yes I am also working on the slow log query still got many more queries need to be optimise too. But the question is I have so much of resources and why mysql cant take myload. For instance if you notice today's snippet of mysqld log file as below. If you notice the time it end and restart is so fast and we did not restart today the db server too. So is it a mysql bug itself ?


    110216 17:45:21 mysqld started
    110217 12:12:41 mysqld ended

    110217 12:12:41 mysqld started
    110217 14:03:15 mysqld ended

    110217 14:03:15 mysqld started
    110217 14:23:09 mysqld ended

    110217 14:23:10 mysqld started
    110217 15:06:58 mysqld ended

    110217 15:06:59 mysqld started

  14. #14
    Twitter: @TimIgoe silver trophy TimIgoe's Avatar
    Join Date
    Feb 2005
    Location
    Blackpool, UK
    Posts
    1,058
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)
    What version of mysql are you on, have you made sure you are running the latest stable version in your line? 5.0 / 5.1 / 5.5

  15. #15
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Tim,
    I am using the default which came with fedora that is 5.0.67. For us to move to new db will be quite big challenge cause our one table is consist of few hundred millions of lines. Before this mysql behave well. Off course resources should not a be a problem in my case right? When you said raid array means more thant one hard disk rite?

  16. #16
    Twitter: @TimIgoe silver trophy TimIgoe's Avatar
    Join Date
    Feb 2005
    Location
    Blackpool, UK
    Posts
    1,058
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)
    You have more than enough ram, if anything, you could increase the memory / connections of mysql without too many issues there, try pushing it up to 1250 connections and see how that behaves?

    RAID is indeed an array of multiple hard drives (0-striped, speed. 1-mirrored, redundancy. and others that combine both).

    If mysql was behaving ok until recently, what have you done that may increase load? collecting more data? increase of traffic to the front end?

  17. #17
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Tim,
    What else in the .cnf file do you think I can tune to over come this problem? Yes the load became higher become add more program which does complex select on the db. How to know if my server is indeed raid or not ? Thank you.

  18. #18
    Twitter: @TimIgoe silver trophy TimIgoe's Avatar
    Join Date
    Feb 2005
    Location
    Blackpool, UK
    Posts
    1,058
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)
    You'll know from what you purchased / how its been upgraded.

    If you've added a complex select recently, you should be looking into that and trying to work out if you can optimise it at all, use the EXPLAIN syntax on the query, this should help you.

  19. #19
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Tim,
    The problem is that the server is now on a remote location. Is there any linux command where I can know exactly how many physical hard disk is there? Actually raid is just about setting rite or do we have special hard disk just for raid. The problem we have add quite a number of new program and together with a lot of more new queries. My curiosity is that even with that additional mysql is showing it using very minimal resources but then why it fails?

  20. #20
    Twitter: @TimIgoe silver trophy TimIgoe's Avatar
    Join Date
    Feb 2005
    Location
    Blackpool, UK
    Posts
    1,058
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)
    RAID is about how the drives are set up, it could be software raid (LVM) or Hardware raid, in which case Linux would see the raid drive presented rather tahn the pile of drives that make it up.

    Mysql is using resources, however, its possible you've got ONE query thats locking tables, in that case connections build up until you run out of spare connections which causes the server to appear to fail. You really need to be looking at what the server is doing when it starts to struggle, rather than just resetting it.

  21. #21
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Tim,
    So my guess is that when it comes to RAID is normally more than one hardissk as one is the master and the rest are keeping the lastest copy of the db is it true? Ok when you say that once the query lock the table connection build up I dont quite get you here. I thought that once I make one connection and query the a particular table then how will the connection grow. I am kind of confuse here. Yes I agree I dont want to just reset as that is not the solution and struggling to find the solution. Thank you.

  22. #22
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Try having the server-side language your using log to a file, whatever query is being sent to MySQL when MySQL isn't responding.

    How does the app your connect to MySQL, does it create many connections or share a single connection around? Does it create temporary or permanent connections to MySQL?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  23. #23
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Space,
    I dont get you how to log only when the server is log responding? Any method for that specifically. One is the web application and here I have config file and keep the details here as below.

    <?php
    error_reporting(0);
    define('DB_HOST', ******);
    define('DB_USER', '***');
    define('DB_PASSWORD', '****);
    define('DB_DATABASE', '*****');
    ?>

    Then in other files I called it like this $dbconn = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); and finall close it like this mysql_close(); . Then I have java listener which keep opening the db connection and close it later too. So I dont know how you consider this as single connection and permanent?

  24. #24
    Twitter: @TimIgoe silver trophy TimIgoe's Avatar
    Join Date
    Feb 2005
    Location
    Blackpool, UK
    Posts
    1,058
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)
    Hold on, where does the Java listener come into the PHP accessing the database?! Is it possible the Java listener isn't closing connections so its eating all the spare ones after a period?

    RAID is more than 1 drive, yes, it can be a 'large' disk that spans all drives, or a mirror (or a combination of both).

    With regards to the query locking, if i run a complex query against a database, I can gain locks on that table - if another user wants to query the same table, it has to wait for me to free my locks, adding them to a queue to wait for a table - they'll kjeep waiting until its free.

  25. #25
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Tim,
    Ok let me clearly explain it. My application is a gps application where in the background around 3000 devices are inserting and updating data into my db.Java listener is working here to capture the data and store into the db. On the front end is the php based web application where the user are viewing the gps data and also generate various reports.
    What do you mean by 'large' disk that spans all drives? Drive here mean single hard disk is it?
    So when a user have locked a particular table the rest will be waiting in the queue will this lead to number of connection problems? Thank you.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •