¾Æ ½º¿ÒÅ©±â´Â ÀϹÝÀûÀ¸·Î º¸Åë ¹°¸®Àû ¸Þ¸ð¸®ÀÇ 2¹è Á¤µµ·Î Àâ¾ÆµÎ´Â
°ÍÀÌ ÁÁÀ¸³ª ÇöÀç DB ¶ó¸é 4G Á¤µµ ÀÖ¾î¾ß µÉ µí Çϳ׿ä.
[»êÀÌ]´ÔÀÌ ³²±â½Å ±Û:
>[±è±ÙÅÂ]´ÔÀÌ ³²±â½Å ±Û:
>
>>ÀÌÁú¹®À» ¿©±â¿¡ µå·Áµµ µÇ´ÂÁö¸ð¸£°ÚÁö¸¸ mysql status °³¹ßÀÚ´ÔÀ̽Å
>>°Í°°¾Æ¼ ½Ç·ÊÁö¸¸ ¹®Àǵ帳´Ï´Ù.
>>ÇØ´çÇÁ·Î±×·¥ µ¹¸®¸é ¾Æ·¡¿Í °°Àº °á°ú°¡ ³ª¿À°íÀÖ½À´Ï´Ù.
>>
>>¿©±â¼ key_buffer_sizeºÎºÐÀÌ¿ä..ÀÌ°É
4±â°¡±îÁö ´Ã·Áµµ ´õ ´Ã¸®¶ó´Â
>>
>>¸»ÀÌ ³ª¿É´Ï´Ù.ÃÊ´ç Äõ¸® ¿äû¼ö°¡ 20ȸ¿¡¼ 100ȸÁ¤µµ ³ª¿À´Âµ¥..
>
>Àý´ëÀûÀÎ MAX °ª ÁöÁ¤ÀÌ ¾ø±â ¶§¹®¿¡ °è»ê»óÀÇ ¾î¿ ¼ö ¾ø´Â ¹ö±×(?)ÀÔ´Ï´Ù.
¤Ð¤Ð
>»¡°£»öÀÌ ¸¹ÀÌ ³ª¿Â´Ù°í Çؼ Àý´ëÀûÀ¸·Î Àß ¸øµÈ °ÍÀº ¾Æ´Õ´Ï´Ù.
>
>ÀÏ´Ü À§ÀÇ key buffer ¸¦ ³Ê¹« Å©°Ô Àâ¾Æ ³õ¾Ò³×¿ä.
>
>ÀÌ °ªÀ» ³Ê¹« Å©°Ô Àâ´Ù º¸´Ï ´Ù¸¥ thread ¿¡¼ »ç¿ëÇÒ ¸Þ¸ð¸®°¡ »ó´ëÀûÀ¸·Î À۾Ƽ
¾Æ¸¶µµ disk ¸¦ »ç¿ëÇÏ´Â µí ÇÕ´Ï´Ù.
>
>¹Ý´ë·Î join_buffer, read_rnd_buffer ¸¦ ³Ê¹« ÀÛ°Ô Àâ¾Æ ³õ¾Ò³×¿ä.
>
>ÇöÀç mysql Àü¿ë ¼¹ö·Î »ç¿ëÇÏ°í ÀÖ°í Àß ¸¸ Æ©´×Çϸé 1G Á¤µµ¿¡¼
>ÃæºÐÈ÷ ¸Å²ô·´°Ô(?) µ¹¾Æ°¥ µí ÇÕ´Ï´Ù.
>
>ÀÏ´Ü ¾Æ·¡ Á¦°¡ ¼ø½Ä°£(?)¿¡ Æ©´×ÇÑ °ªÀ» Àû¿ëÇØ º¸¼¼¿ä.
>
>Àû¿ëÈÄ ÇÏ·ç Á¤µµ Áö³ª°í mysql_status ·Î È®ÀÎÇغ¸¼¼¿ä.
>(°á°ú°¡ ±Ã±ÝÇÏ³×¿ä ¤¾¤¾)
>
>-------------------------------------
>set-variable = key_buffer=384M ; // Æ©´× ÇÊ¿ä(¸Þ¸ð¸® Áõ¼³½Ã 512M·Î
¼³Á¤)
>set-variable = table_cache=512 ; // 512~1024 Á¤µµ(Å×ÀÌºí °³¼ö¿Í
°ü·ÃµÊ)
>set-variable = thread_cache=8 ; // 8~16Á¤µµ¸é ÃæºÐ
>set-variable = join_buffer=2M ; // Æ©´× ÇÊ¿ä
>set-variable = myisam_sort_buffer_size=64M
>set-variable = read_buffer=2M ; // 2M Á¤µµ¸é ÃæºÐ
>set-variable = read_rnd_buffer=2M ; // 2M Á¤µµ¸é ÃæºÐ
>set-variable = sort_buffer=2M ; // 2M Á¤µµ¸é ÃæºÐ
>set-variable = wait_timeout=10 ; // 10ÃÊ ÀÌÇÏ·Î ¹«ÀǹÌÇÔ
>set-variable = max_connections=200 ; // 300 ÀÌ»ó½Ã ¸Þ¸ð¸® 2G ÀÌ»ó
>set-variable = open_files_limit=0
>set-variable = tmp_table_size=64M ; // 64M Á¤µµ
>-------------------------------------
>
>Âü°í·Î À§ÀÇ ¼³Á¤Àº ¸Þ¸ð¸®°¡ 1G~2G ¶Ç´Â ±× ÀÌ»óÀÌ ¿ä±¸µË´Ï´Ù.
>
>ÇÑ°¡Áö ´õ Âü°í·Î, ¸Þ¸ð¸®°¡ ºÎÁ·Çϰųª À߸ø Æ©´×Çϸé mysql ·Î±×¿¡
>
>key_buffer_size + (read_buffer + sort_buffer) * max_connections = available of
memory
>
>ģöÇÏ°Ô ÀÌ·± ºñ½ÁÇÑ ¸Þ½ÃÁö°¡ º¸ÀÔ´Ï´Ù.
>
>À§ÀÇ °è»ê´ë·Î¶ó¸é max_connections ÀÌ 160 Á¤µµ ³ª¿À´Âµ¥ 200 ±îÁö
>¼³Á¤Çصµ µË´Ï´Ù.
>
>
>>
>>Äõ¸®´Â ´Ü¼ø select,insert,update¹®ÀÌ°í
Äõ¸®´Â ÃÖÀûÈ µÇ¾îÀÖ½À´Ï´Ù.
>>
>>key_buffer_size¸¦ 4±â°¡ ÀÌ»óÀ¸·Î ´Ã·Á¾ßµÉÁö..¾Æ´Ï¸é ´Ù¸¥ ¹®Á¦°¡
>>
>>Àִ°ÍÀÎÁö ±Ã±ÝÇϱ¸¿ä.¼¹ö»ç¾çÀº
Âü°í·Î amd ¿ÉÅ×·Ð 2800 µà¾óÀÔ´Ï´Ù.
>>
>>·¥Àº 1±â°¡ÀÔ´Ï´Ù..
>>
>>Çϵå¿þ¾î ¼º´ÉÀ» ´õ ³ô¿©¾ß µÇ´Â°ÍÀÎÁö..½ÇÁ¦·Î mysql_connect ½ÇÆÐÀ²ÀÌ
>>
>>»ó´çÈ÷ ¸¹½À´Ï´Ù.¼Õ½ÇÀ» ÁÙÀÏ·Á¸é ·¥À» ¿Ã·Á¾ß µÉ±î¿ä...
>>
>>ÀÌ°ÅÀú°Í ¿É¼ÇÀ» ¹Ù²ãºÃÁö¸¸..±×´ë·Î³×¿ä..
>>
>>´äº¯±â´Ù¸®°Ú½À´Ï´Ù.°¨»çÇÕ´Ï´Ù.
>>
>>
>>-----------------------°á°ú---------------------------------------
>>
>>TOTAL_STATUS 19.27 very busy (1ÃÊ´ç Æò±Õ Äõ¸® ¿äû¼ö)
>>CRITICAL_LIFE_TIME 2ÃÊ Ä¿³Ø¼Ç´ç Æò±ÕÀûÀÎ(Ãß¼¼) ÀÓ°è life time(CUR)
>>CRITICAL_LIFE_TIME_MAX 100ÃÊ Ä¿³Ø¼Ç´ç Æò±ÕÀûÀÎ(ÃÖ´ë) ÀÓ°è life time(EXP)
>>All_databases 13 or more µ¥ÀÌÅͺ£À̽º ¼ö
>>Aborted_clients 0 ¿¬°á Ãë¼Ò Clients ¼ö, refer 28,800(8½Ã°£ 0ÃÊ)[wait_timeout] and
28,800(8½Ã°£ 0ÃÊ)[interactive_timeout]
>>Aborted_connects 3 ¿¬°á ½ÇÆмö, refer 3(3ÃÊ)[connect_timeout]
>>Aborted_clients_percent 0.00% ¿¬°á Ãë¼ÒÀ², Á¤»ó[Âü°í]
>>Aborted_connects_percent 0.17% ¿¬°á ½ÇÆÐÀ², Á¤»ó
>>Bytes_received 270 KB ¼ö½Å·®(ÃÑ)
>>Bytes_sent 1 MB Àü¼Û·®(ÃÑ)
>>Bytes_per_rs 5.22 ¹Þ°í(1) º¸³»´Â(x) ºñÀ², 1:x
>>Bytes_sent_per_sec 4 KB Àü¼Û·®(ÃÊ´ç)
>>Bytes_sent_per_min 245 KB Àü¼Û·®(ºÐ´ç)
>>Bytes_sent_per_hour 14 MB Àü¼Û·®(½Ã°£´ç)
>>Bytes_sent_per_day 345 MB Àü¼Û·®(ÇÏ·çÆò±Õ), ¿¹»ó¼öÄ¡
>>Com_delete 0.00% DELETE Äõ¸® »ç¿ëÀ²(Àüü DB Åë°è)
>>Com_insert 5.73% INSERT Äõ¸® »ç¿ëÀ²(Àüü DB Åë°è)
>>Com_select 27.29% SELECT Äõ¸® »ç¿ëÀ²(Àüü DB Åë°è)
>>Com_update 6.19% UPDATE Äõ¸® »ç¿ëÀ²(Àüü DB Åë°è)
>>Com_replace 0.00% REPLACE Äõ¸® »ç¿ëÀ²(Àüü DB Åë°è)
>>Com_lock_tables 0.00% LOCK TABLES Äõ¸® »ç¿ëÀ²(Àüü DB Åë°è)
>>Connections 1,740 ÃÑ connections ¼ö
>>Connections_per_sec 5.06 ÃÊ´ç connections ¼ö
>>Created_tmp_tables 0 ¸Þ¸ð¸®¿¡ »ý¼ºµÈ Àӽà Å×ÀÌºí ¼ö
>>Created_tmp_disk_tables 0 DISK¿¡ »ý¼ºµÈ Àӽà Å×ÀÌºí ¼ö, 33,554,432(32
MB)[tmp_table_size]º¸´Ù
¸¹Àº ¸Þ¸ð¸®¸¦ ¿ä±¸ÇÏ´Â SQL ±¸¹®À» ½ÇÇà½Ã ´©°èµÊ
>>Create_tmp_disk_tables_per 0.00% DISK¿¡ »ý¼ºµÈ Àӽà Å×ÀÌºí »ç¿ë ºñÀ²
>>Intervals_of_connection 0.198 Ä¿³Ø¼Ç ÁÖ±â(ÃÊ)
>>Intervals_of_question 0.052 Äõ¸® ÁÖ±â(ÃÊ)
>>Key_reads 570 DISK¿¡¼ Key ÀÐÀº ¼ö
>>Key_read_requests 9,334 ij½Ã¿¡¼ Key Àб⠿äû¼ö
>>Key_writes 270 DISK¿¡ Key¸¦ ¾´ ¼ö
>>Key_write_requests 270 ij½Ã¿¡ Key ¾²±â ¿äû¼ö
>>Key_reads_per_request 0.06% DISK¿¡¼ Key¸¦ Àд ºñÀ²,
>>DISK¿¡¼ Key¸¦ Àд ¿äûÀÌ ¸¹½À´Ï´Ù(±âÁØ 0.01%). 1,073,741,824(1,024
MB)(key_buffer_size)°ªÀ»
¿Ã¸®¼¼¿ä.
>>Key_writes_per_request 1.00% DISK¿¡ Key¸¦ ¾²´Â ºñÀ², º¸Åë 1¿¡ °¡±î¿ö¾ß Á¤»ó
>>Max_used_connections 10 µ¿½Ã¿¡ ¿¬°áµÈ ÃÖ´ë°ª, Á¤»ó
>>Open_tables 8 ÇöÀç ¿·ÁÀÖ´Â Tables ¼ö
>>Opened_tables 14 ¿·È´ø Tables ¼ö, refer 512[table_cache], 0.01/Connection
>>Questions 6,628 ÇöÀç±îÁö Äõ¸® ¿äû¼ö
>>Questions_per_connect 3.81 Ä¿³Ø¼Ç´ç Æò±Õ Äõ¸® ¿äû¼ö
>>Select_full_join 0 Key ¾øÀÌ FULL-JOIN Ƚ¼ö
>>Slow_queries 0 10(10ÃÊ)(long_query_time)ÃÊ
º¸´Ù Å« Äõ¸® ¿äû¼ö
>>Table_locks_waited 0ÃÊ Lock wait Ãѽð£
>>Threads_cached 5 ij½ÃµÈ ¾²·¹µå¼ö, 17[thread_cache_size]º¸´Ù
Ç×»ó ÀÛÀ½
>>Threads_connected 6 ÇöÀç ¿·ÁÀÖ´Â Ä¿³Ø¼Ç ¼ö
>>Threads_created 11 ÇöÀç±îÁö handle Ä¿³Ø¼Ç¿¡ »ý¼ºµÈ ¾²·¹µå ¼ö
>>Threads_running 1 ÇöÀç ±¸µ¿ÁßÀÎ ¾²·¹µå ¼ö(not sleeping)
>>Threads_created_per 0.01% Ä¿³Ø¼Ç´ç »ý¼ºµÈ Æò±Õ ¾²·¹µå ¼ö(±âÁØ 0.01%), Á¤»ó[Âü°í]
>>Uptime 5ºÐ 44ÃÊ ÃÖ±Ù MySQL ¼¹ö ±¸µ¿ ½Ã°£
>>
>>
>>MYSQL SERVER VARIABLES (connected by TCP/IP), ´©ÀûµÈ Åë°èÀÚ·á°¡ ³Ê¹« Àû½À´Ï´Ù.
Ç׸ñ VARIABLES commnets, [***] Ç¥½Ã´Â ¼º´É Çâ»ó°ú °ü·ÃµÊ
>>back_log 50 man 2 listen, net.ipv4.tcp_max_syn_backlog
>>connect_timeout 3(3ÃÊ) bad handshake timeout(ÃÊ), Á¤»ó
>>join_buffer_size 131,072(128 KB) [***] FULL-JOIN¿¡ »ç¿ëµÇ´Â ¸Þ¸ð¸®
>>key_buffer_size 1,073,741,824(1,024 MB) [***] INDEX key buffer¿¡ »ç¿ëµÇ´Â ¸Þ¸ð¸®,
refer [Key_xxx],
>>DISK¿¡¼ Key¸¦ Àд ¿äûÀÌ ¸¹½À´Ï´Ù(±âÁØ 0.01%). 1,073,741,824(1,024
MB)(key_buffer_size)°ªÀ»
¿Ã¸®¼¼¿ä.
>>log OFF ·Î±× ±â·Ï ¿©ºÎ
>>log_update OFF UPDATE ·Î±× ±â·Ï ¿©ºÎ
>>log_bin OFF binary ·Î±× ±â·Ï ¿©ºÎ
>>long_query_time 10(10ÃÊ) refer 0[Slow_queries]
>>lower_case_table_names 0 Å×ÀÌºí ´ë¼Ò¹®ÀÚ ±¸º°À¯¹«(0 ±¸º°)
>>max_allowed_packet 104,856,576(100 MB) ÃÖ´ë Çã¿ëÇÒ ÆÐŶ
>>max_connections 1,000 [***] ÃÖ´ë µ¿½Ã Á¢¼Ó Ä¿³Ø¼Ç ¼ö, refer
10[Max_used_connections], Á¤»ó
>>max_delayed_threads 20 ÃÖ´ë delayed ¾²·¹µå ¼ö, INSERT DELAYED ±¸¹®°ú °ü·ÃµÊ
>>max_join_size 18,446,744,073,709,557,000(17,179,869,136.0 GB) JOIN¿¡ »ç¿ëµÉ ÃÖ´ë
Å©±â(¸Þ¸ð¸® ¾Æ´Ô)
>>max_sort_length 1,024(1 KB) TEXT, BLOBÀÇ Á¤·Ä¿¡ »ç¿ëµÇ´Â ÃÖ´ë Å©±â
>>max_user_connections 1,000 ÃÖ´ë µ¿½Ã user ¼ö(0Àº Á¦ÇѾøÀ½)
>>myisam_max_extra_sort_file_size 268,435,456(256 MB) ºü¸¥ INDEX¸¦ »ý¼º½Ã »ç¿ëµÇ´Â
ÃÖ´ë Àӽà ÆÄÀÏ Å©±â
>>myisam_max_sort_file_size 9,223,372,036,854,779,000(8,589,934,568.0 GB) REPAIR,
ALTER, LOAD...µî INDEX¸¦ Àç»ý¼º½Ã »ç¿ëµÇ´Â Àӽà ÆÄÀÏ Å©±â
>>myisam_sort_buffer_size 67,108,864(64 MB) [***] REPAIR, INDEX, ALTER Á¤·Ä¿¡
»ç¿ëÇÏ´Â ¸Þ¸ð¸®
>>open_files_limit 5,010 ÆÄÀÏ open Á¦ÇÑ ¼ö(0Àº Á¦ÇѾøÀ½)
>>read_buffer_size 2,093,056(2 MB) [***] ¼øÂ÷ÀûÀÎ °Ë»ö¿¡ »ç¿ëµÇ´Â
¸Þ¸ð¸®(read_buffer_size)
>>read_rnd_buffer_size 262,144(256 KB) [***] ORDER BY Á¤·Ä¿¡ »ç¿ëµÇ´Â ¸Þ¸ð¸®(to
avoid a disk seeks)
>>skip_external_locking ON ¿ÜºÎ lock À» »ç¿ëÇÑ´Ù¸é OFF·Î ¼³Á¤.
>>skip_networking OFF TCP/IP ¿¬°á ¿©ºÎ(ONÀº UNIX domain socket, OFF´Â TCP/IP ¿¬°á),
Á¤»ó
>>skip_show_database OFF SHOW DATABASES ±¸¹® »ç¿ë ¿©ºÎ(ONÀº ¿øõºÀ¼â, OFF´Â
»ç¿ë°¡´É)
>>sort_buffer_size 2,097,144(2 MB) [***] ORDER BY, GROUP BY Á¤·Ä¿¡ »ç¿ëµÇ´Â ¸Þ¸ð¸®
>>table_cache 512 [***] Çѹø¿¡(all thread) ¿ ¼ö ÀÖ´Â Å×ÀÌºí ¼ö, refer
14[Opened_tables]
>>thread_cache_size 17 [***] ¾²·¹µå ij½Ã Àç»ç¿ë ¼ö, refer 1,740[Connections],
11[Threads_created], °è»êµÈ ÃßÁ¤°ª(ÃÖ¼Ò:6, ±ÇÀå:13, ÃÖ´ë:20), Á¤»ó[Âü°í]
>>tmp_table_size 33,554,432(32 MB) [***] º¹ÀâÇÑ GROUP BY Á¤·Ä¿¡ »ç¿ëµÇ´Â ¸Þ¸ð¸®(to
avoid a disk seeks), refer 0[Created_tmp_disk_tables]
>>interactive_timeout 28,800(8½Ã°£ 0ÃÊ) interactive -> re-active¿¡ ±â´Ù¸®´Â
½Ã°£(ÀÌÈÄ closed), Á¤»ó[Âü°í]
>>wait_timeout 28,800(8½Ã°£ 0ÃÊ) none interactive -> re-active¿¡ ±â´Ù¸®´Â ½Ã°£(ÀÌÈÄ
closed), °è»êµÈ ÃßÁ¤°ª(Ãß¼¼:2ÃÊ, ÀÓ°è:74ÃÊ), Á¤»ó[Âü°í]
>>timezone KST ÇöÀç MySQL ¼¹öÀÇ TIME-ZONE
>>version 4.0.26 ÇöÀç MySQL ¼¹ö ¹öÀü
>
>========================================
========================================
|