ºìÁªLinuxÃÅ»§
Linux°ïÖú

ÔÚLinux¸ß¸ºÔØÏÂmysqlÊý¾Ý¿â³¹µ×ÓÅ»¯

·¢²¼Ê±¼ä:2006-09-12 00:30:07À´Ô´:ºìÁª×÷Õß:MORR
¡¡¡¡Í¬Ê±ÔÚÏß·ÃÎÊÁ¿¼ÌÐøÔö´ó ¶ÔÓÚ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ÉèÖóɺܴóµÄÖµ¡£Èç¹ûÉèÖõÃÌ«¸ß£¬¿ÉÄÜ»áÔì³ÉÎļþÃèÊö·û²»×㣬´Ó¶øÔì³ÉÐÔÄܲ»Îȶ¨»òÕßÁ¬½Óʧ°Ü¡£
ÎÄÕÂÆÀÂÛ

¹²ÓÐ 2 ÌõÆÀÂÛ

  1. 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
    Íû²»Áߴͽ̣¬¿´ÅäÖ÷½ÃæÒª¸Ä½øʲô£¬»¹ÇëÖ¸µãÒ»¶þ£¬Ð»Ð»

  2. 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Ö§³Ö