{"id":1148,"date":"2011-12-21T15:43:00","date_gmt":"2011-12-21T07:43:00","guid":{"rendered":"\/?p=1148"},"modified":"2012-01-13T14:49:42","modified_gmt":"2012-01-13T06:49:42","slug":"mysql-tunning-performance","status":"publish","type":"post","link":"https:\/\/blog.vimge.com\/archives\/mysql\/mysql-tunning-performance.html","title":{"rendered":"MySQL\u670d\u52a1\u5668\u5b89\u88c5\u5b8c\u4e4b\u540e\u5982\u4f55\u8c03\u8282\u6027\u80fd"},"content":{"rendered":"

\u770b\u4e86\u539f\u6587\u89c9\u5f97\u633a\u5b9e\u7528\u7684\uff0c\u786e\u5b9e\u5bf9\u4e8eDB\u65b0\u519c\u6765\u8bb2\uff0c\u7528\u7cfb\u7edf\u9ed8\u8ba4\u914d\u7f6e\u6216\u8005MySQL\u81ea\u5e26\u7684\u51e0\u4e2a\u914d\u7f6e\u6a21\u677f\u6bd4\u8f83\u591a\uff0c\u9876\u591a\u4f1a\u53bb\u8c03\u51e0\u4e2a\u53c2\u6570\u3002\u8fd9\u7f16\u535a\u5ba2\u5217\u51fa\u7684\u70b9\u5982\u4f5c\u8005\u6240\u8ff0\uff0c\u201cMySQL\u53ef\u8c03\u7684\u53c2\u6570\u786e\u5b9e\u4e0d\u5c11\uff0c\u4e0d\u8fc7\u771f\u6b63\u5bf9\u7cfb\u7edf\u6027\u80fd\u6709\u975e\u5e38\u663e\u8457\u7684\u5f71\u54cd\u5c31\u90a3\u4e48\u51e0\u4e2a\u201d\u3002<\/p>\n

key_buffer_size<\/strong>
\ninnodb_buffer_pool_size
\n<\/strong>innodb_additional_mem_pool_size
\n<\/strong>innodb_log_file_size<\/strong>
\ninnodb_log_buffer_size<\/strong>
\ninnodb_flush_log_at_trx_commit<\/strong>
\ntable_cache<\/strong>
\nthread_cache<\/strong>
\nquery_cache_size<\/strong><\/p><\/blockquote>\n


\n\u539f\u6587\uff1a<\/strong>
\nhttp:\/\/www.mysqlperformanceblog.com\/2006\/09\/29\/what-to-tune-in-mysql-server-after-installation\/<\/a>
\nMy favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings.<\/p>\n

\u5728\u9762\u8bd5MySQL DBA\u6216\u8005\u90a3\u4e9b\u6253\u7b97\u505aMySQL\u6027\u80fd\u4f18\u5316\u7684\u4eba\u65f6\uff0c\u6211\u6700\u559c\u6b22\u95ee\u9898\u662f\uff1aMySQL\u670d\u52a1\u5668\u6309\u7167\u9ed8\u8ba4\u8bbe\u7f6e\u5b89\u88c5\u5b8c\u4e4b\u540e\uff0c\u5e94\u8be5\u505a\u54ea\u4e9b\u65b9\u9762\u7684\u8c03\u8282\u5462\uff1f<\/p>\n

I\u2019m surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with default settings.<\/p>\n

\u4ee4\u6211\u5f88\u60ca\u8bb6\u7684\u662f\uff0c\u6709\u591a\u5c11\u4eba\u5bf9\u8fd9\u4e2a\u95ee\u9898\u65e0\u6cd5\u7ed9\u51fa\u5408\u7406\u7684\u7b54\u6848\uff0c\u53c8\u6709\u591a\u5c11\u670d\u52a1\u5668\u90fd\u8fd0\u884c\u5728\u9ed8\u8ba4\u7684\u8bbe\u7f6e\u4e0b\u3002<\/p>\n

Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right other changes will most commonly offer only incremental performance improvements.<\/p>\n

\u5c3d\u7ba1\u4f60\u53ef\u4ee5\u8c03\u8282\u5f88\u591aMySQL\u670d\u52a1\u5668\u4e0a\u7684\u53d8\u91cf\uff0c\u4f46\u662f\u5728\u5927\u591a\u6570\u901a\u5e38\u7684\u5de5\u4f5c\u8d1f\u8f7d\u4e0b\uff0c\u53ea\u6709\u5c11\u6570\u51e0\u4e2a\u624d\u771f\u6b63\u91cd\u8981\u3002\u5982\u679c\u4f60\u628a\u8fd9\u4e9b\u53d8\u91cf\u8bbe\u7f6e\u6b63\u786e\u4e86\uff0c\u90a3\u4e48\u4fee\u6539\u5176\u4ed6\u53d8\u91cf\u6700\u591a\u53ea\u80fd\u5bf9\u7cfb\u7edf\u6027\u80fd\u6539\u5584\u6709\u4e00\u5b9a\u63d0\u5347\u3002<\/p>\n

key_buffer_size<\/strong>\u00a0– Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time – it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you\u2019ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.<\/p>\n

key_buffer_size<\/strong>\u00a0– \u8fd9\u5bf9MyISAM\u8868\u6765\u8bf4\u975e\u5e38\u91cd\u8981\u3002\u5982\u679c\u53ea\u662f\u4f7f\u7528MyISAM\u8868\uff0c\u53ef\u4ee5\u628a\u5b83\u8bbe\u7f6e\u4e3a\u53ef\u7528\u5185\u5b58\u7684 30-40%\u3002\u5408\u7406\u7684\u503c\u53d6\u51b3\u4e8e\u7d22\u5f15\u5927\u5c0f\u3001\u6570\u636e\u91cf\u4ee5\u53ca\u8d1f\u8f7d — \u8bb0\u4f4f\uff0cMyISAM\u8868\u4f1a\u4f7f\u7528\u64cd\u4f5c\u7cfb\u7edf\u7684\u7f13\u5b58\u6765\u7f13\u5b58\u6570\u636e\uff0c\u56e0\u6b64\u9700\u8981\u7559\u51fa\u90e8\u5206\u5185\u5b58\u7ed9\u5b83\u4eec\uff0c\u5f88\u591a\u60c5\u51b5\u4e0b\u6570\u636e\u6bd4\u7d22\u5f15\u5927\u591a\u4e86\u3002\u5c3d\u7ba1\u5982\u6b64\uff0c\u9700\u8981\u603b\u662f\u68c0\u67e5\u662f\u5426\u6240\u6709\u7684 key_buffer \u90fd\u88ab\u5229\u7528\u4e86 — .MYI \u6587\u4ef6\u53ea\u6709 1GB\uff0c\u800c key_buffer \u5374\u8bbe\u7f6e\u4e3a 4GB \u7684\u60c5\u51b5\u662f\u975e\u5e38\u5c11\u7684\u3002\u8fd9\u4e48\u505a\u592a\u6d6a\u8d39\u4e86\u3002\u5982\u679c\u4f60\u5f88\u5c11\u4f7f\u7528MyISAM\u8868\uff0c\u90a3\u4e48\u4e5f\u4fdd\u7559\u4f4e\u4e8e 16-32MB \u7684 key_buffer_size \u4ee5\u9002\u5e94\u7ed9\u4e88\u78c1\u76d8\u7684\u4e34\u65f6\u8868\u7d22\u5f15\u6240\u9700\u3002<\/span><\/p>\n

innodb_buffer_pool_size<\/strong>\u00a0This is very important variable to tune if you\u2019re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply – if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.<\/p>\n

innodb_buffer_pool_size<\/strong>\u00a0– \u8fd9\u5bf9Innodb\u8868\u6765\u8bf4\u975e\u5e38\u91cd\u8981\u3002Innodb\u76f8\u6bd4MyISAM\u8868\u5bf9\u7f13\u51b2\u66f4\u4e3a\u654f\u611f\u3002MyISAM\u53ef\u4ee5\u5728\u9ed8\u8ba4\u7684 key_buffer_size \u8bbe\u7f6e\u4e0b\u8fd0\u884c\u7684\u53ef\u4ee5\uff0c\u7136\u800cInnodb\u5728\u9ed8\u8ba4\u7684 innodb_buffer_pool_size \u8bbe\u7f6e\u4e0b\u5374\u8ddf\u8717\u725b\u4f3c\u7684\u3002\u7531\u4e8eInnodb\u628a\u6570\u636e\u548c\u7d22\u5f15\u90fd\u7f13\u5b58\u8d77\u6765\uff0c\u65e0\u9700\u7559\u7ed9\u64cd\u4f5c\u7cfb\u7edf\u592a\u591a\u7684\u5185\u5b58\uff0c\u56e0\u6b64\u5982\u679c\u53ea\u9700\u8981\u7528Innodb\u7684\u8bdd\u5219\u53ef\u4ee5\u8bbe\u7f6e\u5b83\u9ad8\u8fbe 70-80% \u7684\u53ef\u7528\u5185\u5b58\u3002\u4e00\u4e9b\u5e94\u7528\u4e8e key_buffer \u7684\u89c4\u5219\u6709 — \u5982\u679c\u4f60\u7684\u6570\u636e\u91cf\u4e0d\u5927\uff0c\u5e76\u4e14\u4e0d\u4f1a\u66b4\u589e\uff0c\u90a3\u4e48\u65e0\u9700\u628a innodb_buffer_pool_size \u8bbe\u7f6e\u7684\u592a\u5927\u4e86\u3002<\/p>\n

\u672c\u6b21\u5bf9\u6211\u4eec\u7684\u670d\u52a1\u5668\u8fdb\u884c\u6027\u80fd\u8c03\u4f18\u7684\u65f6\u5019\uff0c\u53d1\u73b0\u7cfb\u7edf\u7684\u7a7a\u95f2\u5185\u5b58\u5f88\u591a\uff08\u6211\u4eec\u670d\u52a1\u566816G\u5185\u5b58\uff0c\u4f46\u662f\u670d\u52a1\u5668\u53ea\u7528\u4e863\u4e2aG\uff09\uff0c\u901a\u8fc7\u76d1\u63a7\u53d1\u73b0page out \u5f88\u9ad8\u8fbe\u5230\u6bcf\u79d21\u4e07\u591a\u6b21\uff0c\u6839\u636e\u7ecf\u9a8c\u5f97\u77e5\uff0c\u4e00\u822cpage out\u9ad8\u662f\u7531\u4e8e\u5185\u5b58\u4e0d\u8db3\u5f15\u8d77\u7684\uff0c\u4f46\u662f\u6211\u4eec\u670d\u52a1\u5668\u8fd8\u670910\u591a\u4e2aG\u6ca1\u7528\uff0c\u6240\u4ee5\u731c\u60f3\u8fd9\u80af\u5b9a\u662f\u7531\u4e8e\u6570\u636e\u5e93\u7684\u914d\u7f6e\u95ee\u9898\uff0c\u7ecf\u8fc7\u6392\u67e5\u53d1\u73b0\uff0c\u6211\u4eec\u7684innodb_buffer_pool_size<\/strong>\u00a0\u624d\u7ed9\u4e868M\u7684\u5185\u5b58\uff0c\u540e\u6765\u4fee\u6539\u4e86\u4e00\u4e0b\u8fd9\u4e2a\u95ee\u9898\u5c31\u7acb\u9a6c\u89e3\u51b3\u4e86\uff0c\u6240\u4ee5\u8bf4innodb_buffer_pool_size<\/strong>\u00a0\u8fd9\u4e2a\u53c2\u6570\u5bf9mysql\u7684\u6027\u80fd\u5f71\u54cd\u662f\u76f8\u5f53\u7684\u5927\u3002<\/span><\/p>\n

innodb_additional_pool_size<\/strong>\u00a0This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.<\/p>\n

innodb_additional_pool_size<\/strong>\u00a0– \u8fd9\u4e2a\u9009\u9879\u5bf9\u6027\u80fd\u5f71\u54cd\u5e76\u4e0d\u592a\u591a\uff0c\u81f3\u5c11\u5728\u6709\u5dee\u4e0d\u591a\u8db3\u591f\u5185\u5b58\u53ef\u5206\u914d\u7684\u64cd\u4f5c\u7cfb\u7edf\u4e0a\u662f\u8fd9\u6837\u3002\u4e0d\u8fc7\u5982\u679c\u4f60\u4ecd\u7136\u60f3\u8bbe\u7f6e\u4e3a 20MB(\u6216\u8005\u66f4\u5927)\uff0c\u56e0\u6b64\u5c31\u9700\u8981\u770b\u4e00\u4e0bInnodb\u5176\u4ed6\u9700\u8981\u5206\u914d\u7684\u5185\u5b58\u6709\u591a\u5c11\u3002<\/p>\n

innodb_log_file_size<\/strong>\u00a0Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.<\/p>\n

innodb_log_file_size<\/strong>\u00a0\u5728\u9ad8\u5199\u5165\u8d1f\u8f7d\u5c24\u5176\u662f\u5927\u6570\u636e\u96c6\u7684\u60c5\u51b5\u4e0b\u5f88\u91cd\u8981\u3002\u8fd9\u4e2a\u503c\u8d8a\u5927\u5219\u6027\u80fd\u76f8\u5bf9\u8d8a\u9ad8\uff0c\u4f46\u662f\u8981\u6ce8\u610f\u5230\u53ef\u80fd\u4f1a\u589e\u52a0\u6062\u590d\u65f6\u95f4\u3002\u6211\u7ecf\u5e38\u8bbe\u7f6e\u4e3a 64-512MB\uff0c\u8ddf\u636e\u670d\u52a1\u5668\u5927\u5c0f\u800c\u5f02\u3002<\/p>\n

innodb_log_buffer_size\u00a0<\/strong>Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory – it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.<\/p>\n

innodb_log_buffer_size\u00a0<\/strong>\u9ed8\u8ba4\u7684\u8bbe\u7f6e\u5728\u4e2d\u7b49\u5f3a\u5ea6\u5199\u5165\u8d1f\u8f7d\u4ee5\u53ca\u8f83\u77ed\u4e8b\u52a1\u7684\u60c5\u51b5\u4e0b\uff0c\u670d\u52a1\u5668\u6027\u80fd\u8fd8\u53ef\u4ee5\u3002\u5982\u679c\u5b58\u5728\u66f4\u65b0\u64cd\u4f5c\u5cf0\u503c\u6216\u8005\u8d1f\u8f7d\u8f83\u5927\uff0c\u5c31\u5e94\u8be5\u8003\u8651\u52a0\u5927\u5b83\u7684\u503c\u4e86\u3002\u5982\u679c\u5b83\u7684\u503c\u8bbe\u7f6e\u592a\u9ad8\u4e86\uff0c\u53ef\u80fd\u4f1a\u6d6a\u8d39\u5185\u5b58 — \u5b83\u6bcf\u79d2\u90fd\u4f1a\u5237\u65b0\u4e00\u6b21\uff0c\u56e0\u6b64\u65e0\u9700\u8bbe\u7f6e\u8d85\u8fc71\u79d2\u6240\u9700\u7684\u5185\u5b58\u7a7a\u95f4\u3002\u901a\u5e38 8-16MB \u5c31\u8db3\u591f\u4e86\u3002\u8d8a\u5c0f\u7684\u7cfb\u7edf\u5b83\u7684\u503c\u8d8a\u5c0f\u3002<\/span><\/p>\n

innodb_flush_logs_at_trx_commit<\/strong>\u00a0Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.<\/p>\n

innodb_flush_logs_at_trx_commit<\/strong>\u00a0\u662f\u5426\u4e3aInnodb\u6bd4MyISAM\u61621000\u500d\u800c\u5934\u5927\uff1f\u770b\u6765\u4e5f\u8bb8\u4f60\u5fd8\u4e86\u4fee\u6539\u8fd9\u4e2a\u53c2\u6570\u4e86\u3002\u9ed8\u8ba4\u503c\u662f 1\uff0c\u8fd9\u610f\u5473\u7740\u6bcf\u6b21\u63d0\u4ea4\u7684\u66f4\u65b0\u4e8b\u52a1\uff08\u6216\u8005\u6bcf\u4e2a\u4e8b\u52a1\u4e4b\u5916\u7684\u8bed\u53e5\uff09\u90fd\u4f1a\u5237\u65b0\u5230\u78c1\u76d8\u4e2d\uff0c\u800c\u8fd9\u76f8\u5f53\u8017\u8d39\u8d44\u6e90\uff0c\u5c24\u5176\u662f\u6ca1\u6709\u7535\u6c60\u5907\u7528\u7f13\u5b58\u65f6\u3002\u5f88\u591a\u5e94\u7528\u7a0b\u5e8f\uff0c\u5c24\u5176\u662f\u4eceMyISAM\u8f6c\u53d8\u8fc7\u6765\u7684\u90a3\u4e9b\uff0c\u628a\u5b83\u7684\u503c\u8bbe\u7f6e\u4e3a 2 \u5c31\u53ef\u4ee5\u4e86\uff0c\u4e5f\u5c31\u662f\u4e0d\u628a\u65e5\u5fd7\u5237\u65b0\u5230\u78c1\u76d8\u4e0a\uff0c\u800c\u53ea\u5237\u65b0\u5230\u64cd\u4f5c\u7cfb\u7edf\u7684\u7f13\u5b58\u4e0a\u3002\u65e5\u5fd7\u4ecd\u7136\u4f1a\u6bcf\u79d2\u5237\u65b0\u5230\u78c1\u76d8\u4e2d\u53bb\uff0c\u56e0\u6b64\u901a\u5e38\u4e0d\u4f1a\u4e22\u5931\u6bcf\u79d21-2\u6b21\u66f4\u65b0\u7684\u6d88\u8017\u3002\u5982\u679c\u8bbe\u7f6e\u4e3a 0 \u5c31\u5feb\u5f88\u591a\u4e86\uff0c\u4e0d\u8fc7\u4e5f\u76f8\u5bf9\u4e0d\u5b89\u5168\u4e86 — MySQL\u670d\u52a1\u5668\u5d29\u6e83\u65f6\u5c31\u4f1a\u4e22\u5931\u4e00\u4e9b\u4e8b\u52a1\u3002\u8bbe\u7f6e\u4e3a 2 \u6307\u6325\u4e22\u5931\u5237\u65b0\u5230\u64cd\u4f5c\u7cfb\u7edf\u7f13\u5b58\u7684\u90a3\u90e8\u5206\u4e8b\u52a1\u3002<\/span><\/p>\n

table_cache<\/strong>\u00a0– Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I\u2019ve seen values over 100.000 used.<\/p>\n

table_cache<\/strong>\u00a0— \u6253\u5f00\u4e00\u4e2a\u8868\u7684\u5f00\u9500\u53ef\u80fd\u5f88\u5927\u3002\u4f8b\u5982MyISAM\u628aMYI\u6587\u4ef6\u5934\u6807\u5fd7\u8be5\u8868\u6b63\u5728\u4f7f\u7528\u4e2d\u3002\u4f60\u80af\u5b9a\u4e0d\u5e0c\u671b\u8fd9\u79cd\u64cd\u4f5c\u592a\u9891\u7e41\uff0c\u6240\u4ee5\u901a\u5e38\u8981\u52a0\u5927\u7f13\u5b58\u6570\u91cf\uff0c\u4f7f\u5f97\u8db3\u4ee5\u6700\u5927\u9650\u5ea6\u5730\u7f13\u5b58\u6253\u5f00\u7684\u8868\u3002\u5b83\u9700\u8981\u7528\u5230\u64cd\u4f5c\u7cfb\u7edf\u7684\u8d44\u6e90\u4ee5\u53ca\u5185\u5b58\uff0c\u5bf9\u5f53\u524d\u7684\u786c\u4ef6\u914d\u7f6e\u6765\u8bf4\u5f53\u7136\u4e0d\u662f\u4ec0\u4e48\u95ee\u9898\u4e86\u3002\u5982\u679c\u4f60\u6709200\u591a\u4e2a\u8868\u7684\u8bdd\uff0c\u90a3\u4e48\u8bbe\u7f6e\u4e3a 1024 \u4e5f\u8bb8\u6bd4\u8f83\u5408\u9002\uff08\u6bcf\u4e2a\u7ebf\u7a0b\u90fd\u9700\u8981\u6253\u5f00\u8868\uff09\uff0c\u5982\u679c\u8fde\u63a5\u6570\u6bd4\u8f83\u5927\u90a3\u4e48\u5c31\u52a0\u5927\u5b83\u7684\u503c\u3002\u6211\u66fe\u7ecf\u89c1\u8fc7\u8bbe\u7f6e\u4e3a 100,000 \u7684\u60c5\u51b5\u3002<\/p>\n

thread_cache<\/strong>\u00a0Thread creation\/destructions can be expensive, which happen at each connect\/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
\nThreads_Created<\/strong>\u00a0variable I boost it higher. The goal is not to have threads created in normal operation.<\/p>\n

thread_cache<\/strong>\u00a0— \u7ebf\u7a0b\u7684\u521b\u5efa\u548c\u9500\u6bc1\u7684\u5f00\u9500\u53ef\u80fd\u5f88\u5927\uff0c\u56e0\u4e3a\u6bcf\u4e2a\u7ebf\u7a0b\u7684\u8fde\u63a5\/\u65ad\u5f00\u90fd\u9700\u8981\u3002\u6211\u901a\u5e38\u81f3\u5c11\u8bbe\u7f6e\u4e3a 16\u3002\u5982\u679c\u5e94\u7528\u7a0b\u5e8f\u4e2d\u6709\u5927\u91cf\u7684\u8df3\u8dc3\u5e76\u53d1\u8fde\u63a5\u5e76\u4e14Threads_Created<\/strong>\u00a0\u7684\u503c\u4e5f\u6bd4\u8f83\u5927\uff0c\u90a3\u4e48\u6211\u5c31\u4f1a\u52a0\u5927\u5b83\u7684\u503c\u3002\u5b83\u7684\u76ee\u7684\u662f\u5728\u901a\u5e38\u7684\u64cd\u4f5c\u4e2d\u65e0\u9700\u521b\u5efa\u65b0\u7ebf\u7a0b\u3002<\/p>\n

query_cache<\/strong>\u00a0If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.<\/p>\n

query_cache<\/strong>\u00a0— \u5982\u679c\u4f60\u7684\u5e94\u7528\u7a0b\u5e8f\u6709\u5927\u91cf\u8bfb\uff0c\u800c\u4e14\u6ca1\u6709\u5e94\u7528\u7a0b\u5e8f\u7ea7\u522b\u7684\u7f13\u5b58\uff0c\u90a3\u4e48\u8fd9\u5f88\u6709\u7528\u3002\u4e0d\u8981\u628a\u5b83\u8bbe\u7f6e\u592a\u5927\u4e86\uff0c\u56e0\u4e3a\u60f3\u8981\u7ef4\u62a4\u5b83\u4e5f\u9700\u8981\u4e0d\u5c11\u5f00\u9500\uff0c\u8fd9\u4f1a\u5bfc\u81f4MySQL\u53d8\u6162\u3002\u901a\u5e38\u8bbe\u7f6e\u4e3a 32-512Mb\u3002\u8bbe\u7f6e\u5b8c\u4e4b\u540e\u6700\u597d\u662f\u8ddf\u8e2a\u4e00\u6bb5\u65f6\u95f4\uff0c\u67e5\u770b\u662f\u5426\u8fd0\u884c\u826f\u597d\u3002\u5728\u4e00\u5b9a\u7684\u8d1f\u8f7d\u538b\u529b\u4e0b\uff0c\u5982\u679c\u7f13\u5b58\u547d\u4e2d\u7387\u592a\u4f4e\u4e86\uff0c\u5c31\u542f\u7528\u5b83\u3002<\/p>\n

Note:<\/strong>\u00a0as you can see all of these are global variables. These variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increasesort_buffer_size<\/strong>\u00a0even if you have 64GB of memory to waste. Furthermore doing so may decrease performance.
\nI normally leave per session variable tuning to second step after I can analyze workload.<\/p>\n

\u6ce8\u610f\uff1a<\/strong>\u5c31\u50cf\u4f60\u770b\u5230\u7684\u4e0a\u9762\u8fd9\u4e9b\u5168\u5c40\u8868\u91cf\uff0c\u5b83\u4eec\u90fd\u662f\u4f9d\u636e\u786c\u4ef6\u914d\u7f6e\u4ee5\u53ca\u4e0d\u540c\u7684\u5b58\u50a8\u5f15\u64ce\u800c\u4e0d\u540c\uff0c\u4f46\u662f\u4f1a\u8bdd\u53d8\u91cf\u901a\u5e38\u662f\u6839\u636e\u4e0d\u540c\u7684\u8d1f\u8f7d\u6765\u8bbe\u5b9a\u7684\u3002\u5982\u679c\u4f60\u53ea\u6709\u4e00\u4e9b\u7b80\u5355\u7684\u67e5\u8be2\uff0c\u90a3\u4e48\u5c31\u65e0\u9700\u589e\u52a0sort_buffer_size<\/strong>\u00a0\u7684\u503c\u4e86\uff0c\u5c3d\u7ba1\u4f60\u6709 64GB \u7684\u5185\u5b58\u3002\u641e\u4e0d\u597d\u4e5f\u8bb8\u4f1a\u964d\u4f4e\u6027\u80fd\u3002
\n\u6211\u901a\u5e38\u5728\u5206\u6790\u7cfb\u7edf\u8d1f\u8f7d\u540e\u624d\u6765\u8bbe\u7f6e\u4f1a\u8bdd\u53d8\u91cf\u3002<\/p>\n

P.S Note MySQL distribution contains bunch of sample my.cnf files which may be great templates to use. Typically they would already be much better than defaults if you chose correct one.<\/p>\n

P.S\uff0cMySQL\u7684\u53d1\u884c\u7248\u5df2\u7ecf\u5305\u542b\u4e86\u5404\u79cd my.cnf \u8303\u4f8b\u6587\u4ef6\u4e86\uff0c\u53ef\u4ee5\u4f5c\u4e3a\u914d\u7f6e\u6a21\u677f\u4f7f\u7528\u3002\u901a\u5e38\u8fd9\u6bd4\u4f60\u4f7f\u7528\u9ed8\u8ba4\u8bbe\u7f6e\u597d\u7684\u591a\u4e86\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"

\u770b\u4e86\u539f\u6587\u89c9\u5f97\u633a\u5b9e\u7528\u7684\uff0c\u786e\u5b9e\u5bf9\u4e8eDB\u65b0\u519c\u6765\u8bb2\uff0c\u7528\u7cfb\u7edf\u9ed8\u8ba4\u914d\u7f6e\u6216\u8005MySQL\u81ea\u5e26\u7684\u51e0\u4e2a\u914d\u7f6e\u6a21\u677f\u6bd4\u8f83\u591a\uff0c\u9876\u591a\u4f1a\u53bb\u8c03\u51e0\u4e2a\u53c2\u6570\u3002\u8fd9\u7f16\u535a\u5ba2\u5217\u51fa\u7684\u70b9\u5982\u4f5c\u8005\u6240\u8ff0\uff0c\u201cMySQL\u53ef\u8c03\u7684\u53c2\u6570\u786e\u5b9e\u4e0d\u5c11\uff0c\u4e0d\u8fc7\u771f\u6b63\u5bf9\u7cfb\u7edf\u6027\u80fd\u6709\u975e\u5e38\u663e\u8457\u7684\u5f71\u54cd\u5c31\u90a3\u4e48\u51e0\u4e2a\u201d\u3002<\/p>\n

key_buffer_size<\/b>
innodb_buffer_pool_size<\/b>
innodb_additional_mem_pool_size<\/b>
innodb_log_file_size<\/b>
innodb_log_buffer_size<\/b>
innodb_flush_log_at_trx_commit<\/b>
tabl<\/b><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7],"tags":[],"_links":{"self":[{"href":"https:\/\/blog.vimge.com\/wp-json\/wp\/v2\/posts\/1148"}],"collection":[{"href":"https:\/\/blog.vimge.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.vimge.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.vimge.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.vimge.com\/wp-json\/wp\/v2\/comments?post=1148"}],"version-history":[{"count":7,"href":"https:\/\/blog.vimge.com\/wp-json\/wp\/v2\/posts\/1148\/revisions"}],"predecessor-version":[{"id":1338,"href":"https:\/\/blog.vimge.com\/wp-json\/wp\/v2\/posts\/1148\/revisions\/1338"}],"wp:attachment":[{"href":"https:\/\/blog.vimge.com\/wp-json\/wp\/v2\/media?parent=1148"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.vimge.com\/wp-json\/wp\/v2\/categories?post=1148"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.vimge.com\/wp-json\/wp\/v2\/tags?post=1148"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}