MySQL: How to troubleshoot when simple queries are not returning anymore?












0















This InnoDB database has been up for a couple years now but in the last 24h it has experienced the same issue twice; I have to restart the mysqld service to make it respond to queries again.



When it happens, I can use a client to login but when I run simple queries they run forever and don't return, or would eventually return but I dont know after how much time.



mysqld.log shows nothing important and when I kill my SQL query I get: 160712 7:41:36 [ERROR] /usr/libexec/mysqld: Sort aborted, which I think is normal.



Disk space is fine, plenty of space. CPUs are fine too.



Basic mysqlcheck on all databases all returned OK.



mysqltuner results are the following one hour after being restarted:



[OK] Currently running supported MySQL version 5.1.71-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 4K (Tables: 2)
[--] Data in InnoDB tables: 327M (Tables: 222)
[!!] Total fragmented tables: 12

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 8m 4s (12K q [2.990 qps], 2K conn, TX: 4M, RX: 842K)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 6.2G global + 14.2M per thread (500 max threads)
[OK] Maximum possible memory usage: 13.2G (84% of installed RAM)
[OK] Slow queries: 0% (0/12K)
[OK] Highest usage of available connections: 1% (8/500)
[OK] Key buffer size / total MyISAM indexes: 128.0M/102.0K
[!!] Key buffer hit rate: 81.2% (48 cached / 9 reads)
[OK] Query cache efficiency: 91.0% (4K cached / 5K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 139 sorts)
[!!] Joins performed without indexes: 16
[OK] Temporary tables created on disk: 0% (0 on disk / 9 total)
[OK] Thread cache hit rate: 99% (8 created / 2K connections)
[OK] Table cache hit rate: 97% (230 open / 237 opened)
[OK] Open file limit used: 0% (25/65K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB buffer pool / data size: 6.0G/327.7M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 2.0M, or always use indexes with joins)


I see some tables are fragmented, is it something I should be checking?
What else should I be doing/looking for?



Thank you in advance










share|improve this question














bumped to the homepage by Community 11 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.






migrated from stackoverflow.com Jul 12 '16 at 13:41


This question came from our site for professional and enthusiast programmers.



















  • This question has nothing to do with sw development, this is a pure operational issue. DBAs would be in a lot better position to answer your question. Therefore I'm voting to close this question on SO and migrate it to the dba site.

    – Shadow
    Jul 12 '16 at 13:09
















0















This InnoDB database has been up for a couple years now but in the last 24h it has experienced the same issue twice; I have to restart the mysqld service to make it respond to queries again.



When it happens, I can use a client to login but when I run simple queries they run forever and don't return, or would eventually return but I dont know after how much time.



mysqld.log shows nothing important and when I kill my SQL query I get: 160712 7:41:36 [ERROR] /usr/libexec/mysqld: Sort aborted, which I think is normal.



Disk space is fine, plenty of space. CPUs are fine too.



Basic mysqlcheck on all databases all returned OK.



mysqltuner results are the following one hour after being restarted:



[OK] Currently running supported MySQL version 5.1.71-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 4K (Tables: 2)
[--] Data in InnoDB tables: 327M (Tables: 222)
[!!] Total fragmented tables: 12

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 8m 4s (12K q [2.990 qps], 2K conn, TX: 4M, RX: 842K)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 6.2G global + 14.2M per thread (500 max threads)
[OK] Maximum possible memory usage: 13.2G (84% of installed RAM)
[OK] Slow queries: 0% (0/12K)
[OK] Highest usage of available connections: 1% (8/500)
[OK] Key buffer size / total MyISAM indexes: 128.0M/102.0K
[!!] Key buffer hit rate: 81.2% (48 cached / 9 reads)
[OK] Query cache efficiency: 91.0% (4K cached / 5K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 139 sorts)
[!!] Joins performed without indexes: 16
[OK] Temporary tables created on disk: 0% (0 on disk / 9 total)
[OK] Thread cache hit rate: 99% (8 created / 2K connections)
[OK] Table cache hit rate: 97% (230 open / 237 opened)
[OK] Open file limit used: 0% (25/65K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB buffer pool / data size: 6.0G/327.7M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 2.0M, or always use indexes with joins)


I see some tables are fragmented, is it something I should be checking?
What else should I be doing/looking for?



Thank you in advance










share|improve this question














bumped to the homepage by Community 11 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.






migrated from stackoverflow.com Jul 12 '16 at 13:41


This question came from our site for professional and enthusiast programmers.



















  • This question has nothing to do with sw development, this is a pure operational issue. DBAs would be in a lot better position to answer your question. Therefore I'm voting to close this question on SO and migrate it to the dba site.

    – Shadow
    Jul 12 '16 at 13:09














0












0








0


1






This InnoDB database has been up for a couple years now but in the last 24h it has experienced the same issue twice; I have to restart the mysqld service to make it respond to queries again.



When it happens, I can use a client to login but when I run simple queries they run forever and don't return, or would eventually return but I dont know after how much time.



mysqld.log shows nothing important and when I kill my SQL query I get: 160712 7:41:36 [ERROR] /usr/libexec/mysqld: Sort aborted, which I think is normal.



Disk space is fine, plenty of space. CPUs are fine too.



Basic mysqlcheck on all databases all returned OK.



mysqltuner results are the following one hour after being restarted:



[OK] Currently running supported MySQL version 5.1.71-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 4K (Tables: 2)
[--] Data in InnoDB tables: 327M (Tables: 222)
[!!] Total fragmented tables: 12

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 8m 4s (12K q [2.990 qps], 2K conn, TX: 4M, RX: 842K)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 6.2G global + 14.2M per thread (500 max threads)
[OK] Maximum possible memory usage: 13.2G (84% of installed RAM)
[OK] Slow queries: 0% (0/12K)
[OK] Highest usage of available connections: 1% (8/500)
[OK] Key buffer size / total MyISAM indexes: 128.0M/102.0K
[!!] Key buffer hit rate: 81.2% (48 cached / 9 reads)
[OK] Query cache efficiency: 91.0% (4K cached / 5K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 139 sorts)
[!!] Joins performed without indexes: 16
[OK] Temporary tables created on disk: 0% (0 on disk / 9 total)
[OK] Thread cache hit rate: 99% (8 created / 2K connections)
[OK] Table cache hit rate: 97% (230 open / 237 opened)
[OK] Open file limit used: 0% (25/65K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB buffer pool / data size: 6.0G/327.7M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 2.0M, or always use indexes with joins)


I see some tables are fragmented, is it something I should be checking?
What else should I be doing/looking for?



Thank you in advance










share|improve this question














This InnoDB database has been up for a couple years now but in the last 24h it has experienced the same issue twice; I have to restart the mysqld service to make it respond to queries again.



When it happens, I can use a client to login but when I run simple queries they run forever and don't return, or would eventually return but I dont know after how much time.



mysqld.log shows nothing important and when I kill my SQL query I get: 160712 7:41:36 [ERROR] /usr/libexec/mysqld: Sort aborted, which I think is normal.



Disk space is fine, plenty of space. CPUs are fine too.



Basic mysqlcheck on all databases all returned OK.



mysqltuner results are the following one hour after being restarted:



[OK] Currently running supported MySQL version 5.1.71-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 4K (Tables: 2)
[--] Data in InnoDB tables: 327M (Tables: 222)
[!!] Total fragmented tables: 12

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 8m 4s (12K q [2.990 qps], 2K conn, TX: 4M, RX: 842K)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 6.2G global + 14.2M per thread (500 max threads)
[OK] Maximum possible memory usage: 13.2G (84% of installed RAM)
[OK] Slow queries: 0% (0/12K)
[OK] Highest usage of available connections: 1% (8/500)
[OK] Key buffer size / total MyISAM indexes: 128.0M/102.0K
[!!] Key buffer hit rate: 81.2% (48 cached / 9 reads)
[OK] Query cache efficiency: 91.0% (4K cached / 5K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 139 sorts)
[!!] Joins performed without indexes: 16
[OK] Temporary tables created on disk: 0% (0 on disk / 9 total)
[OK] Thread cache hit rate: 99% (8 created / 2K connections)
[OK] Table cache hit rate: 97% (230 open / 237 opened)
[OK] Open file limit used: 0% (25/65K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB buffer pool / data size: 6.0G/327.7M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 2.0M, or always use indexes with joins)


I see some tables are fragmented, is it something I should be checking?
What else should I be doing/looking for?



Thank you in advance







mysql






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Jul 12 '16 at 12:56







Questionz












bumped to the homepage by Community 11 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 11 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.






migrated from stackoverflow.com Jul 12 '16 at 13:41


This question came from our site for professional and enthusiast programmers.









migrated from stackoverflow.com Jul 12 '16 at 13:41


This question came from our site for professional and enthusiast programmers.















  • This question has nothing to do with sw development, this is a pure operational issue. DBAs would be in a lot better position to answer your question. Therefore I'm voting to close this question on SO and migrate it to the dba site.

    – Shadow
    Jul 12 '16 at 13:09



















  • This question has nothing to do with sw development, this is a pure operational issue. DBAs would be in a lot better position to answer your question. Therefore I'm voting to close this question on SO and migrate it to the dba site.

    – Shadow
    Jul 12 '16 at 13:09

















This question has nothing to do with sw development, this is a pure operational issue. DBAs would be in a lot better position to answer your question. Therefore I'm voting to close this question on SO and migrate it to the dba site.

– Shadow
Jul 12 '16 at 13:09





This question has nothing to do with sw development, this is a pure operational issue. DBAs would be in a lot better position to answer your question. Therefore I'm voting to close this question on SO and migrate it to the dba site.

– Shadow
Jul 12 '16 at 13:09










1 Answer
1






active

oldest

votes


















0














You are hardly using MyISAM, so ignore the issues about MyISAM.



Ignore the recommendation to OPTIMIZE and 'fragmented'; that tool always says that, even if it is of no use (which is usually the case).



Joins without indexes -- you are better off looking at any 'slow' queries.



Find the slow queries. First try SHOW FULL PROCESSLIST when it "hangs" to see if you can catch a query. Another thing to do is to turn on the Slowlog, run for a day, then use pt-query-digest to summarize the slowlog. Let's look at the first couple of queries.



"Sort aborted" sounds like a poorly written query; the PROCESSLIST should show it before killing it. Perhaps it is a JOIN without an ON.






share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f143605%2fmysql-how-to-troubleshoot-when-simple-queries-are-not-returning-anymore%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown
























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    You are hardly using MyISAM, so ignore the issues about MyISAM.



    Ignore the recommendation to OPTIMIZE and 'fragmented'; that tool always says that, even if it is of no use (which is usually the case).



    Joins without indexes -- you are better off looking at any 'slow' queries.



    Find the slow queries. First try SHOW FULL PROCESSLIST when it "hangs" to see if you can catch a query. Another thing to do is to turn on the Slowlog, run for a day, then use pt-query-digest to summarize the slowlog. Let's look at the first couple of queries.



    "Sort aborted" sounds like a poorly written query; the PROCESSLIST should show it before killing it. Perhaps it is a JOIN without an ON.






    share|improve this answer




























      0














      You are hardly using MyISAM, so ignore the issues about MyISAM.



      Ignore the recommendation to OPTIMIZE and 'fragmented'; that tool always says that, even if it is of no use (which is usually the case).



      Joins without indexes -- you are better off looking at any 'slow' queries.



      Find the slow queries. First try SHOW FULL PROCESSLIST when it "hangs" to see if you can catch a query. Another thing to do is to turn on the Slowlog, run for a day, then use pt-query-digest to summarize the slowlog. Let's look at the first couple of queries.



      "Sort aborted" sounds like a poorly written query; the PROCESSLIST should show it before killing it. Perhaps it is a JOIN without an ON.






      share|improve this answer


























        0












        0








        0







        You are hardly using MyISAM, so ignore the issues about MyISAM.



        Ignore the recommendation to OPTIMIZE and 'fragmented'; that tool always says that, even if it is of no use (which is usually the case).



        Joins without indexes -- you are better off looking at any 'slow' queries.



        Find the slow queries. First try SHOW FULL PROCESSLIST when it "hangs" to see if you can catch a query. Another thing to do is to turn on the Slowlog, run for a day, then use pt-query-digest to summarize the slowlog. Let's look at the first couple of queries.



        "Sort aborted" sounds like a poorly written query; the PROCESSLIST should show it before killing it. Perhaps it is a JOIN without an ON.






        share|improve this answer













        You are hardly using MyISAM, so ignore the issues about MyISAM.



        Ignore the recommendation to OPTIMIZE and 'fragmented'; that tool always says that, even if it is of no use (which is usually the case).



        Joins without indexes -- you are better off looking at any 'slow' queries.



        Find the slow queries. First try SHOW FULL PROCESSLIST when it "hangs" to see if you can catch a query. Another thing to do is to turn on the Slowlog, run for a day, then use pt-query-digest to summarize the slowlog. Let's look at the first couple of queries.



        "Sort aborted" sounds like a poorly written query; the PROCESSLIST should show it before killing it. Perhaps it is a JOIN without an ON.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Jul 15 '16 at 2:45









        Rick JamesRick James

        43.5k22259




        43.5k22259






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f143605%2fmysql-how-to-troubleshoot-when-simple-queries-are-not-returning-anymore%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh