Monster Oasis

覺得累就是進步的開始!

Share on Facebook

download: http://dn.monster.tw/my/docs/mysql/mysql_perf_tuning.pdf

Share on Facebook

Mysql server version:

mysql> show variables like '%version%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| protocol_version         | 10                           |
| version                  | 5.1.46-community             |
| version_comment          | MySQL Community Server (GPL) |
| version_compile_machine  | x86_64                       |
| version_compile_os       | unknown-linux-gnu            |
+--------------------------+------------------------------+
9 rows in set (0.01 sec)

Check things…

mysql> show variables like '%inno%';
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| have_innodb                             | YES                    |

mysql> show variables like '%part%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |

my schema:

--
CREATE TABLE `tbl_ts_ip` (
  `xdate` int(8) NOT NULL default 0,
  `xhour` varchar(2) NOT NULL default '00',
  `tag1` varchar(100) NOT NULL default '',
  `tag2` varchar(300) NOT NULL default '',
  `val` int(10) unsigned  NOT NULL default '0',
  KEY `ndx_xdate` (`xdate`),
  KEY `ndx_xhour` (`xhour`),
  KEY `ndx_tag1` (`tag1`),
  KEY `ndx_tag2` (`tag2`)
) ENGINE=InnoDB
partition by range(xdate) (
  partition p201003 values less than (20100400),
  partition p201004 values less than (20100500),
  partition p201005 values less than (20100600),
  partition p201006 values less than (20100700)
);

testing

--
insert into tbl_ts_ip values (20100301,'01','tag1','tag2',1);
insert into tbl_ts_ip values (20100401,'01','tag1','tag2',1);
insert into tbl_ts_ip values (20100501,'01','tag1','tag2',1);
insert into tbl_ts_ip values (20100601,'01','tag1','tag2',1);
insert into tbl_ts_ip values (20100701,'01','tag1','tag2',1);
--

result:

--
mysql> insert into tbl_ts_ip values (20100301,1,'tag1','tag2',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_ts_ip values (20100401,1,'tag1','tag2',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_ts_ip values (20100501,1,'tag1','tag2',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_ts_ip values (20100601,1,'tag1','tag2',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_ts_ip values (20100701,1,'tag1','tag2',1);
ERROR 1526 (HY000): Table has no partition for value 20100701
mysql> select * from tbl_ts_ip;
+----------+-------+------+------+-----+
| xdate    | xhour | tag1 | tag2 | val |
+----------+-------+------+------+-----+
| 20100301 |     1 | tag1 | tag2 |   1 |
| 20100401 |     1 | tag1 | tag2 |   1 |
| 20100501 |     1 | tag1 | tag2 |   1 |
| 20100601 |     1 | tag1 | tag2 |   1 |
+----------+-------+------+------+-----+
4 rows in set (0.00 sec)
--

data files :

[root@ stat]# ls -l
total 676
-rw-rw---- 1 mysql mysql     65 May 18 17:02 db.opt
-rw-rw---- 1 mysql mysql   8682 May 18 17:07 tbl_ts_ip.frm
-rw-rw---- 1 mysql mysql     52 May 18 17:07 tbl_ts_ip.par
-rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201003.ibd
-rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201004.ibd
-rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201005.ibd
-rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201006.ibd

add partitions:

--
alter table tbl_ts_ip
add partition (
  partition p201007 values less than (20100800),
  partition p201008 values less than (20100900),
  partition p201009 values less than (20101000),
  partition p201010 values less than (20101100),
  partition p201011 values less than (20101200),
  partition p201012 values less than (20110100)
);
--

explain partitions:

--
mysql> explain partitions select * from tbl_ts_ip where xdate=20100501;
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table     | partitions | type | possible_keys | key       | key_len | ref   | rows | Extra |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | tbl_ts_ip | p201005    | ref  | ndx_xdate     | ndx_xdate | 4       | const |    1 |       |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+-------+
--
--
--
mysql> explain partitions select * from tbl_ts_ip where xdate>20100101 and xdate<20100501 and xhour=1 and tag1='x1' and tag2='x2';
+----+-------------+-----------+-------------------------+------+---------------------------------------+-----------+---------+-------+------+-------------+
| id | select_type | table     | partitions              | type | possible_keys                         | key       | key_len | ref   | rows | Extra       |
+----+-------------+-----------+-------------------------+------+---------------------------------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | tbl_ts_ip | p201003,p201004,p201005 | ref  | ndx_xdate,ndx_xhour,ndx_tag1,ndx_tag2 | ndx_xhour | 4       | const |    3 | Using where |
+----+-------------+-----------+-------------------------+------+---------------------------------------+-----------+---------+-------+------+-------------+
--

######################

sample schema:

create table t1 (

  d date

) engine=innodb partition by range ( to_days(d) ) (

  partition p1 values less than (to_days(’1999-01-01′)),

  partition p2 values less than (to_days(’2000-01-01′))

);

– drop old partition

alter table tablename

  drop partition p1;

######

--
CREATE TABLE `tbl_ts_ip` (
  `xdate` int(8) NOT NULL default 0,
  `xhour` int(2) NOT NULL default 0,
  `tag1` varchar(100) NOT NULL default '',
  `tag2` varchar(300) NOT NULL default '',
  `val` int(10) unsigned  NOT NULL default '0',
  KEY `ndx_xdate` (`xdate`),
  KEY `ndx_xhour` (`xhour`),
  KEY `ndx_tag1` (`tag1`),
  KEY `ndx_tag2` (`tag2`)
) ENGINE=InnoDB
partition by range(xdate) (
  partition p200901 values less than (20090200),
  partition p200902 values less than (20090300),
  partition p200903 values less than (20090400),
  partition p200904 values less than (20090500),
  partition p200905 values less than (20090600),
  partition p200906 values less than (20090700),
  partition p200907 values less than (20090800),
  partition p200908 values less than (20090900),
  partition p200909 values less than (20091000),
  partition p200910 values less than (20091100),
  partition p200911 values less than (20091200),
  partition p200912 values less than (20100100),
  partition p201001 values less than (20100200),
  partition p201002 values less than (20100300),
  partition p201003 values less than (20100400),
  partition p201004 values less than (20100500),
  partition p201005 values less than (20100600),
  partition p201006 values less than (20100700),
  partition p201007 values less than (20100800),
  partition p201008 values less than (20100900),
  partition p201009 values less than (20101000),
  partition p201010 values less than (20101100),
  partition p201011 values less than (20101200),
  partition p201012 values less than (20110100),
  partition p201101 values less than (20110200),
  partition p201102 values less than (20110300),
  partition p201103 values less than (20110400),
  partition p201104 values less than (20110500),
  partition p201105 values less than (20110600),
  partition p201106 values less than (20110700),
  partition p201107 values less than (20110800),
  partition p201108 values less than (20110900),
  partition p201109 values less than (20111000),
  partition p201110 values less than (20111100),
  partition p201111 values less than (20111200),
  partition p201112 values less than (20120100)
);
--

###

一天一個 partition …

--
CREATE TABLE `tbl_ts_ip` (
  `xdate` date NOT NULL DEFAULT '0000-00-00',
  `xhour` int(2) NOT NULL DEFAULT '0',
  `tag1` varchar(100) NOT NULL DEFAULT '',
  `tag2` varchar(300) NOT NULL DEFAULT '',
  `val` int(10) unsigned NOT NULL DEFAULT '0',
  KEY `ndx_xdate` (`xdate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE ( to_days(xdate) )
(PARTITION p0 VALUES LESS THAN ( to_days('2005-1-1') ) );
--
--
alter table tbl_ts_ip
add partition (
  partition p20100401 values less than ( to_days('20100401')+1 )
);

--

###

產生 alter table … add partition 的 code

$start_date = mktime( 0,0,0,4,1,2010);
$day_count = 365;

for ( $i=0; $i < $day_count; $i++ ) {
  echo "
    alter table tbl_ts_ip
    add partition (
      partition p".date("Ymd",$start_date)." values less than ( to_days('".date("Ymd",$start_date)."')+1 )
    );
  ";
  echo "
    alter table tbl_ts_member
    add partition (
      partition p".date("Ymd",$start_date)." values less than ( to_days('".date("Ymd",$start_date)."')+1 )
    );
  ";
  $start_date += 86400;
}

###

--
mysql> explain partitions select * from tbl_ts_ip where xdate>20100510 and xdate<20100517 and tag2='114.39.237.77'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_ts_ip
   partitions: p0,p20100511,p20100512,p20100513,p20100514,p20100515,p20100516
         type: ALL
possible_keys: ndx_xdate
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 37706064
        Extra: Using where
1 row in set (0.01 sec)

mysql> select * from tbl_ts_ip where xdate>20100510 and xdate<20100517 and tag2='114.39.237.77';
+------------+-------+--------------------+---------------+-----+
| xdate      | xhour | tag1               | tag2          | val |
+------------+-------+--------------------+---------------+-----+
| 2010-05-16 |    23 | 37043600380A380C3E | 114.39.237.77 |   2 |
+------------+-------+--------------------+---------------+-----+
1 row in set (24.26 sec)
--

###

—

用 mysql partition table 要注意的事:

Check resources!

• InnoDB
  
  check disk space (uses more than MyISAM)

  check CPU usage

• MyISAM
  
  use 2 file handles per partition

  If you use more than one partitioned table, count total file handles

• Archive
  
  check CPU usage

  check memory usage

Share on Facebook

Share on Facebook

Share on Facebook

重要參數1 = key_buffer_size 這個值只針對 MYISAM engine 有作用 , 這個值用來設定 index 使用多少 memory , mysql 把 index 儘可能的放在 memory 中, 這 key_buffer_size 決定 index 的使用效率 , 效率高的話就是多數的 索引 index 都是存在 memory 中, 那就 select 會快, 一般這個值是 16M , 這兩個狀態值可以用來判斷: Key_reads , Key_read_requests

看以下的例子:
DBM53 的 
show global status like ‘Key_read%’
Key_read_requests 36,479,348,961 
Key_reads 1,009,874,537 

Key_reads / Key_read_requests = 0.02 這樣偏高, 不太好 , 這個值正常是 0.01 , 0.001 最佳

好朋友 phpmyadmin 裡面有很好的解釋:

這台的 show global variables like ‘key_%’ 是 128MB
key_buffer_size 134,217,728

set global key_buffer_size=1024M

—

重要參數2 = table_cache

show global status like ‘Open%’

Open_tables 2,048 

若等於

show global variables like ‘%table%cache%’

table_cache 2,048 

並且 Opened_tables 一直增加 , 那要增加 table_cache 的值 , server 1G RAM 推薦 table_cache 128 ~ 256

沒錯, phpmyadmin 指示

那麼就來修改 table_cache 吧!

set global table_cache=3000;

not finish!

參考網址:

• http://www.slideshare.net/ligaya/performance-tuning

Share on Facebook

• mysql 的 innodb 重裝或改了 innodb_log_file_size 後, 發現 xxx/yyy.frm 壞了 , 解決辦法是 把 /var/lib/mysql/ib_logfile* 砍了, 再 restart mysql
• 在 http://dev.mysql.com/doc/refman/5.1/en/alter-table.html 中提到….

  若要大量 bluk 作 insert 動作前, 下 ALTER TABLE tbl_name DISABLE KEYS , 這樣可以讓 insert 加快,

  但是作完 insert 後還是得 enable keys , 把 missing 的 indexs 補回來, 我想這時也是非常耗時間吧!!

  另外 enable / disable keys 對於 mysql 5.1.1 以前的 partition table 沒有用

Share on Facebook

mysql> explain
    -> select SQL_CALC_FOUND_ROWS tbl_item.g_no, tbl_item.ctrl_rowid ,tbl_item.rank , g_priority_order
    -> from tbl_item
    -> where
    -> match(g_name_key) against('+AA +BB CC' in boolean mode)
    -> and tbl_item.g_end_int > 1249953400
    -> and tbl_item.g_class not like '0025%'
    -> order by g_priority_order desc, tbl_item.rank desc,tbl_item.g_post_time desc limit 0, 30\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_item
         type: fulltext
possible_keys: ndx_g_end_int,ndx_g_name_key
          key: ndx_g_name_key
      key_len: 0
          ref:
         rows: 1
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

Share on Facebook

參考: http://dev.mysql.com/doc/refman/5.0/en/optimizing-database-structure.html

7.4.1. Make Your Data as Small as Possible
7.4.2. Column Indexes
7.4.3. Multiple-Column Indexes
7.4.4. How MySQL Uses Indexes
7.4.5. The MyISAM Key Cache
7.4.6. MyISAM Index Statistics Collection
7.4.7. How MySQL Opens and Closes Tables
7.4.8. Disadvantages of Creating Many Tables in the Same Database

減少 record structure 的大小 – Numeric Types 參考表
(用 MEDIUMINT 3bytes 比 INT 4bytes 好 , 若資料內容不可能有負值那加上 UNSIGNED , 則數值範圍可多一倍!)

Share on Facebook

http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

The name index is an index over the last_name and first_name columns. The index can be used for queries that specify values in a known range for last_name, or for both last_name and first_name. Therefore, the name index is used in the following queries:

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

However, the name index is not used in the following queries, 以下的 query 用不到 index —> 多重 index 有先後區別.

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

Share on Facebook