æè¿ãMySQLã®ãã©ã¡ã¼ã¿ã®èª¿æ´ãããæ©ä¼ããã£ãã®ã§ãããç¹å®ã®ãã©ã¡ã¼ã¿ãå¤æ´ããéã«ãã¡ã¢ãªã®æ¶è²»éã«ã©ãå½±é¿ããã®ããã¨ããã®ã調æ»ããéã«ãã¤ã³ã¿ã¼ãããã彷徨ã£ãã¨ããããµã¤ãã«ãã£ã¦æ¸ãã¦ãããã¨ã«ãã©ã¤ãããã£ãã®ã§ãèªåã§ãã¾ã¨ãã¦ã¿ããã¨ã«ããã
çµè«ããæ¸ãã¨ãåèã«ããã®ã¯ä»¥ä¸ã®ãªã©ã¤ãªã¼ã®æ¸ç±ãMySQLãã©ãã«ã·ã¥ã¼ãã£ã³ã°ãã§ãè¨è¿°ãä¸çªããããããæ¸ããã¦ããã
ãã®ã¨ã³ããªã¯ããã®æ¸ç±ã® ã3.9.3 ãªãã·ã§ã³ã®å®å
¨å¤ãè¨ç®ããã ã«ã¦è¨è¼ãããå
容ãã¾ã¨ãããã®ã«ãªãã
èè ã«ã¤ãã¦
Sveta Smirnova(ã¹ã´ã§ã¼ã¿ã»ã¹ãã«ãã´ã¡): Oracle社MySQLãµãã¼ãã°ã«ã¼ãã»ãã°æ¤è¨¼ã°ã«ã¼ãã®ä¸»å¸ãã¯ãã«ã«ãµãã¼ãã¨ã³ã¸ãã¢ã¨ãã¦æ¯æ¥MySQLã®ãã°ä¿®æ£ã¨ãµãã¼ããæ å½ããã
çºå£²ã¯2012å¹´ã¨å°ãå¤ãæ¬ã§ãæ¬æ¸ã«ããã¨å¯¾è±¡ã¨ãã¦ããMySQLã®ãã¼ã¸ã§ã³ã¯5.5ã®æ¨¡æ§ããªã®ã§ãå°ãå¤ãè¨è¿°ã§ã¯ããã¨æãã
å°ãMySQL 5.7 ã 8.0 ã«ã¤ãã¦ã¯ã以ä¸ã®å ¬å¼ããã¥ã¡ã³ãã«è¿ããå 容ããããã¦ããï¼ã¨æãï¼ã
- MySQL :: MySQL 5.7 Reference Manual :: 8.12.4.1 How MySQL Uses Memory
- MySQL :: MySQL 8.0 Reference Manual :: 8.12.3.1 How MySQL Uses Memory
ã¡ã¢ãªä½¿ç¨éã®ãã£ããè¨ç®å¼
ã¡ã¢ãªä½¿ç¨é = ã°ãã¼ãã«ãããã¡ã®ä½¿ç¨é + max_connections * ã¹ã¬ãããããã¡ã®ä½¿ç¨é
ä¸è¨ã«å ãã¦ãOSã®ã¡ã¢ãªä½¿ç¨éçãå ãããã®ãããµã¼ãã®ç©çã¡ã¢ãªéãè¶
ããªãããã«èª¿æ´ããå¿
è¦ãããã¾ãã
ã°ãã¼ãã«ãããã¡
ãã®ãªãã·ã§ã³ã¯ã°ãã¼ãã«ã§ããã¹ã¦ã®æ¥ç¶ã¨ã¯ã¨ãªã«å½±é¿ã
ãµã¼ãã®èµ·åæã«å²ãå½ã¦ããããã®ãããã°ã使ç¨ããªããå¢ãã¦ãããªãã·ã§ã³ (ã¯ã¨ãªãã£ãã·ã¥ãªã©) ãããã
- query_cache_size
- innodb_additional_mem_pool_size
- innodb_buffer_pool_size
- innodb_log_buffer_size
- key_buffer_size
(åè) ã°ãã¼ãã«ã§çç¥ã§ãããã©ã¡ã¼ã¿
ãµã¼ãã«ã¯ããã¡ã¤ã«è¨è¿°åã®æ°ãå¶éãããªãã·ã§ã³ã¨ãã£ãã·ã¥ãããã¹ã¬ããã®æ°ãå¶éãããªãã·ã§ã³ãããããã¢ãã³ãªã·ã¹ãã ã§ã¯ç¡è¦ã§ããã»ã©å°ããã®ã§ãçç¥ã§ããã¨ã®ãã¨ã以ä¸ããã®ãªã¹ãã
- thread_cache_size
- table_definition_cache
- table_open_cache
- innodb_open_files
ã¹ã¬ãããããã¡
åºæ¬çã«ã¹ã¬ãããã¨ã«å²ãå½ã¦ããããããã¡
- net_buffer_length
- thread_stack
- query_prealloc_size
- binlog_cache_size
- binlog_stmt_cache_size
ç¹å®å¦çã«å²ãå½ã¦ããããããã¡
ãããã®ãããã¡ã¯ããµã¼ããç¹å®ã®å¦çãå®è¡ããªããã°ãããªãæã«ãå¿
è¦ã«å¿ãã¦å²ãå½ã¦ãããã
å²ãå½ã¦ãããã¡ã¢ãªéãæ£ç¢ºã«è¨ç®ããã®ã¯é£ãããã¯ã¨ãªãåæãã¦ãã©ãã大éã®ãªã½ã¼ã¹ãè¦æ±ãã¦ãããã調ã¹ã¦ã以ä¸ã®ãããªè¨ç®ãè¡ãã
(ãããã¡ãµã¤ãº) * (ç¹å®ã®ã¯ã¨ãªã«å²ãå½ã¦ããããããã¡æ°) * (並åãã¦å®è¡ãããã¯ã¨ãªã®æ°)
ãããå ¨ã¦ã®å¤æ°ã§å®è¡ããçµæã®åè¨ãè¨ç®ããã
ãããã®ãªãã·ã§ã³ã¯ãã»ã¨ãã©ã®ã¯ã¨ãªã§å°ããªéã«æãã¦ããã®ãè¯ãããããç¹å®ã®ã¯ã¨ãªã§ããå¤ãã®ã¡ã¢ãªãå¿
è¦ãªå ´åã¯ããã®å¤æ°ã®å¤ãã»ãã·ã§ã³ãã¨ã«å¢ããã®ãè¯ãã(ãã®ã«ãã£ã¦ã¯ã°ãã¼ãã«ã«è¨å®ãã¦ãæå³ããªãã®ã§ãã¯ã¨ãªãå®è¡ããç´åã«è¨å®ããã)
ã¹ã¬ãããã¨ã«1åå²ãå½ã¦ããããªãã·ã§ã³
- read_rnd_buffer_size
- sort_buffer_size
- myisam_mmap_size
- myisam_sort_buffer_size
- bulk_insert_buffer_size
- preload_buffer_size
ã¹ã¬ãããã¨ã«è¤æ°åå²ãå½ã¦ããããªãã·ã§ã³
- join_buffer_size
- read_buffer_size
- tmp_table_size
MySQL ãæ¶è²»ããã¡ã¢ãªã®æ大éãè¨ç®ããå ¬å¼ (MB表è¨)
Set @join_tables = 1ã¹ã¬ãããããã§JOINããããã¼ãã«æ°ã®è¦ç©; Set @scan_tables = 1ã¹ã¬ãããããã®ãã¼ãã«ã¹ãã£ã³æ°ã®è¦ç©; Set @tmp_tables = 1ã¹ã¬ãããããã§ä½æãããä¸æãã¼ãã«æ°ã®è¦ç©; SELECT(@@query_cache_size + @@innodb_additional_mem_pool_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size + @@max_connections * (@@global.net_buffer_length + @@thread_stack + @@global.query_prealloc_size + @@global.read_rnd_buffer_size + @@global.sort_buffer_size + @@myisam_mmap_size + @@global.myisam_sort_buffer_size + @@global.bulk_insert_buffer_size + @@global.preload_buffer_size + @@binlog_cache_size + @@binlog_stmt_cache_size + @@global.join_buffer_size * IFNULL(@join_tables, 1) + @@global.read_buffer_size * IFNULL(@scan_tables, 1)+ @@global.tmp_table_size * IFNULL(@tmp_tables, 1)))/ (1024*1024);
(åè) MySQLã®åãªãã·ã§ã³ã®è¨å®å¤ã確èªããä¾
mysql> SHOW GLOBAL VARIABLES LIKE '%buffer%';
(追è¨) éã¾ã£ããæè¦
yokuããã¨ããnippondanjiããã¨ããåããããã¨MySQLç¥ã¯ã©ã¹ã®æ¹ãããã£ã¼ãããã¯ãããã¦ããããããéåç¥ã
æè¿ã®ã¯ããã«ï¼ã°ãã¼ãã«ãããã¡ã¨ãã¦ï¼performance_schemaã®ãã¼ãã«åãè¼ã£ã¦ããã®ã§ãæ°ãã¤ããã ããï¼
— yoku0825 (@yoku0825) June 21, 2021
mallocã«ä¾åãã¦ãé½åä¸ãlibcå é¨ã«ãçµæ§ã¡ã¢ãªããã¾ã£ã¦ãã¾ãã£ã¦ã®ã注æãå¿ è¦ãå人çã«ã¯ãã³ããã¼ã¯ãã¦æ¬²ããããæåã¯æ§ããã«ãããã¡ãå²ãå½ã¦ã¦ãæ§åãè¦ã¦ä½è£ããããªãå²ãå½ã¦ãå¢ããéç¨ããªã¹ã¹ã¡ã
— Mikiya Okuno (@nippondanji) June 21, 2021
åèã«ããã¦ããã ããæ¸ç±ã»ãªã³ã¯