¡¡¡¡Í¬Ê±ÔÚÏß·ÃÎÊÁ¿¼ÌÐøÔö´ó ¶ÔÓÚ1GÄÚ´æµÄ·þÎñÆ÷Ã÷ÏԸоõµ½³ÔÁ¦ÑÏÖØʱÉõÖÁÿÌ춼»áËÀ»ú »òÕßʱ²»Ê±µÄ·þÎñÆ÷¿¨Ò»Ï Õâ¸öÎÊÌâÔø¾À§ÈÅÁËÎÒ°ë¸ö¶àÔÂMySQLʹÓÃÊǺܾßÉìËõÐÔµÄËã·¨£¬Òò´ËÄãͨ³£ÄÜÓúÜÉÙµÄÄÚ´æÔËÐлò¸øMySQL¸ü¶àµÄ±»´æÒԵõ½¸üºÃµÄÐÔÄÜ¡£
¡¡¡¡°²×°ºÃmysqlºó£¬ÅäÖÆÎļþÓ¦¸ÃÔÚ/usr/local/mysql/share/mysqlĿ¼ÖУ¬ÅäÖÆÎļþÓм¸¸ö£¬ÓÐmy-huge.cnf my-medium.cnf my-large.cnf my-small.cnf,²»Í¬µÄÁ÷Á¿µÄÍøÕ¾ºÍ²»Í¬ÅäÖƵķþÎñÆ÷»·¾³£¬µ±È»ÐèÒªÓв»Í¬µÄÅäÖÆÎļþÁË¡£
¡¡¡¡Ò»°ãµÄÇé¿öÏ£¬my-medium.cnfÕâ¸öÅäÖÆÎļþ¾ÍÄÜÂú×ãÎÒÃǵĴó¶àÐèÒª£»Ò»°ãÎÒÃÇ»á°ÑÅäÖÃÎļþ¿½±´µ½/etc/my.cnf Ö»ÐèÒªÐÞ¸ÄÕâ¸öÅäÖÃÎļþ¾Í¿ÉÒÔÁË£¬Ê¹ÓÃmysqladmin variables extended-status -u root -p ¿ÉÒÔ¿´µ½Ä¿Ç°µÄ²ÎÊý£¬ÓУ³¸öÅäÖòÎÊýÊÇ×îÖØÒªµÄ£¬¼´key_buffer_size,query_cache_size,table_cache¡£
¡¡¡¡key_buffer_sizeÖ»¶ÔMyISAM±íÆð×÷Óã¬
¡¡¡¡key_buffer_sizeÖ¸¶¨Ë÷Òý»º³åÇøµÄ´óС£¬Ëü¾ö¶¨Ë÷Òý´¦ÀíµÄËٶȣ¬ÓÈÆäÊÇË÷Òý¶ÁµÄËٶȡ£Ò»°ãÎÒÃÇÉèΪ16M,ʵ¼ÊÉÏÉÔ΢´óÒ»µãµÄÕ¾µã¡¡Õâ¸öÊý×ÖÊÇÔ¶Ô¶²»¹»µÄ£¬Í¨¹ý¼ì²é״ֵ̬Key_read_requestsºÍKey_reads,¿ÉÒÔÖªµÀkey_buffer_sizeÉèÖÃÊÇ·ñºÏÀí¡£±ÈÀýkey_reads / key_read_requestsÓ¦¸Ã¾¡¿ÉÄܵĵͣ¬ÖÁÉÙÊÇ1:100£¬1:1000¸üºÃ£¨ÉÏÊö״ֵ̬¿ÉÒÔʹÓÃSHOW STATUS LIKE ¡®key_read%¡¯»ñµÃ£©¡£ »òÕßÈç¹ûÄã×°ÁËphpmyadmin ¿ÉÒÔͨ¹ý·þÎñÆ÷ÔËÐÐ״̬¿´µ½,±ÊÕßÍƼöÓÃphpmyadmin¹ÜÀímysql£¬ÒÔϵÄ״ֵ̬¶¼ÊDZ¾ÈËͨ¹ýphpmyadmin»ñµÃµÄʵÀý·ÖÎö:
¡¡¡¡Õâ¸ö·þÎñÆ÷ÒѾÔËÐÐÁË20Ìì
[code]key_buffer_size - 128M
key_read_requests - 650759289
key_reads - 79112[/code]
¡¡¡¡±ÈÀý½Ó½ü1:8000 ½¡¿µ×´¿ö·Ç³£ºÃ
¡¡¡¡ÁíÍâÒ»¸ö¹À¼Ækey_buffer_sizeµÄ°ì·¨¡¡°ÑÄãÍøÕ¾Êý¾Ý¿âµÄÿ¸ö±íµÄË÷ÒýËùÕ¼¿Õ¼ä´óС¼ÓÆðÀ´¿´¿´ÒÔ´Ë·þÎñÆ÷ΪÀý:±È½Ï´óµÄ¼¸¸ö±íË÷Òý¼ÓÆðÀ´´ó¸Å125M Õâ¸öÊý×Ö»áËæ×űí±ä´ó¶ø±ä´ó¡£
¡¡¡¡´Ó4.0.1¿ªÊ¼£¬MySQLÌṩÁ˲éѯ»º³å»úÖÆ¡£Ê¹Óòéѯ»º³å£¬MySQL½«SELECTÓï¾äºÍ²éѯ½á¹û´æ·ÅÔÚ»º³åÇøÖУ¬½ñºó¶ÔÓÚͬÑùµÄSELECTÓï¾ä£¨Çø·Ö´óСд£©£¬½«Ö±½Ó´Ó»º³åÇøÖжÁÈ¡½á¹û¡£¸ù¾ÝMySQLÓû§ÊֲᣬʹÓòéѯ»º³å×î¶à¿ÉÒÔ´ïµ½238%µÄЧÂÊ¡£
¡¡¡¡Í¨¹ýµ÷½ÚÒÔϼ¸¸ö²ÎÊý¿ÉÒÔÖªµÀquery_cache_sizeÉèÖõÃÊÇ·ñºÏÀí
[code]Qcache inserts
Qcache hits
Qcache lowmem prunes
Qcache free blocks
Qcache total blocks[/code]
¡¡¡¡Qcache_lowmem_prunesµÄÖµ·Ç³£´ó£¬Ôò±íÃ÷¾³£³öÏÖ»º³å²»¹»µÄÇé¿ö,ͬʱQcache_hitsµÄÖµ·Ç³£´ó£¬Ôò±íÃ÷²éѯ»º³åʹÓ÷dz£Æµ·±£¬´ËʱÐèÒªÔö¼Ó»º³å´óСQcache_hitsµÄÖµ²»´ó£¬Ôò±íÃ÷ÄãµÄ²éѯÖظ´Âʺܵͣ¬ÕâÖÖÇé¿öÏÂʹÓòéѯ»º³å·´¶ø»áÓ°ÏìЧÂÊ£¬ÄÇô¿ÉÒÔ¿¼ÂDz»Óòéѯ»º³å¡£´ËÍ⣬ÔÚSELECTÓï¾äÖмÓÈëSQL_NO_CACHE¿ÉÒÔÃ÷È·±íʾ²»Ê¹Óòéѯ»º³å¡£
¡¡¡¡Qcache_free_blocks£¬Èç¹û¸ÃÖµ·Ç³£´ó£¬Ôò±íÃ÷»º³åÇøÖÐËéƬºÜ¶àquery_cache_typeÖ¸¶¨ÊÇ·ñʹÓòéѯ»º³å
¡¡¡¡ÎÒÉèÖÃ:
[code]query_cache_size = 32M
query_cache_type= 1[/code]
¡¡¡¡µÃµ½ÈçÏÂ״ֵ̬:
[code]Qcache queries in cache 12737 ±íÃ÷Ä¿Ç°»º´æµÄÌõÊý
Qcache inserts 20649006
Qcache hits 79060095 ¡¡¿´À´Öظ´²éѯÂÊ»¹Í¦¸ßµÄ
Qcache lowmem prunes 617913¡¡ÓÐÕâô¶à´Î³öÏÖ»º´æ¹ýµÍµÄÇé¿ö
Qcache not cached 189896 ¡¡¡¡
Qcache free memory 18573912¡¡¡¡Ä¿Ç°Ê£Ó໺´æ¿Õ¼ä
Qcache free blocks 5328 Õâ¸öÊý×ÖËƺõÓеã´ó¡¡ËéƬ²»ÉÙ
Qcache total blocks 30953[/code]
¡¡¡¡Èç¹ûÄÚ´æÔÊÐí32MÓ¦¸ÃÒªÍùÉϼӵã
¡¡¡¡table_cacheÖ¸¶¨±í¸ßËÙ»º´æµÄ´óС¡£Ã¿µ±MySQL·ÃÎÊÒ»¸ö±íʱ£¬Èç¹ûÔÚ±í»º³åÇøÖл¹Óпռ䣬¸Ã±í¾Í±»´ò¿ª²¢·ÅÈëÆäÖУ¬ÕâÑù¿ÉÒÔ¸ü¿ìµØ·ÃÎʱíÄÚÈÝ¡£Í¨¹ý¼ì²é·åֵʱ¼äµÄ״ֵ̬Open_tablesºÍOpened_tables£¬¿ÉÒÔ¾ö¶¨ÊÇ·ñÐèÒªÔö¼Ótable_cacheµÄÖµ¡£Èç¹ûÄã·¢ÏÖopen_tablesµÈÓÚtable_cache£¬²¢ÇÒopened_tablesÔÚ²»¶ÏÔö³¤£¬ÄÇôÄã¾ÍÐèÒªÔö¼Ótable_cacheµÄÖµÁË£¨ÉÏÊö״ֵ̬¿ÉÒÔʹÓÃSHOW STATUS LIKE ¡®Open%tables¡¯»ñµÃ£©¡£×¢Ò⣬²»ÄÜäĿµØ°Ñtable_cacheÉèÖóɺܴóµÄÖµ¡£Èç¹ûÉèÖõÃÌ«¸ß£¬¿ÉÄÜ»áÔì³ÉÎļþÃèÊö·û²»×㣬´Ó¶øÔì³ÉÐÔÄܲ»Îȶ¨»òÕßÁ¬½Óʧ°Ü¡£
vema ÓÚ 2006-09-13 10:17:19·¢±í:
mysql> SHOW PROCESSLIST;
+--------+--------------+-----------+------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+--------------+-----------+------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 638753 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
| 639286 | auto18_f | localhost | auto18 | Sleep | 206 | | NULL |
| 639387 | auto18_f | localhost | auto18 | Sleep | 194 | | NULL |
| 639435 | auto18_f | localhost | auto18 | Sleep | 191 | | NULL |
| 639437 | auto18_f | localhost | auto18 | Sleep | 191 | | NULL |
| 639456 | auto18_f | localhost | auto18 | Sleep | 188 | | NULL |
| 639540 | auto18_f | localhost | auto18 | Sleep | 173 | | NULL |
| 639652 | auto18_f | localhost | auto18 | Sleep | 150 | | NULL |
| 639675 | auto18_f | localhost | auto18 | Sleep | 139 | | NULL |
| 639688 | auto18_f | localhost | auto18 | Sleep | 139 | | NULL |
| 639744 | auto18_f | localhost | auto18 | Sleep | 136 | | NULL |
| 639767 | auto18_f | localhost | auto18 | Sleep | 132 | | NULL |
| 639776 | auto18_f | localhost | auto18 | Sleep | 135 | | NULL |
| 639790 | auto18_f | localhost | auto18 | Sleep | 134 | | NULL |
| 639849 | auto18_f | localhost | auto18 | Sleep | 118 | | NULL |
| 640714 | auto18_f | localhost | auto18 | Sleep | 17 | | NULL |
| 640776 | auto18_f | localhost | auto18 | Query | 1 | Locked | select xlname
from mb_carprice as a
left join car_chexing as b on a.cxid=b |
| 640792 | auto18_f | localhost | auto18 | Sleep | 6 | | NULL |
| 640840 | auto18_f | localhost | auto18 | Query | 1 | Copying to tmp table | select
a.id,a.cxid,a.price,a.addtime,a.userid,a.province,
b.xlname,replace(b.zxname,\'ÎÞ\',\'\') as zxn |
| 640848 | auto18_f | localhost | auto18 | Query | 0 | Sorting result | select a.id,a.title,a.province,a.addtime,a.addr,a.good from news_news as a where 1 and a.xid=\'16\' an |
| 640852 | auto18_f | localhost | auto18 | Query | 1 | Locked | update car_chexing set hits=hits+1 where id=\'2055\' |
| 640854 | auto18_bbs_f | localhost | auto18_bbs | Sleep | 1 | | NULL |
+--------+--------------+-----------+------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
mysql> show status;
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 4 |
| Aborted_connects | 1548 |
| Bytes_received | 1021593916 |
| Bytes_sent | 1364428047 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 682316 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 59263 |
| Com_delete | 2272 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 6614 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 2 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 4998533 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 1 |
| Com_show_fields | 76 |
| Com_show_grants | 1 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 91 |
| Com_show_slave_status | 0 |
| Com_show_status | 20 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 7 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 166056 |
| Connections | 648131 |
| Created_tmp_disk_tables | 291022 |
| Created_tmp_tables | 393123 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 1050 |
| Handler_read_first | 18036 |
| Handler_read_key | 3027263087 |
| Handler_read_next | 2648054165 |
| Handler_read_prev | 797881331 |
| Handler_read_rnd | 862425008 |
| Handler_read_rnd_next | 711256450 |
| Handler_update | 2011628 |
| Handler_write | 1039612289 |
| Key_blocks_used | 7793 |
| Key_read_requests | 3096231942 |
| Key_reads | 391922 |
| Key_write_requests | 17972658 |
| Key_writes | 12664 |
| Max_used_connections | 100 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 99 |
| Open_streams | 0 |
| Opened_tables | 205064 |
| Questions | 6568410 |
| Select_full_join | 1028 |
| Select_full_range_join | 0 |
| Select_range | 25018 |
| Select_range_check | 0 |
| Select_scan | 1318165 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 587 |
| Slow_queries | 5011 |
| Sort_merge_passes | 0 |
| Sort_range | 1525732 |
| Sort_rows | 1642156667 |
| Sort_scan | 1056846 |
| Table_locks_immediate | 8686931 |
| Table_locks_waited | 51512 |
| Threads_cached | 0 |
| Threads_created | 648130 |
| Threads_connected | 2 |
| Threads_running | 1 |
| Uptime | 258099 |
+--------------------------+------------+
112 rows in set (0.00 sec)
top½á¹û£º
8318 mysql 24 0 28024 20M 1360 S 190.8 2.1 2140m 1 mysqld
7822 root 15 0 2028 276 188 S 6.5 0.0 0:49 1 httpd
8891 root 16 0 1176 1176 820 R 1.0 0.1 0:00 0 top
1 root 15 0 112 80 56 S 0.0 0.0 0:05 1 init
2 root RT 0 0 0 0 SW 0.0 0.0 0:00 0 migration/0
3 root RT 0 0 0 0 SW 0.0 0.0 0:00 1 migration/1
4 root 15 0 0 0 0 SW 0.0 0.0 0:00 1 keventd
5 root 34 19 0 0 0 SWN 0.0 0.0 0:00 0 ksoftirqd/0
6 root 34 19 0 0 0 SWN 0.0 0.0 0:00 1 ksoftirqd/1
9 root 15 0 0 0 0 SW 0.0 0.0 0:00 1 bdflush
7 root 15 0 0 0 0 SW 0.0 0.0 0:17 1 kswapd
8 root 15 0 0 0 0 SW 0.0 0.0 0:10 0 kscand
10 root 15 0 0 0 0 SW 0.0 0.0 0:10 0 kupdated
11 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 mdrecoveryd
18 root 25 0 0 0 0 SW 0.0 0.0 0:00 1 scsi_eh_0
19 root 25 0 0 0 0 SW 0.0 0.0 0:00 1 scsi_eh_1
22 root 15 0 0 0 0 SW 0.0 0.0 0:54 1 kjournald
73 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 khubd
767 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
3845 root 15 0 200 164 116 S 0.0 0.0 0:00 1 syslogd
3849 root 15 0 180 168 120 S 0.0 0.0 0:00 0 klogd
3859 root 15 0 244 232 180 S 0.0 0.0 0:09 1 irqbalance
3867 rpc 22 0 76 4 0 S 0.0 0.0 0:00 1 portmap
3886 rpcuser 25 0 76 4 0 S 0.0 0.0 0:00 0 rpc.statd
3897 root 15 0 168 152 108 S 0.0 0.0 0:00 1 mdadm
3912 root RT 0 188 88 52 S 0.0 0.0 0:00 1 auditd
Íû²»Áߴͽ̣¬¿´ÅäÖ÷½ÃæÒª¸Ä½øʲô£¬»¹ÇëÖ¸µãÒ»¶þ£¬Ð»Ð»
MORR ÓÚ 2006-09-12 00:31:26·¢±í:
¡¡¡¡¶ÔÓÚÓÐ1GÄÚ´æµÄ»úÆ÷£¬ÍƼöÖµÊÇ128£256¡£
¡¡¡¡±ÊÕßÉèÖÃtable_cache = 256
¡¡¡¡µÃµ½ÒÔÏÂ״̬:
[code]Open tables 256
Opened tables 9046[/code]
¡¡¡¡ËäÈ»open_tablesÒѾµÈÓÚtable_cache£¬µ«ÊÇÏà¶ÔÓÚ·þÎñÆ÷ÔËÐÐʱ¼äÀ´Ëµ,ÒѾÔËÐÐÁË20Ì죬opened_tablesµÄÖµÒ²·Ç³£µÍ¡£Òò´Ë£¬Ôö¼Ótable_cacheµÄÖµÓ¦¸ÃÓô¦²»´ó¡£Èç¹ûÔËÐÐÁË6¸öСʱ¾Í³öÏÖÉÏÊöÖµ ÄǾÍÒª¿¼ÂÇÔö´ótable_cache¡£
¡¡¡¡Èç¹ûÄã²»ÐèÒª¼Ç¼2½øÖÆlog ¾Í°ÑÕâ¸ö¹¦Äܹصô£¬×¢Òâ¹ØµôÒÔºó¾Í²»Äָܻ´³öÎÊÌâÇ°µÄÊý¾ÝÁË£¬ÐèÒªÄúÊÖ¶¯±¸·Ý£¬¶þ½øÖÆÈÕÖ¾°üº¬ËùÓиüÐÂÊý¾ÝµÄÓï¾ä£¬ÆäÄ¿µÄÊÇÔÚ»Ö¸´Êý¾Ý¿âʱÓÃËüÀ´°ÑÊý¾Ý¾¡¿ÉÄָܻ´µ½×îºóµÄ״̬¡£ÁíÍ⣬Èç¹û×öͬ²½¸´ÖÆ( Replication )µÄ»°£¬Ò²ÐèҪʹÓöþ½øÖÆÈÕÖ¾´«ËÍÐÞ¸ÄÇé¿ö¡£
¡¡¡¡log_binÖ¸¶¨ÈÕÖ¾Îļþ£¬Èç¹û²»ÌṩÎļþÃû£¬MySQL½«×Ô¼º²úÉúȱʡÎļþÃû¡£MySQL»áÔÚÎļþÃûºóÃæ×Ô¶¯Ìí¼ÓÊý×ÖÒý£¬Ã¿´ÎÆô¶¯·þÎñʱ£¬¶¼»áÖØÐÂÉú³ÉÒ»¸öеĶþ½øÖÆÎļþ¡£´ËÍ⣬ʹÓÃlog-bin-index¿ÉÒÔÖ¸¶¨Ë÷ÒýÎļþ£»Ê¹ÓÃbinlog-do-db¿ÉÒÔÖ¸¶¨¼Ç¼µÄÊý¾Ý¿â£»Ê¹ÓÃbinlog-ignore-db¿ÉÒÔÖ¸¶¨²»¼Ç¼µÄÊý¾Ý¿â¡£×¢ÒâµÄÊÇ£ºbinlog-do-dbºÍbinlog-ignore-dbÒ»´ÎÖ»Ö¸¶¨Ò»¸öÊý¾Ý¿â£¬Ö¸¶¨¶à¸öÊý¾Ý¿âÐèÒª¶à¸öÓï¾ä¡£¶øÇÒ£¬MySQL»á½«ËùÓеÄÊý¾Ý¿âÃû³Æ¸Ä³ÉСд£¬ÔÚÖ¸¶¨Êý¾Ý¿âʱ±ØÐëÈ«²¿Ê¹ÓÃСдÃû×Ö£¬·ñÔò²»»áÆð×÷Óá£
¡¡¡¡¹ØµôÕâ¸ö¹¦ÄÜÖ»ÐèÒªÔÚËûÇ°Ãæ¼ÓÉÏ#ºÅ
[code]#log-bin[/code]
¡¡¡¡¿ªÆôÂý²éѯÈÕÖ¾( slow query log )
¡¡¡¡Âý²éѯÈÕÖ¾¶ÔÓÚ¸ú×ÙÓÐÎÊÌâµÄ²éѯ·Ç³£ÓÐÓá£Ëü¼Ç¼ËùÓвé¹ýlong_query_timeµÄ²éѯ£¬Èç¹ûÐèÒª£¬»¹¿ÉÒԼǼ²»Ê¹ÓÃË÷ÒýµÄ¼Ç¼¡£ÏÂÃæÊÇÒ»¸öÂý²éѯÈÕÖ¾µÄÀý×Ó£º
¡¡¡¡¿ªÆôÂý²éѯÈÕÖ¾£¬ÐèÒªÉèÖòÎÊýlog_slow_queries¡¢long_query_times¡¢log-queries-not-using-indexes¡£
¡¡¡¡log_slow_queriesÖ¸¶¨ÈÕÖ¾Îļþ£¬Èç¹û²»ÌṩÎļþÃû£¬MySQL½«×Ô¼º²úÉúȱʡÎļþÃû¡£long_query_timesÖ¸¶¨Âý²éѯµÄãÐÖµ£¬È±Ê¡ÊÇ10Ãë¡£log-queries-not-using-indexesÊÇ4.1.0ÒÔºóÒýÈëµÄ²ÎÊý£¬Ëüָʾ¼Ç¼²»Ê¹ÓÃË÷ÒýµÄ²éѯ¡£±ÊÕßÉèÖÃlong_query_time=10
¡¡¡¡±ÊÕßÉèÖÃ:
[code]sort_buffer_size = 1M
max_connections=120
wait_timeout =120
back_log=100
read_buffer_size = 1M
thread_cache=32
interactive_timeout=120
thread_concurrency = 4[/code]
¡¡¡¡²ÎÊý˵Ã÷:
[code]back_log[/code]
¡¡¡¡ÒªÇóMySQLÄÜÓеÄÁ¬½ÓÊýÁ¿¡£µ±Ö÷ÒªMySQLÏß³ÌÔÚÒ»¸öºÜ¶Ìʱ¼äÄڵõ½·Ç³£¶àµÄÁ¬½ÓÇëÇó£¬Õâ¾ÍÆð×÷Óã¬È»ºóÖ÷Ï̻߳¨Ð©Ê±¼ä(¾¡¹ÜºÜ¶Ì)¼ì²éÁ¬½Ó²¢ÇÒÆô¶¯Ò»¸öÐÂÏ̡߳£back_logÖµÖ¸³öÔÚMySQLÔÝʱֹͣ»Ø´ðÐÂÇëÇó֮ǰµÄ¶Ìʱ¼äÄÚ¶àÉÙ¸öÇëÇó¿ÉÒÔ±»´æÔÚ¶ÑÕ»ÖС£Ö»ÓÐÈç¹ûÆÚÍûÔÚÒ»¸ö¶Ìʱ¼äÄÚÓкܶàÁ¬½Ó£¬ÄãÐèÒªÔö¼ÓËü£¬»»¾ä»°Ëµ£¬ÕâÖµ¶Ôµ½À´µÄTCP/IPÁ¬½ÓµÄÕìÌý¶ÓÁеĴóС¡£ÄãµÄ²Ù×÷ϵͳÔÚÕâ¸ö¶ÓÁдóСÉÏÓÐËü×Ô¼ºµÄÏÞÖÆ¡£ Unix listen(2)ϵͳµ÷ÓõÄÊÖ²áÒ³Ó¦¸ÃÓиü¶àµÄϸ½Ú¡£¼ì²éÄãµÄOSÎĵµÕÒ³öÕâ¸ö±äÁ¿µÄ×î´óÖµ¡£ÊÔͼÉ趨back_log¸ßÓÚÄãµÄ²Ù×÷ϵͳµÄÏÞÖƽ«ÊÇÎÞЧµÄ¡£
[code]max_connections[/code]
¡¡¡¡²¢·¢Á¬½ÓÊýÄ¿×î´ó£¬120 ³¬¹ýÕâ¸öÖµ¾Í»á×Ô¶¯»Ö¸´£¬³öÁËÎÊÌâÄÜ×Ô¶¯½â¾ö
[code]thread_cache[/code]
¡¡¡¡Ã»ÕÒµ½¾ßÌå˵Ã÷£¬²»¹ýÉèÖÃΪ32ºó 20Ìì²Å´´½¨ÁË400¶à¸öÏß³Ì ¶øÒÔÇ°Ò»Ìì¾Í´´½¨ÁËÉÏǧ¸öÏß³Ì ËùÒÔ»¹ÊÇÓÐÓõÄ
[code]thread_concurrency[/code]
¡¡¡¡#ÉèÖÃΪÄãµÄcpuÊýÄ¿x2,ÀýÈ磬ֻÓÐÒ»¸öcpu,ÄÇôthread_concurrency=2
¡¡¡¡#ÓÐ2¸öcpu,ÄÇôthread_concurrency=4
[code]skip-innodb[/code]
¡¡¡¡#È¥µôinnodbÖ§³Ö