MySQL threads taking time in "statistics" state

--00163630fd534d8710048520d5d5
Content-Type: text/plain; charset=ISO-8859-1

We have installed mysql-5.1.39 and having a database with following table.

CREATE TABLE `EntMsgLog` (
`msgId` bigint(20) NOT NULL,
`causeId` bigint(20) NOT NULL,
`entityId` int(11) NOT NULL,
`msgReceiver` bigint(20) NOT NULL,
`msgTextId` int(11) NOT NULL,
`flags` bit(8) NOT NULL,
`timeStamp` bigint(20) NOT NULL,
`credits` float NOT NULL,
UNIQUE KEY `causeId` (`causeId`,`msgId`,`timeStamp`),
KEY `entityId` (`entityId`),
KEY `msgReceiver` (`msgReceiver`),
KEY `timeStamp` (`timeStamp`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (timeStamp)
(PARTITION p01042010 VALUES LESS THAN (1270146600000) ENGINE = MyISAM,
PARTITION p02042010 VALUES LESS THAN (1270233000000) ENGINE = MyISAM,
PARTITION p03042010 VALUES LESS THAN (1270319400000) ENGINE = MyISAM,
------------------
60 such partitions..
----
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ |

my query is following format:
mysql> explain select * from EntMsgLog where causeId= 659824157048176974 and
msgId = 143168093266866137;
+----+-------------+-----------+------+---------------+----- ----+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+-----------+------+---------------+----- ----+---------+-------------+------+-------+
| 1 | SIMPLE | EntMsgLog | ref | causeId | causeId | 16 |
const,const | 62 | |
+----+-------------+-----------+------+---------------+----- ----+---------+-------------+------+-------+


This query is taking 1-2 sec. to execute and after profiling the query, I
found query is taking 90-95% time in "statistics" state. Please let me know
what I am doing wrong.

-Dheeraj

--00163630fd534d8710048520d5d5--

Re: MySQL threads taking time in "statistics" state

Hi,

You probably aren't doing anything wrong, per se, but I suspect
Handler::info() is slow and is being called once per partition. You
should probably start looking at your system overall to check where
the time is spent. Is it in reading from disk? If so, can you make
it read from memory instead, or if that's not possible, are your disks
slower than they should be...? and so on. What OS are you running?

On Mon, Apr 26, 2010 at 5:59 AM, Dheeraj Kumar wrote:
> We have installed mysql-5.1.39 and having a database with following table=
..
>
> =A0CREATE TABLE `EntMsgLog` (
> =A0`msgId` bigint(20) NOT NULL,
> =A0`causeId` bigint(20) NOT NULL,
> =A0`entityId` int(11) NOT NULL,
> =A0`msgReceiver` bigint(20) NOT NULL,
> =A0`msgTextId` int(11) NOT NULL,
> =A0`flags` bit(8) NOT NULL,
> =A0`timeStamp` bigint(20) NOT NULL,
> =A0`credits` float NOT NULL,
> =A0UNIQUE KEY `causeId` (`causeId`,`msgId`,`timeStamp`),
> =A0KEY `entityId` (`entityId`),
> =A0KEY `msgReceiver` (`msgReceiver`),
> =A0KEY `timeStamp` (`timeStamp`)
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8
> /*!50100 PARTITION BY RANGE (timeStamp)
> (PARTITION p01042010 VALUES LESS THAN (1270146600000) ENGINE =3D MyISAM,
> =A0PARTITION p02042010 VALUES LESS THAN (1270233000000) ENGINE =3D MyISAM=
,
> =A0PARTITION p03042010 VALUES LESS THAN (1270319400000) ENGINE =3D MyISAM=
,
> ------------------
> 60 such partitions..
> ----
> PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE =3D MyISAM) */ |
>
> my query is following format:
> mysql> explain select * from EntMsgLog where causeId=3D 65982415704817697=
4 and
> msgId =3D 143168093266866137;
> +----+-------------+-----------+------+---------------+----- ----+--------=
-+-------------+------+-------+
> | id | select_type | table =A0 =A0 | type | possible_keys | key =A0 =A0 |=
key_len |
> ref =A0 =A0 =A0 =A0 | rows | Extra |
> +----+-------------+-----------+------+---------------+----- ----+--------=
-+-------------+------+-------+
> | =A01 | SIMPLE =A0 =A0 =A0| EntMsgLog | ref =A0| causeId =A0 =A0 =A0 | c=
auseId | 16 =A0 =A0 =A0|
> const,const | =A0 62 | =A0 =A0 =A0 |
> +----+-------------+-----------+------+---------------+----- ----+--------=
-+-------------+------+-------+
>
>
> This query is taking 1-2 sec. to execute and after profiling the query, I
> found query is taking 90-95% time in "statistics" state. =A0Please let me=
know
> what I am doing wrong.
>
> -Dheeraj
>



--=20
Baron Schwartz
Percona Inc
Consulting, Training, Support & Services for MySQL

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MySQL threads taking time in "statistics" state

--00163630f0b5cb0b56048547b43f
Content-Type: text/plain; charset=ISO-8859-1

Hi Baron,
I am too new to mysql internal jargon to understand "Handler:info()". Can
you tell me specific variables you want to get the real issue?

*Regarding OS:* It ubuntu disto. of linux.
dheeraj@XXXX:~$ uname -a
Linux host0125 2.6.24-16-generic #1 SMP Thu Apr 10 12:47:45 UTC 2008 x86_64
GNU/Linux

*Regarding Hardware:*
Plus, it is 16GB RAM with 13.2 GB alloted to mysql and total index size of
all tables is 7-8 GB. CPU is quad-core.

-Thanks in Advance,
Dheeraj

On Mon, Apr 26, 2010 at 7:22 PM, Baron Schwartz wrote:

> Hi,
>
> You probably aren't doing anything wrong, per se, but I suspect
> Handler::info() is slow and is being called once per partition. You
> should probably start looking at your system overall to check where
> the time is spent. Is it in reading from disk? If so, can you make
> it read from memory instead, or if that's not possible, are your disks
> slower than they should be...? and so on. What OS are you running?
>
> On Mon, Apr 26, 2010 at 5:59 AM, Dheeraj Kumar wrote:
> > We have installed mysql-5.1.39 and having a database with following
> table.
> >
> > CREATE TABLE `EntMsgLog` (
> > `msgId` bigint(20) NOT NULL,
> > `causeId` bigint(20) NOT NULL,
> > `entityId` int(11) NOT NULL,
> > `msgReceiver` bigint(20) NOT NULL,
> > `msgTextId` int(11) NOT NULL,
> > `flags` bit(8) NOT NULL,
> > `timeStamp` bigint(20) NOT NULL,
> > `credits` float NOT NULL,
> > UNIQUE KEY `causeId` (`causeId`,`msgId`,`timeStamp`),
> > KEY `entityId` (`entityId`),
> > KEY `msgReceiver` (`msgReceiver`),
> > KEY `timeStamp` (`timeStamp`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=utf8
> > /*!50100 PARTITION BY RANGE (timeStamp)
> > (PARTITION p01042010 VALUES LESS THAN (1270146600000) ENGINE = MyISAM,
> > PARTITION p02042010 VALUES LESS THAN (1270233000000) ENGINE = MyISAM,
> > PARTITION p03042010 VALUES LESS THAN (1270319400000) ENGINE = MyISAM,
> > ------------------
> > 60 such partitions..
> > ----
> > PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ |
> >
> > my query is following format:
> > mysql> explain select * from EntMsgLog where causeId= 659824157048176974
> and
> > msgId = 143168093266866137;
> >
> +----+-------------+-----------+------+---------------+----- ----+---------+-------------+------+-------+
> > | id | select_type | table | type | possible_keys | key | key_len
> |
> > ref | rows | Extra |
> >
> +----+-------------+-----------+------+---------------+----- ----+---------+-------------+------+-------+
> > | 1 | SIMPLE | EntMsgLog | ref | causeId | causeId | 16
> |
> > const,const | 62 | |
> >
> +----+-------------+-----------+------+---------------+----- ----+---------+-------------+------+-------+
> >
> >
> > This query is taking 1-2 sec. to execute and after profiling the query, I
> > found query is taking 90-95% time in "statistics" state. Please let me
> know
> > what I am doing wrong.
> >
> > -Dheeraj
> >
>
>
>
> --
> Baron Schwartz
> Percona Inc
> Consulting, Training, Support & Services for MySQL
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=dksidana@gmail.com
>
>

--00163630f0b5cb0b56048547b43f--