¿¡°ø ¾Æ·¡ °Ô½Ã¹°ÀÌ ÀÌ»óÇÏ°Ô ¿Ã¶ó°¬³×¿ä ¤Ð¤Ð
¾ÏÈ£¼³Á¤À» ¾ÈÇؼ ¼öÁ¤ »èÁ¦°¡ ¾ÈµÇ³×¿ä
»èÁ¦ÇØÁÖ¼¼¿ä Á˼ÛÇÕ´Ï´Ù.
»êÀÌ´Ô²²¼ ¹èÆ÷ÇϽŠmysql-status¸¦ µ¹·ÁºÃ´Âµ¥¿ä
key_buffer_size¸¦ ¿Ã¸®¶ó°í ³ª¿Ô½À´Ï´Ù.
±×·±µ¥
key_buffer_size +(read_buffer_size + sort_buffer_size) * max_connections
= 512M + (4M + 4M ) * 512
°ªÀÌ ¹°¸®Àû ¸Þ¸ð¸®(4G)À» Á¶±Ý ³Ñ´Âµ¥
¿©±â¼ ´õ key_buffer_size°ªÀ» ¿Ã¸®¸é ½Ã½ºÅÛ¿¡ ¹«¸®°¡ µÇ°ÚÁÒ??
ÀÌ·² °æ¿ì´Â ¾î¶»°Ô ÇؾßÇÏ´ÂÁö
Âü°í·Î DB¼¹ö´Â 3´ë·Î ÇÑ°³´Â ÄÚ¾î·Î ¾²°í ÀÖ°í
2´ë´Â ¸¶½ºÅÍ¿Í ½½·¹À̺ê·Î ¸®Çø®ÄÉÀÌ¼Ç µÇ¾îÀÖ½À´Ï´Ù.
¸¶½ºÅÍÂÊ mysql status¸¦ °°ÀÌ Ã·ºÎÇÏ°Ú½À´Ï´Ù.
MYSQL SERVER
STATUS (connected by TCP/IP)
Ç׸ñ |
STATUS |
commnets, [***] Ç¥½Ã´Â ¼º´É Çâ»ó°ú °ü·ÃµÊ |
TOTAL_STATUS |
284.84 |
hot busy (1ÃÊ´ç Æò±Õ Äõ¸® ¿äû¼ö) |
CRITICAL_LIFE_TIME |
5ÃÊ |
Ä¿³Ø¼Ç´ç Æò±ÕÀûÀÎ(Ãß¼¼) ÀÓ°è life time(CUR) |
CRITICAL_LIFE_TIME_MAX |
13ÃÊ |
Ä¿³Ø¼Ç´ç Æò±ÕÀûÀÎ(ÃÖ´ë) ÀÓ°è life time(EXP) |
All_databases |
2 or more |
µ¥ÀÌÅͺ£À̽º ¼ö |
Aborted_clients |
376 |
¿¬°á Ãë¼Ò Clients ¼ö, refer 1,800(30ºÐ 0ÃÊ)[wait_timeout] and
28,800(8½Ã°£ 0ÃÊ)[interactive_timeout] |
Aborted_connects |
0 |
¿¬°á ½ÇÆмö, refer 1,800(30ºÐ 0ÃÊ)[connect_timeout] |
Aborted_clients_percent |
0.01% |
¿¬°á Ãë¼ÒÀ², Á¤»ó[Âü°í] |
Aborted_connects_percent |
0.00% |
¿¬°á ½ÇÆÐÀ², Á¤»ó |
Bytes_received |
182 Bytes |
¼ö½Å·®(ÃÑ) |
Bytes_sent |
17 KB |
Àü¼Û·®(ÃÑ) |
Bytes_per_rs |
97.53 |
¹Þ°í(1) º¸³»´Â(x) ºñÀ², 1:x |
Bytes_sent_per_sec |
0 Bytes |
Àü¼Û·®(ÃÊ´ç) |
Bytes_sent_per_min |
3 Bytes |
Àü¼Û·®(ºÐ´ç) |
Bytes_sent_per_hour |
205 Bytes |
Àü¼Û·®(½Ã°£´ç) |
Bytes_sent_per_day |
5 KB |
Àü¼Û·®(ÇÏ·çÆò±Õ) |
Com_delete |
0.00% |
DELETE Äõ¸® »ç¿ëÀ²(Àüü DB Åë°è) |
Com_insert |
0.00% |
INSERT Äõ¸® »ç¿ëÀ²(Àüü DB Åë°è) |
Com_select |
0.00% |
SELECT Äõ¸® »ç¿ëÀ²(Àüü DB Åë°è) |
Com_update |
0.00% |
UPDATE Äõ¸® »ç¿ëÀ²(Àüü DB Åë°è) |
Com_replace |
0.00% |
REPLACE Äõ¸® »ç¿ëÀ²(Àüü DB Åë°è) |
Com_lock_tables |
0.00% |
LOCK TABLES Äõ¸® »ç¿ëÀ²(Àüü DB Åë°è) |
Connections |
6,798,543 |
ÃÑ connections ¼ö |
Connections_per_sec |
21.86 |
ÃÊ´ç connections ¼ö |
Created_tmp_tables |
3 |
¸Þ¸ð¸®¿¡ »ý¼ºµÈ Àӽà Å×ÀÌºí ¼ö |
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.046 |
Ä¿³Ø¼Ç ÁÖ±â(ÃÊ) |
Intervals_of_question |
0.004 |
Äõ¸® ÁÖ±â(ÃÊ) |
Key_reads |
245,641 |
DISK¿¡¼ Key ÀÐÀº ¼ö |
Key_read_requests |
11,759,327 |
ij½Ã¿¡¼ Key Àб⠿äû¼ö |
Key_writes |
2,248,261 |
DISK¿¡ Key¸¦ ¾´ ¼ö |
Key_write_requests |
2,254,178 |
ij½Ã¿¡ Key ¾²±â ¿äû¼ö |
Key_reads_per_request |
0.02% |
DISK¿¡¼ Key¸¦ Àд ºñÀ², DISK¿¡¼
Key¸¦ Àд ¿äûÀÌ
¸¹½À´Ï´Ù(±âÁØ 0.01%). 536,870,912(512 MB)(key_buffer_size)°ªÀ»
¿Ã¸®¼¼¿ä. |
Key_writes_per_request |
1.00% |
DISK¿¡ Key¸¦ ¾²´Â ºñÀ², º¸Åë 1¿¡ °¡±î¿ö¾ß Á¤»ó |
Max_used_connections |
162 |
µ¿½Ã¿¡ ¿¬°áµÈ ÃÖ´ë°ª, Á¤»ó |
Open_tables |
394 |
ÇöÀç ¿·ÁÀÖ´Â Tables ¼ö |
Opened_tables |
0 |
¿·È´ø Tables ¼ö, refer 2,048[table_cache],
0.00/Connection |
Questions |
88,604,366 |
ÇöÀç±îÁö Äõ¸® ¿äû¼ö |
Questions_per_connect |
13.03 |
Ä¿³Ø¼Ç´ç Æò±Õ Äõ¸® ¿äû¼ö |
Select_full_join |
0 |
Key ¾øÀÌ FULL-JOIN Ƚ¼ö |
Slow_queries |
0 |
2(2ÃÊ)(long_query_time)ÃÊ
º¸´Ù Å« Äõ¸® ¿äû¼ö |
Table_locks_waited |
1½Ã°£ 17ºÐ 17ÃÊ |
Lock wait Ãѽð£ |
Threads_cached |
5 |
ij½ÃµÈ ¾²·¹µå¼ö, 8[thread_cache_size]º¸´Ù
Ç×»ó ÀÛÀ½ |
Threads_connected |
32 |
ÇöÀç ¿·ÁÀÖ´Â Ä¿³Ø¼Ç ¼ö |
Threads_created |
188,724 |
ÇöÀç±îÁö handle Ä¿³Ø¼Ç¿¡ »ý¼ºµÈ ¾²·¹µå ¼ö |
Threads_running |
2 |
ÇöÀç ±¸µ¿ÁßÀÎ ¾²·¹µå ¼ö(not sleeping) |
Threads_created_per |
0.03% |
Ä¿³Ø¼Ç´ç »ý¼ºµÈ Æò±Õ ¾²·¹µå ¼ö(±âÁØ 0.01%), 8[thread_cache_size]°ªÀ»
¿Ã¸®¼¼¿ä.[Âü°í] |
Uptime |
3ÀÏ 14½Ã°£ 24ºÐ 24ÃÊ |
ÃÖ±Ù MySQL ¼¹ö ±¸µ¿
½Ã°£ | | MYSQL
SERVER
VARIABLES (connected by TCP/IP)
Ç׸ñ |
VARIABLES |
commnets, [***] Ç¥½Ã´Â ¼º´É Çâ»ó°ú °ü·ÃµÊ |
back_log |
50 |
man 2 listen, net.ipv4.tcp_max_syn_backlog |
connect_timeout |
1,800(30ºÐ
0ÃÊ) |
bad handshake timeout(ÃÊ), Á¤»ó |
join_buffer_size |
4,190,208(4 MB) |
[***] FULL-JOIN¿¡ »ç¿ëµÇ´Â ¸Þ¸ð¸® |
key_buffer_size |
536,870,912(512 MB) |
[***] INDEX key buffer¿¡ »ç¿ëµÇ´Â ¸Þ¸ð¸®, refer [Key_xxx], DISK¿¡¼
Key¸¦ Àд ¿äûÀÌ ¸¹½À´Ï´Ù(±âÁØ 0.01%).
536,870,912(512 MB)(key_buffer_size)°ªÀ»
¿Ã¸®¼¼¿ä. |
log |
OFF |
·Î±× ±â·Ï ¿©ºÎ |
log_update |
|
UPDATE ·Î±× ±â·Ï ¿©ºÎ |
log_bin |
ON |
binary ·Î±× ±â·Ï ¿©ºÎ, Replication(¹Ì·¯¸µ)±â¹ýÀ»
»ç¿ëÇÏÁö
¾Ê´Â´Ù¸é ÀÌ ±â´ÉÀ» OFF Çϼ¼¿ä. |
long_query_time |
2(2ÃÊ) |
refer 0[Slow_queries] |
lower_case_table_names |
0 |
Å×ÀÌºí ´ë¼Ò¹®ÀÚ ±¸º°À¯¹«(0 ±¸º°) |
max_allowed_packet |
16,776,192(16 MB) |
ÃÖ´ë Çã¿ëÇÒ ÆÐŶ |
max_connections |
512 |
[***] ÃÖ´ë µ¿½Ã Á¢¼Ó Ä¿³Ø¼Ç ¼ö, refer 162[Max_used_connections], Á¤»ó |
max_delayed_threads |
20 |
ÃÖ´ë delayed ¾²·¹µå ¼ö, INSERT DELAYED ±¸¹®°ú °ü·ÃµÊ |
max_join_size |
18,446,744,073,709,552,000(17,179,869,136.0
GB) |
JOIN¿¡ »ç¿ëµÉ ÃÖ´ë Å©±â(¸Þ¸ð¸® ¾Æ´Ô) |
max_sort_length |
1,024(1 KB) |
TEXT, BLOBÀÇ Á¤·Ä¿¡ »ç¿ëµÇ´Â ÃÖ´ë Å©±â |
max_user_connections |
0 |
ÃÖ´ë µ¿½Ã user ¼ö(0Àº Á¦ÇѾøÀ½) |
myisam_max_extra_sort_file_size |
0(0 Bytes) |
ºü¸¥ INDEX¸¦ »ý¼º½Ã »ç¿ëµÇ´Â ÃÖ´ë Àӽà ÆÄÀÏ Å©±â |
myisam_max_sort_file_size |
9,223,372,036,854,776,000(8,589,934,568.0
GB) |
REPAIR, ALTER, LOAD...µî INDEX¸¦ Àç»ý¼º½Ã »ç¿ëµÇ´Â Àӽà ÆÄÀÏ
Å©±â |
myisam_sort_buffer_size |
8,388,608(8 MB) |
[***] REPAIR, INDEX, ALTER Á¤·Ä¿¡ »ç¿ëÇÏ´Â ¸Þ¸ð¸® |
open_files_limit |
8,192 |
ÆÄÀÏ open Á¦ÇÑ ¼ö(0Àº Á¦ÇѾøÀ½) |
read_buffer_size |
4,190,208(4 MB) |
[***] ¼øÂ÷ÀûÀÎ °Ë»ö¿¡ »ç¿ëµÇ´Â ¸Þ¸ð¸®(read_buffer_size) |
read_rnd_buffer_size |
16,773,120(16 MB) |
[***] 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 |
4,194,296(4 MB) |
[***] ORDER BY, GROUP BY Á¤·Ä¿¡ »ç¿ëµÇ´Â ¸Þ¸ð¸® |
table_cache |
2,048 |
[***] Çѹø¿¡(all thread) ¿ ¼ö ÀÖ´Â Å×ÀÌºí ¼ö, refer
0[Opened_tables] |
thread_cache_size |
8 |
[***] ¾²·¹µå ij½Ã Àç»ç¿ë ¼ö, refer 6,798,543[Connections],
188,724[Threads_created], °è»êµÈ ÃßÁ¤°ª(ÃÖ¼Ò:22, ±ÇÀå:45, ÃÖ´ë:68), 8[thread_cache_size]°ªÀ»
¿Ã¸®¼¼¿ä.[Âü°í] |
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 |
1,800(30ºÐ
0ÃÊ) |
none interactive -> re-active¿¡ ±â´Ù¸®´Â ½Ã°£(ÀÌÈÄ closed), °è»êµÈ
ÃßÁ¤°ª(Ãß¼¼:5ÃÊ, ÀÓ°è:13ÃÊ), Á¤»ó[Âü°í] |
timezone |
|
ÇöÀç MySQL ¼¹öÀÇ TIME-ZONE |
version |
5.0.18-standard-log |
ÇöÀç MySQL ¼¹ö
¹öÀü | |
|