Results 1 to 8 of 8

Thread: mysql logger problem

  1. #1
    dijichi2 is offline OpenSource Builder & Moderator
    Join Date
    Oct 2005
    Posts
    1,176
    Rep Power
    12

    Default mysql logger problem

    hello

    i've upgraded an rhel4 box from m2-740 (i think, can't remember exactly) to m3-381. shortly after starting (or restarting) zimbra, i get mysqld running at 99% cpu, and lots of entries in logger_myslow.log like:

    # Time: 060105 2:12:17
    # User@Host: zimbra[zimbra] @ localhost []
    # Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 504206
    select avg(total) from disk_aggregate where host = 'mail.radiatormail.co.uk' and device = '/dev/hda5' and period_start >= '2005-12-30 00:00:00' and period_end <= '2005-12-31 00:00:00';

    they seem to be mainly about hdax, although a few other queries too. does this indicate some queries that aren't indexed correctly? how come they're so frequent? they seem to be constant, a tail -f shows them several times per second.

  2. #2
    Join Date
    Sep 2005
    Posts
    2,103
    Rep Power
    14

    Default indexes

    Gak. Look like I'm not indexing that table - I'll file a bug on this.

    You could add them yourself (I'd index device, period_start and period end) or wait for the patch. (Or turn off stats aggregation...)

  3. #3
    dijichi2 is offline OpenSource Builder & Moderator
    Join Date
    Oct 2005
    Posts
    1,176
    Rep Power
    12

    Default

    added indexes i_device, i_period_start and i_period_end on disk_aggregate. done the trick nicely.

    thanks again!!

  4. #4
    dijichi2 is offline OpenSource Builder & Moderator
    Join Date
    Oct 2005
    Posts
    1,176
    Rep Power
    12

    Default

    seems to need index on host field too

  5. #5
    dijichi2 is offline OpenSource Builder & Moderator
    Join Date
    Oct 2005
    Posts
    1,176
    Rep Power
    12

    Default

    hmm, i'm still seeing prolongued 99% cpu on mysqld

    lots of these entries in logger_myslow.log:

    # Time: 060105 14:05:24
    # User@Host: zimbra[zimbra] @ localhost []
    # Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 169018
    select avg(total) from disk_aggregate where host = 'mail.radiatormail.co.uk' and device = '/dev/hda5' and period_start >= '2005-12-11 00:00:00' and period_end <= '2005-12-12 00:00:00';

    i have these indexes:

    +----------------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +----------------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
    | disk_aggregate | 1 | i_period_start | 1 | period_start | A | 1091 | NULL | NULL | | BTREE | |
    | disk_aggregate | 1 | i_period_end | 1 | period_end | A | 1091 | NULL | NULL | | BTREE | |
    | disk_aggregate | 1 | i_device | 1 | device | A | 3 | NULL | NULL | YES | BTREE | |
    | disk_aggregate | 1 | i_host | 1 | host | A | 1 | NULL | NULL | | BTREE | |
    +----------------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

  6. #6
    Join Date
    Sep 2005
    Posts
    2,103
    Rep Power
    14

    Default bug filed

    bug 5264 filed on this, I'll take a look at what I can do to speed this up.

  7. #7
    Join Date
    Nov 2005
    Posts
    42
    Rep Power
    10

    Default

    Quote Originally Posted by marcmac
    bug 5264 filed on this, I'll take a look at what I can do to speed this up.
    Could you provide a sql script to fix this? I'm going to disable the logger until this is fixed.

  8. #8
    Join Date
    Nov 2005
    Posts
    42
    Rep Power
    10

    Default

    Quote Originally Posted by manthrax3
    Could you provide a sql script to fix this? I'm going to disable the logger until this is fixed.
    btw, I'm using M3.

Similar Threads

  1. Zimbra fails after working for 2 weeks
    By Linsys in forum Administrators
    Replies: 10
    Last Post: 10-07-2008, 01:42 AM
  2. Multi-server install - logger problem
    By mrfileio in forum Administrators
    Replies: 1
    Last Post: 11-29-2006, 06:53 AM
  3. Missing MySQL logger root password
    By kevindods in forum Installation
    Replies: 14
    Last Post: 06-26-2006, 04:31 AM
  4. Logger MySQL db Root password error
    By MACaid in forum Administrators
    Replies: 2
    Last Post: 06-14-2006, 03:06 PM
  5. Can't start MySQL to init db - Gentoo
    By fak3r in forum Installation
    Replies: 3
    Last Post: 10-10-2005, 07:59 PM

Posting Permissions

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