Professional Web Applications Themes

Slow query, yes, but it is such a simple table. - MySQL

Removed by Administrator...

  1. Moderated Post

    Default Slow query, yes, but it is such a simple table.

    Removed by Administrator
    FFMG Guest
    Moderated Post

  2. Moderated Post

    Default Re: Slow query, yes, but it is such a simple table.

    Removed by Administrator
    subtenante Guest
    Moderated Post

  3. #3

    Default Re: Slow query, yes, but it is such a simple table.


    subtenante;78196 Wrote: 

    Well, I have the default values set.
    And not all the queries appear in the log.

    I am not sure what the time is supposed to show as most of my queries
    are '0' or '1'

    FFMG


    --

    'webmaster forum' (http://www.httppoint.com) | 'webmaster Directory'
    (http://www.webhostshunter.com/) | 'Recreation Vehicle insurance'
    (http://www.insurance-owl.com/other/car_rec.php)
    'Free URL redirection service' (http://urlkick.com/)
    ------------------------------------------------------------------------
    FFMG's Profile: http://www.httppoint.com/member.php?userid=580
    View this thread: http://www.httppoint.com/showthread.php?t=17789

    Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).

    FFMG Guest

  4. #4

    Default Re: Slow query, yes, but it is such a simple table.

    On Mon, 25 Jun 2007 07:04:13 -0400, FFMG
    <httppoint.com> wrote:
     
    >
    >Well, I have the default values set.
    >And not all the queries appear in the log.
    >
    >I am not sure what the time is supposed to show as most of my queries
    >are '0' or '1'
    >
    >FFMG[/ref]

    *sigh* :'(
    I was just so happy to make a good joke this time.
    But hey !

    5.12.4. The Slow Query Log
    The slow query log consists of all SQL statements that took more than
    long_query_time seconds to execute. The time to acquire the initial
    table locks is not counted as execution time. The minimum and default
    values of long_query_time are 1 and 10, respectively.

    Minimum value=1, you log value=0... We really need an expert there.
    subtenante Guest

  5. #5

    Default Re: Slow query, yes, but it is such a simple table.

    > 5.12.4. The Slow Query Log 

    (See also http://dev.mysql.com/doc/refman/5.1/en/server-options.html)

    Is the --log-queries-not-using-indexes set? Then it still would surprise
    me that such a query ends up in the slow query log, as there is a
    primary key defined. If the optimizer want a key, there is one.

    Just curious: does it help to switch the above option off (if it was set)?

    Best regards,
    --
    Willem Bogaerts

    Application smith
    Kratz B.V.
    http://www.kratz.nl/
    Willem Guest

  6. #6

    Default Re: Slow query, yes, but it is such a simple table.

    Sorry to follow up on my own post, but this site gives an answer, I think...

    http://www.mysqlperformanceblog.com/2006/09/06/slow-query-log-yzes-tools/

    --
    Willem Bogaerts

    Application smith
    Kratz B.V.
    http://www.kratz.nl/
    Willem Guest

  7. #7

    Default Re: Slow query, yes, but it is such a simple table.


    subtenante;78212 Wrote: 

    Sorry, my bad for missing that one, I was half asleep :).

    FFMG


    --

    'webmaster forum' (http://www.httppoint.com) | 'webmaster Directory'
    (http://www.webhostshunter.com/) | 'Recreation Vehicle insurance'
    (http://www.insurance-owl.com/other/car_rec.php)
    'Free URL redirection service' (http://urlkick.com/)
    ------------------------------------------------------------------------
    FFMG's Profile: http://www.httppoint.com/member.php?userid=580
    View this thread: http://www.httppoint.com/showthread.php?t=17789

    Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).

    FFMG Guest

  8. #8

    Default Re: Slow query, yes, but it is such a simple table.

    On Mon, 25 Jun 2007 14:41:39 +0200, Willem Bogaerts
    <maardanzonderditstuk.nl> wrote:
     

    The question is now : how can the slow query log log a query that is
    less slow than the least slow query it should log, *before* our friend
    applied the patch. (Without touching anything it should be by default
    10 seconds.)

    I'm afraid to ask it, but... is it really the slow query log ?
    subtenante Guest

  9. #9

    Default Re: Slow query, yes, but it is such a simple table.

    >> http://www.mysqlperformanceblog.com/2006/09/06/slow-query-log-yzes-tools/ 

    I had to read the above sentence a few times, but it does make sense.

    There are two settings here: a minimum execution time (which is patched
    by the page at the above link) and a setting that marks all full table
    scans as "slow".

    This does make sense also: A full table scan can become slow very
    rapidly on "live data". It is nice that MySQL sees this is a very early
    stage, before it becomes a real problem.
    The problem is that a full table scan will not become slow on a
    "definition table" that barely grows. So this is a false alarm.

    It would make sense to set the --log-queries-not-using-indexes option ON
    on a development machine or a test server, and OFF on a live server. But
    you may want to leave the minimum execution time ON on a live server, to
    be alerted on performance issues when the database grows.

    Best regards,
    --
    Willem Bogaerts

    Application smith
    Kratz B.V.
    http://www.kratz.nl/
    Willem Guest

  10. #10

    Default Re: Slow query, yes, but it is such a simple table.

    On Mon, 25 Jun 2007 16:41:53 +0200, Willem Bogaerts
    <maardanzonderditstuk.nl> wrote:
     
    >>
    >> The question is now : how can the slow query log log a query that is
    >> less slow than the least slow query it should log, *before* our friend
    >> applied the patch. (Without touching anything it should be by default
    >> 10 seconds.)[/ref][/ref]
     

    Full table scans are pretty common, a
    mysql -u ... -p... -e "SELECT * FROM blabla" > dummy.file
    on a table of 90000 rows, 5Mb, takes less than one second on my
    machine.
    His query returned 14 rows... If that is considered slow, it's the
    Speedy Gonzalez release he downloaded from MySQL.

    Besides i'm really not convinced MySQL is as smart as you say here :
    if it were, the link you give there wouldn't exist, for MySQL would
    already do the job without the patch.
     

    Even if it grows, if the expected output is above 10% of the size of
    the table, the optimizer goes for a full table scan: faster. A slow
    select involes joins, unions, joined unions and spaghetti design. Or a
    286.

    I would say there is a trick somewhere, a config file that defines a
    slow query log and overdefines it as another log somewhere else.
    Or else his MySQL has a bug.
    Or it's on acid.
    subtenante Guest

  11. #11

    Default Re: Slow query, yes, but it is such a simple table.

    On Mon, 25 Jun 2007 09:56:37 -0400, FFMG
    <httppoint.com> wrote:
     

    No offense, first time i didn't notice myself :).
    subtenante Guest

  12. #12

    Default Re: Slow query, yes, but it is such a simple table.

    subtenante <com> wrote: [/ref]
    > [/ref]

    No need to guess. Indeed MySQL logs *all* full table scans to the slow
    query log when log-queries-not-using-indexes is effective. Regardless
    of how long the execution time is or how many rows there are.

    The beforementioned patch contains another goodie:
    min_examined_row_limit - set the minimum number of rows for a full
    table scan to be logged into the slow query log. This can be used to
    suppress false positives from log-queries-not-using-indexes.
     

    This query is not at all common. And on a already loaded database it
    would probably take much longer, because of I/O contention.
     

    Just FYI: this patch *is* from MySQL developers. It just didn't make it
    into the product for various reasons.

    [For the interested: the mentioned patch was quick'n dirty solution
    for http://forge.mysql.com/worklog/task.php?id=1277 but has been
    suspended in favour of a more complete query profiling solution]


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

Similar Threads

  1. Slow Query Help
    By megalith in forum Coldfusion Database Access
    Replies: 7
    Last Post: October 12th, 04:36 PM
  2. slow query on big table
    By lambelly@gmail.com in forum MySQL
    Replies: 5
    Last Post: September 15th, 04:46 AM
  3. Slow Query
    By Stuart in forum Informix
    Replies: 3
    Last Post: December 8th, 02:28 PM
  4. No blocking, but simple queries run slow
    By Tom in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: August 9th, 05:21 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139