Showing posts with label mysqlsla. Show all posts
Showing posts with label mysqlsla. Show all posts

Wednesday, March 31, 2010

vBulletin session table and Hash index in MEMORY Engine

In vBulletin Board System there is session table contains online user information and tracking, in which forum now, what the current URL now, So it is have huge concurrent update statement, By default this table is MEMORY engine because its data not important in case MySQL restart.

I notice early loaded in MySQL and server, So I do full tuning for the server then analyze slow query using mysqlsla and notice this query in the top:
SELECT user.username, (user.options & 512) AS invisible, user.usergroupid,
   session.userid, session.inforum, session.lastactivity, session.badlocation,
   IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid
  FROM session AS session force index(lastact_user)
  LEFT JOIN user AS user ON(user.userid = session.userid)
  WHERE session.lastactivity > 1269376225
  ORDER BY lastactivity DESC
and this is create table statement
mysql> show create table session\G
*************************** 1. row ***************************
       Table: session
Create Table: CREATE TABLE `session` (
  `sessionhash` char(32) NOT NULL DEFAULT '',
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `host` char(15) NOT NULL DEFAULT '',
  `idhash` char(32) NOT NULL DEFAULT '',
  `lastactivity` int(10) unsigned NOT NULL DEFAULT '0',
  `location` char(255) NOT NULL DEFAULT '',
  `useragent` char(100) NOT NULL DEFAULT '',
  `styleid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `languageid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `loggedin` smallint(5) unsigned NOT NULL DEFAULT '0',
  `inforum` smallint(5) unsigned NOT NULL DEFAULT '0',
  `inthread` int(10) unsigned NOT NULL DEFAULT '0',
  `incalendar` smallint(5) unsigned NOT NULL DEFAULT '0',
  `badlocation` smallint(5) unsigned NOT NULL DEFAULT '0',
  `bypass` tinyint(4) NOT NULL DEFAULT '0',
  `profileupdate` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`sessionhash`),
  KEY `userid` (`userid`),
  KEY `lastact_user` (`lastactivity`,`userid`),
  KEY `idx_host_user` (`host`,`userid`),
  KEY `idhash_idx` (`idhash`),
  KEY `idx_lastactivity` (`lastactivity`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 PACK_KEYS=0


so for first impression this query use index for where and order by, so its optimal!!!!!

Unfortunately, we see it not using the index even when force it.
mysql> explain select * from session where lastactivity > 1269509561;
+----+-------------+---------+------+-------------------------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys                 | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+-------------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | session | ALL  | lastact_user,idx_lastactivity | NULL | NULL    | NULL | 3199 | Using where |
+----+-------------+---------+------+-------------------------------+------+---------+------+------+-------------+

mysql> explain select * from session force index (idx_lastactivity) where lastactivity > 1269509561;
+----+-------------+---------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | session | ALL  | idx_lastactivity | NULL | NULL    | NULL | 3199 | Using where |
+----+-------------+---------+------+------------------+------+---------+------+------+-------------+
Why?

MEMORY engine support B-tree and Hash indexes, but by default use Hash algorithm:
    *      They are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as < that find a range of values.
    *      The optimizer cannot use a hash index to speed up ORDER BY operations. (This type of index cannot be used to search for the next entry in order.)
    *      MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM table to a hash-indexed MEMORY table.
    *      Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)

so the optimizer will not use this index.

when we alter index algorithm by:

alter table session drop index idx_lastactivity, add index `idx_lastactivity` (`lastactivity`) USING BTREE;

we notice:
mysql> explain select * from session where lastactivity > 1269509561;
+----+-------------+---------+-------+-------------------------------+------------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys                 | key              | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+-------------------------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | session | range | lastact_user,idx_lastactivity | idx_lastactivity | 4       | NULL | 1919 | Using where |
+----+-------------+---------+-------+-------------------------------+------------------+---------+------+------+-------------+

mysql> explain select * from session force index (idx_lastactivity) where lastactivity > 1269509561;
+----+-------------+---------+-------+------------------+------------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys    | key              | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+------------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | session | range | idx_lastactivity | idx_lastactivity | 4       | NULL | 1919 | Using where |
+----+-------------+---------+-------+------------------+------------------+---------+------+------+-------------+

So we should be careful when use MEMORY table with indexes

Sunday, April 12, 2009

mysqlsla amazing tool

mysqlsla is interesting tool to analyze slow log query, aggregate same query in one and generate unique sql statement withCount, (max, min, avg) execute time, lock time, Rows sent, Rows examined for each unique one.

you can use it to review indexes and drop unused index, and create another.


Report for slow logs: slowquery1day.txt
791 queries total, 85 unique
Sorted by 't_sum'
Grand Totals: Time 23.05k s, Lock 2.18k s, Rows sent 24.17M, Rows Examined 120.61M


____________________________________________________________ 001 ___
Count : 355 (44.88%)
Time : 7588 s total, 21.374648 s avg, 11 s to 203 s max (32.92%)
95% of Time : 6352 s total, 18.848665 s avg, 11 s to 44 s max
Lock Time (s) : 675 s total, 1.901408 s avg, 0 to 191 s max (30.95%)
95% of Lock : 57 s total, 169.139 ms avg, 0 to 6 s max
Rows sent : 141 avg, 1 to 150 max (0.21%)
Rows examined : 37.17k avg, 24 to 310.07k max (10.94%)
Database : db_test
Users :
FrashaSlvReplic@ 70.84.164.205 : 92.11% (327) of query, 87.74% (694) of all users
Frashat_test@ 70.84.164.205 : 7.89% (28) of query, 5.44% (43) of all users

Query abstract:
SELECT post.postid, post.pagetext, ifnull( user.username , post.username ) AS username, dateline FROM noway_post AS post LEFT JOIN noway_user AS user ON (user.userid = post.userid) WHERE threadid = N AND visible = N AND post.userid NOT IN (N1) ORDER BY dateline ASC LIMIT N,N;

Query sample:
SELECT post.postid, post.pagetext, IFNULL( user.username , post.username ) AS username, dateline
FROM noway_post AS post
LEFT JOIN noway_user AS user ON (user.userid = post.userid)
WHERE threadid = 314734
AND visible = 1
AND post.userid NOT IN (28816)
ORDER BY dateline ASC
LIMIT 300,150;

_____________________________________________________________ 002 ___
Count : 90 (11.38%)
Time : 1840 s total, 20.444444 s avg, 11 s to 128 s max (7.98%)
95% of Time : 1445 s total, 17 s avg, 11 s to 35 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%)
95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent : 25 avg, 0 to 25 max (0.01%)
Rows examined : 13.89k avg, 146 to 26.54k max (1.04%)
Database : db
Users :
FrashaSlvReplic@ 70.84.164.205 : 100.00% (90) of query, 87.74% (694) of all users

Query abstract:
SELECT COUNT(*) AS COUNT, threadid, MAX(dateline) AS lastpost FROM noway_post AS post WHERE post.userid = N AND post.visible = N AND post.threadid IN (N25) GROUP BY threadid;

Query sample:
SELECT COUNT(*) AS count, threadid, MAX(dateline) AS lastpost
FROM noway_post AS post
WHERE post.userid = 268569 AND
post.visible = 1 AND
post.threadid IN (0820005, 636923, 803089, 735659, 735645, 808314, 765143, 688062, 738500, 787405, 633043, 454061, 703866, 707968, 676619, 647881, 645328, 451514, 674289, 650001, 654712, 562233, 645180, 638518, 619261)
GROUP BY threadid;

_____________________________________________________________ 003 ___
Count : 15 (1.90%)
Time : 1574 s total, 104.933333 s avg, 21 s to 282 s max (6.83%)
95% of Time : 1292 s total, 92.285714 s avg, 21 s to 261 s max
Lock Time (s) : 47 s total, 3.133333 s avg, 0 to 42 s max (2.15%)
95% of Lock : 5 s total, 357.143 ms avg, 0 to 5 s max
Rows sent : 84 avg, 10 to 100 max (0.01%)
Rows examined : 69.30k avg, 1.03k to 219.67k max (0.86%)
Database : db
Users :
FrashaSlvReplic@ 70.84.164.205 : 100.00% (15) of query, 87.74% (694) of all users

Query abstract:
SELECT DISTINCT thread.threadid, thread.forumid, post.userid FROM noway_thread AS thread INNER JOIN noway_post AS post ON(thread.threadid = post.threadid ) WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N20) AND thread.forumid IN(N1) AND post.visible = N LIMIT N;

Query sample:
SELECT
DISTINCT thread.threadid, thread.forumid, post.userid
FROM noway_thread AS thread
INNER JOIN noway_post AS post ON(thread.threadid = post.threadid )
WHERE MATCH(post.title, post.pagetext) AGAINST ('هذه الدنيا عجايب') AND thread.forumid NOT IN (0,38,41,117,55,94,99,100,72,39,42,43,57,44,37,26,40,27,34,53) AND thread.forumid IN(86) AND post.visible = 1
LIMIT 100;


http://hackmysql.com/mysqlsla