MySQL内存表、MyISAM表、MemCache实现Session效率比较
jopen
13年前
<div id="blog_text"> <p>早先问过BleakWind，他认为，给别人做的话MySQL内存表做会话比较 好一些，因为MySQL内存表做Session更容易维护（可以制作安装脚本）。这个周末，我进行了一些测试，测试MySQL MyISAM表做会话（对时间给不给索引）、内存表做会话、MemCache做会话的效率比较。</p> <p><strong>定义会话Session类：</strong></p> <div> <img border="0" alt="MySQL内存表、MyISAM表、MemCache实现Session效率比较" src="https://simg.open-open.com/show/c451eb3fc3964b10a1027a1629886082.png" width="700" height="1390" /> </div> <p><strong>定义会话处理器接口（Session_Handler_Interface)：</strong></p> <div> <img border="0" alt="MySQL内存表、MyISAM表、MemCache实现Session效率比较" src="https://simg.open-open.com/show/849f1bef2965a71f977460a576a91499.png" width="369" height="320" /> </div> <p><strong>实现MySQL内存表Session处理器：</strong></p> <div> <img style="width:760px;height:633px;" border="0" alt="MySQL内存表、MyISAM表、MemCache实现Session效率比较" src="https://simg.open-open.com/show/66cdd3a65afa2aa84d9c0b3a13ed9d0a.png" /> </div> <p><strong>实现MemCache会话处理器：</strong></p> <div> <img border="0" alt="MySQL内存表、MyISAM表、MemCache实现Session效率比较" src="https://simg.open-open.com/show/bc109775acf13976048f6c20859a1da6.png" width="641" height="666" /> </div> <p><strong>MySQL内存表测试程序：</strong></p> <div> <img border="0" alt="MySQL内存表、MyISAM表、MemCache实现Session效率比较" src="https://simg.open-open.com/show/3d80072d138bc997d19b61fdf53ba3d3.png" width="420" height="256" /> </div> <p>MemCache会话测试代码：</p> <div> <img border="0" alt="MySQL内存表、MyISAM表、MemCache实现Session效率比较" src="https://simg.open-open.com/show/30845dd1fa766ae31419ef9a30b15482.png" width="379" height="218" /> </div> <p><strong>MySQL会话表</strong>如下：</p> <p>CREATE TABLE `session` (<br /> `id` char(32) COLLATE utf8_unicode_ci NOT NULL,<br /> `name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,<br /> `ip` varchar(15) COLLATE utf8_unicode_ci NOT NULL,<br /> `time` int(10) unsigned NOT NULL,<br /> PRIMARY KEY (`id`)<br /> ) ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;</p> <p>测试结果：</p> <p><span style="color:#ff0000;">MemCache：</span></p> <p>======================================================================================</p> <p>Concurrency Level: 30<br /> Time taken for tests: 18.234375 seconds<br /> Complete requests: 10000<br /> Failed requests: 0<br /> Write errors: 0<br /> Total transferred: 2774781 bytes<br /> HTML transferred: 280000 bytes<br /> Requests per second: 548.41 [#/sec] (mean)<br /> Time per request: 54.703 [ms] (mean)<br /> Time per request: 1.823 [ms] (mean, across all concurrent requests)<br /> Transfer rate: 148.57 [Kbytes/sec] received</p> <p>Connection Times (ms)<br /> min mean[+/-sd] median max<br /> Connect: 0 0 1.9 0 31<br /> Processing: 0 53 12.1 46 328<br /> Waiting: 0 52 11.9 46 328<br /> Total: 0 53 12.1 46 328</p> <p>Percentage of the requests served within a certain time (ms)<br /> 50% 46<br /> 66% 62<br /> 75% 62<br /> 80% 62<br /> 90% 62<br /> 95% 62<br /> 98% 62<br /> 99% 78<br /> 100% 328 (longest request)</p> <p><span style="color:#ff0000;">MySQL内存表：</span></p> <p>=================================================================================</p> <p>Concurrency Level: 30<br /> Time taken for tests: 20.375000 seconds<br /> Complete requests: 10000<br /> Failed requests: 4694<br /> (Connect: 0, Length: 4694, Exceptions: 0)<br /> Write errors: 0<br /> Total transferred: 3118440 bytes<br /> HTML transferred: 623048 bytes<br /> Requests per second: 490.80 [#/sec] (mean)<br /> Time per request: 61.125 [ms] (mean)<br /> Time per request: 2.038 [ms] (mean, across all concurrent requests)<br /> Transfer rate: 149.45 [Kbytes/sec] received</p> <p>Connection Times (ms)<br /> min mean[+/-sd] median max<br /> Connect: 0 0 1.9 0 31<br /> Processing: 0 60 7.6 62 125<br /> Waiting: 0 59 7.6 62 125<br /> Total: 0 60 7.5 62 125</p> <p>Percentage of the requests served within a certain time (ms)<br /> 50% 62<br /> 66% 62<br /> 75% 62<br /> 80% 62<br /> 90% 62<br /> 95% 62<br /> 98% 78<br /> 99% 78<br /> 100% 125 (longest request)</p> <p>其他测试：</p> <p>将MySQL实现的数据库引擎改为 <span style="color:#ff0000;">MyISAM</span>（依然为 并发 30 测试 10000 次） 结果为：</p> <p>Requests per second: 440.17 [#/sec] (mean)<br /> Percentage of the requests served within a certain time (ms)<br /> 50% 62<br /> 66% 62<br /> 75% 78<br /> 80% 78<br /> 90% 78<br /> 95% 78<br /> 98% 78<br /> 99% 78<br /> 100% 640 (longest request)</p> <p><span style="color:#ff0000;">为MyISAM表的 time 列增加索引</span>（因为 会话表 读写次数几乎相等，因此应该效果不明显），结果为：</p> <p>Requests per second: 441.08 [#/sec] (mean)<br /> Percentage of the requests served within a certain time (ms)<br /> 50% 62<br /> 66% 62<br /> 75% 78<br /> 80% 78<br /> 90% 78<br /> 95% 78<br /> 98% 109<br /> 99% 109<br /> 100% 156 (longest request)</p> <p>=================================================================================</p> <p>结论：</p> <p><span style="color:#ff0000;">MemCache做会话效率最高，也最灵活</span>，但目前尝不支持查看谁在线等功能，附加的，只能自己增加一个数组记录在线用户、以及最后活跃时间并实现gc等。<br /> MySQL内存表做会话效率也相当的高，另外一个有点是，<span style="color:#ff0000;">MySQL内存表似乎更稳定，longest request （125ms）明显的短于 MemCache的（328ms）。不过缺点是，存储的字段数以及字段长度受限</span>。<br /> MySQL MyISAM表做会话在这里居然也达到了440的rps，真不简单。不过您要是等半个小时在测试一次，您就会明白MyISAM表的缺点了，页面几乎崩溃。<span style="color:#ff0000;">MyISAM表的缺点是，一旦其中有较多的碎片，这个数据库几乎都不可用了</span>，您注释掉 gc 的代码在这里貌似可以获得更好的效率表现(^_^、当然也可以定时的Optimizer，概率触发或者Cron定时启动也不错）<br /> <span style="color:#ff0000;">MyISAM表对time列增加索引对每秒完成的请求数没什么影响</span>，不过有一点需要注意的是，增加索引后，每次完成 request的时间更均匀了,longest request从640ms跌到了156ms。<span style="color:#ff0000;">为time列增加索引有助于让完成请求的时间变均匀。</span></p> <p>测试平台：</p> <p>Acer Aspire 4520G：<br /> CPU：AMD Athlon 64 * 2 TK-55<br /> RAM: IG<br /> OS: Windows XP sp2<br /> Web Server: Apache 2.26<br /> PHP: PHP5.25</p> </div>