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. dhee...@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 <ba...@xaprb.com> 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 <dksid...@gmail.com> 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 <http://www.percona.com/> > 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=dksid...@gmail.com > >
