duanaoshu1989 2015-06-17 13:32
浏览 44

用于高频写入的高速缓存/队列[关闭]

Assume a (PHP) web application with X00,000 requests per minute, and a SQL database with about the same number of entries.

Each request will trigger a few (<5) db reads, and one db write (update, mostly). I am mainly worried about performance issues being caused by the writes, and looking for solutions to increase possible requests per minute.

What solutions would you suggest, how would you go about implementing it, and why?

Things I thought about include a cache to write to, which is synced with the db once every some time-interval, or a queue like Gearman, or simply SQL's insert delayed.

However, since the same load is expected to be constant, insert delayed will probably not help much when server load is constantly at >90%, so I am looking for ways to significantly reduce the server load compared to single writes.

Thanks

  • 写回答

1条回答 默认 最新

  • duanjia6959 2015-06-17 13:53
    关注

    If you are updating the table you are also reading, then yes, these updates will slow down reads, because they wil invalidate the MySQL query-cache and possible table locking. One common example is table for product details, where you are updating visit counts.

    Simple approach is not updating the main table, but instead use separate table for the changing values (for example product_views) and alter your queries to use JOIN for fetching the values. Using this approach your main (and big) table will be mostly static and unaffected by query-cache invalidation and locking.

    More advanced approach would be to NOT use UPDATEs, but INSERTs. Instead of updating a row on every pageview, INSERT new value into temp table (e.g. product_views_temp) - so each view will be 1 row. Then schedule a cron job for example every 5 minutes to do a query like SELECT COUNT(*) FROM product_views_temp GROUP BY prod_id and based on the numbers update the main table (should be possible in 1 query). Main benefit is that these INSERTs are faster and you can have the counts in the main table if you need for whatever reason. Little drawback is that that there will be little delay before visitors will see the updated counts.

    EDIT: If you can afford to lose the counts data for those 5 minutes, you can make it super-fast by creating that temp table with MySQL MEMORY storage engine. They are very fast for INSERTs (but can be little slower for UPDATEs). When the server crashes / shutdowns, the table itself still exists, but its contents is lost.

    You can also use the MEMORY engine for session data (it doesn't matter if users need to log-in again after an unexpected server crash as it is something which will not happen often).

    评论

报告相同问题?

悬赏问题

  • ¥30 eclipse开启服务后,网页无法打开
  • ¥30 雷达辐射源信号参考模型
  • ¥15 html+css+js如何实现这样子的效果?
  • ¥15 STM32单片机自主设计
  • ¥15 如何在node.js中或者java中给wav格式的音频编码成sil格式呢
  • ¥15 不小心不正规的开发公司导致不给我们y码,
  • ¥15 我的代码无法在vc++中运行呀,错误很多
  • ¥50 求一个win系统下运行的可自动抓取arm64架构deb安装包和其依赖包的软件。
  • ¥60 fail to initialize keyboard hotkeys through kernel.0000000000
  • ¥30 ppOCRLabel导出识别结果失败