mysql

MySQL performance tunning

by

重要參數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 裡面有很好的解釋:

Key_read_requests 36 G The number of requests to read a key block from the cache.
Key_reads 1,010 M The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.

這台的 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 指示

Open_tables 2,048 The number of tables that are open.
Opened_tables 6,529 The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.

那麼就來修改 table_cache 吧!

set global table_cache=3000;

 

 

 

not finish!

參考網址:

some mysql tips

by
  • 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 沒有用

mysql Full-Text Search syntax

by

ef5dc3eace8cd26e67fb8eb3987eb729

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)

MySQL – Optimizing Database Structure

by

參考: 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 , 則數值範圍可多一倍!)

fd5aa2a415b47b898c229846a7644cf8

mysql index 的建立/使用 , Multiple-Column Indexes

by

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';

解決 mysql innodb 效能問題方法之一

by

可以用
Using Per-Table Tablespaces : http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces.html

Store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because in effect each table has its own tablespace.

Using multiple tablespaces can be beneficial to users who want to move specific tables to separate physical disks or who wish to restore backups of single tables quickly without interrupting the use of other InnoDB tables.

[mysqld]
innodb_file_per_table

那在把不同的 innodb file 放到不同 disk partition 去, 達到分散實體 IO 的目標, 不過, mysql 對於 path 部份要特別處理, 避免 drop table 或 alter table 時, 它會發生無法預期的結果, 翻了 web , 看到有人這麼作:

改 mysys/mf_format.c

if (!lstat(to,&stat_buff))  /* Check if it's a symbolic link */
    if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))
成

if (realpath(to,buff))

試看看吧, 我沒改過!