2014年6月16日 星期一

MySQL Performance Tunning

MyISAM


1.  Table Row_Format

http://dev.mysql.com/doc/refman/5.1/en/static-format.html

show table status

Row_format =  Fixed / Dynamic

Fixed is better for select , but the table can't contain variable-length columns (VARCHAR, VARBINARY, BLOB, TEXT)

ALTER TABLE DBNAME.TABLENAME ROW_FORMAT=Fixed

Static-format tables have these characteristics:
  • CHAR and VARCHAR columns are space-padded to the specified column width, although the column type is not altered. BINARY and VARBINARY columns are padded with 0x00 bytes to the column width.
  • Very quick.
  • Easy to cache.
  • Easy to reconstruct after a crash, because rows are located in fixed positions.
  • Reorganization is unnecessary unless you delete a huge number of rows and want to return free disk space to the operating system. To do this, use OPTIMIZE TABLE or myisamchk -r.
  • Usually require more disk space than dynamic-format tables.


2. OPTIMIZE TABLE
http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

After deleting a large part of a MyISAM or ARCHIVE table, or making many changes to a MyISAM or ARCHIVE table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.

For MyISAM tables, OPTIMIZE TABLE works as follows:
  1. If the table has deleted or split rows, repair the table.
  2. If the index pages are not sorted, sort them.
  3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.
By default, the server writes OPTIMIZE TABLE statements to the binary log so that they replicate to replication slaves. To suppress logging, specify the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.






2014年6月15日 星期日

MySQL Status

1. Dispaly Table locks / cache hit Info


mysql> show global status like 'Table%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| Table_locks_immediate      | 71626997 |
| Table_locks_waited         | 26169971 |
| Table_open_cache_hits      | 97796908 |
| Table_open_cache_misses    | 108      |
| Table_open_cache_overflows | 0        |
+----------------------------+----------+
5 rows in set (0.00 sec)

2. Display Command (Insert/Update etc Count)

mysql> show global status like 'Com_%';
 select * from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME like 'Com_%';
 select * from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME IN ('COM_UPDATE','COM_INSERT','COM_SELECT','COM_DELETE');
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| COM_DELETE    | 120892         |
| COM_INSERT    | 202796         |
| COM_SELECT    | 60755185       |
| COM_UPDATE    | 37150062       |
+---------------+----------------+

3. Show Process List from a Host

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE HOST like '192.168.1.203%';


4. Show Global Query Cache status

mysql> show global status like 'Qca%';


5.  set slow_query_log  on/off

set global slow_query_log = 'on';
set global slow_query_log = 'off';


7. Show Index status from Table;

mysql> show index from DBNAME.TABLENAME;



8. show global variables;

show global variables;







2014年6月14日 星期六

mysqldumpslow


Usage :

mysqldumpslow slowlog

mysqldumpslow slowlog -s t
sort by total query time

mysqldumpslow slowlog -g "TEST"
regular expression match TEST



to display more digital in time

vim mysqldumpslow

167c167
<     printf "Count: %d  Time=%.2fs (%ds)  Lock=%.2fs (%ds)  Rows=%.1f (%d), $user\@$host\n%s\n\n",
---
>     printf "Count: %d  Time=%.6fs (%.2fs)  Lock=%.6fs (%.2fs)  Rows=%.1f (%d), $user\@$host\n%s\n\n",


Awk Samples

1.

awk -v COLUMN=2 '{ sum += $COLUMN } END { print sum}'

set 2nd to COLUMN and sum ,
after process all lines , print the sum

2.

awk -F"[()s]" -v Q=3 '{qSum += $Q} END { print qSum}'

set separater to  ( , ) , s

set 3nd to Q and sum it,
after process all lines , print the sum

3.

awk -F"[()s]" '{qSum += $3; lSum += $7; rSum += $11} END { print qSum,lSum,rSum}'
set separater to  ( , ) , s
sum 3 fileds
print them


4.

#!/usr/bin/awk -f
NR%4 == 0{
        print ARGC
        for ( k=0 ; k < ARGC ; k++) {
                print "ARGV[" k "] = [" ARGV[k] "]"
        }
}



5. regular expression


EX1 :

test.awk
#!/usr/bin/gawk -f
$0 ~ /Time:/{
        #print $0;
        match($0, /Time: ([0-9]+)/, arr)
        if( arr[1] != "")
                print arr[1];
#               print NR;
}

Search String "Time:"

Match the   Time: 140613 22:26:16   to arr
print arr[1]

Output
140613
140613
140613

#!/usr/bin/gawk -f
$0 ~ /Time: [0-9]+ [0-9][0-9]:[0-9][0-9]/{
        print $0
}

Seach String " Time:  [d]+ dd:dd:dd"

Output :

# Time: 140613 22:26:16
# Time: 140613 22:26:17
# Time: 140613 22:26:18
# Time: 140613 22:26:19
# Time: 140613 22:26:20
# Time: 140613 22:26:21