作者：Fenng 发布在 dbanotes.net.

我算得上是 Movable Type 的忠实用户。这两年来，WordPress 快成为 独立 Blog 的标准配置了，MT 可以说每况愈下。自从升级到 MT4 之后，明显感觉 Blog 性能糟糕了很多，每一次发文章都要与 HTTP 500 错误战斗半天才能成功，读者留言也非常费劲，有段时间非常灰心丧气，都不想继续写下去了。如果说 MT 3 之前是与 SPAM 战斗，到了 MT 4 就完全是折腾性能问题了。虽然 MT 4 之后的每次小版本发布都宣称性能上有很大改进，实际表现总是欠佳。

一些常规的 MT 优化技巧 其实作用都不大，我还把 MT 用的数据库索引分析了一遍，删掉了好几个价值不大的索引，虽然不是无用功，但也解决不了问题，因为瓶颈根本不在 DB 这里。大多数 Movable Type 用户的应用场景是在 Dreamhost 上，在这个环境里要想找到应用的瓶颈还真的是个技术活。缺乏 Profiling 工具不说，如果调用某个命令消耗了资源稍微过份一点，立刻被系统 Kill 掉。

其实 MT 的设计思想还是很不错的，大部分页面都 Build 成静态页面，减少对数据库的压力。一般来说，这比 WordPress 频繁调用数据库的方式(当然 WP 是可以起用 Cache 的)，应该在性能上有更好的表现才是。问题是 Dreamhost 对每个用户提供的硬件资源其实是很差的，尤其是磁盘 I/O 。我的所有页面加起来也不过几千个，如果只是访问静态页面或是数据库其实问题都不大的，麻烦出现在构建页面或者是遇到留言行为的时候，MT 会调用若干个磁盘上的模版文件，加上 Perl 本身的系统开销，消耗的资源就到了 Dreamhost 的极限，然后进程就被无情的 Kill 掉了。估计是用户群抱怨太多，从 MTOS 4.1.1 版本开始总算有了一个性能框架 专门用以分析性能问题(并且号召用户提供性能数据以便协助解决性能问题)。启用了一段时间后，收集到的 Log 响应时间典型是这样的：

MT::Template::build[Comment Response]=0.60070
MT::App::Comments::run=28.24320
Total=28.85665

或许在 I/O 不错的系统上，表现会好吧。Dreamhost 上文件都是放置在 NFS 上的，而且众多用户共用一个系统，只能将就一下。

今天看到 4.15 的改进中，已经可以 Cache Template Module 了。甘愿作小白鼠，升级。盼望这次能有效解决特定应用场景的性能问题。不过开启了性能 Log 看了半天，效果并非很显著，

后记：

经验证的有效办法之一

通过Cache提升MT基于Tag搜索的速度，只在第一次构建需要的 Tag 时候会占用平时一样的系统资源。再次搜索时，开销大大降低。"抱怨的同时要有解决办法"

--EOF--

相关文章|Related Articles

• MT安装简记
• 面向 Google 的 Movable Type 优化
• Related Entries Plugin too!
• MT 'nofollow' plugin ,联手抵制SPAM留言

评论数量(7)|Add Comments

本文网址：http://www.dbanotes.net/web/movable_type_performance.html
最近作者还说了什么? Follow Twitter / Fenng

作者：Fenng 发布在 dbanotes.net.

继续我的学习笔记之旅。Flickr 的 DBA Dathan Pattishall 在前几天的 MySQL 大会上分享了 Scaling Heavy Concurrent Writes In Real Time (Record every Referral for Flickr Realtime) ，其中介绍了 Flickr Stats 的设计经验。国内好多 Web 站点其实也在设计类似的功能，只是不知道细节罢了。

数据结构原型

 字段               数据类型         
Path_query         Varchar(255)        PK         
Domain             Varchar(50)                         
Owner              Bigint                              
When               Date                                
Object-ID          Bigint                              
Object-Type        Tinyint                             
Counts and stuff   Various ints        May be some keys

主键是字符串，开销太大。其他的索引如果做主键，也比较大。当表大小超过内存的时候，插入速度很慢，I/O 能力也上不来。

优化数据结构

数据预处理，通过 CONV(SUBSTR(MD5(Url),0,16),16,10) 把 Path_query 修改为 64 位的 ID (8字节), 主键为 ID+Owner+object+objecg-type，这个统计信息很容易抽象到一个数据对象，这个索引的设计也在于此。

另外补充一点，利用 PHP 的 ip2long() 和 long2ip() 函数对 IP 地址作预处理，耗费的存储空间只为原来地 25%，这是个很有趣的技巧。

数据 Sharding

对于海量的数据，以一个礼拜为间隔，水平分割。按照不同的数据力度每周一个表，每年一个全局表，再加上一个汇总表。数据量越大，InnoDB 存储引擎针对字符串的索引浪费的空间就越大。单个查询的 I/O 也自然大了起来。

所有应用对 DB 的响应要求 是 300 毫秒。但高并发写入的时候响应时间就糟糕起来。Flickr 的 Java 牛人实现了 Referral 队列，每 4000 条做批量处理。这样 IO 拥塞的就解决掉了。

总体的服务器规模过去 介绍过，对专业版用户的数据是永久保留的，而普通用户则只保留几周，为节省空间，采用 MyISAM 引擎，当用户转为专业版时，迁移数据。

补充一下，抓取 URL 是用的 curl 。最后，这篇 PPT 在线观看。

--EOF--

相关文章|Related Articles

• WordPress.com 的数据量
• Fotolog.com 的技术信息拾零
• 《大型 Web2.0 站点构建技术初探 》一文系抄袭
• Digg 网站架构

评论数量(2)|Add Comments

本文网址：http://www.dbanotes.net/arch/flickr_referral_design.html
最近作者还说了什么? Follow Twitter / Fenng

作者：Fenng 发布在 dbanotes.net.

公司团队活动，集体观影。笑翻，没想到李连杰、成龙联手，喜剧效果直逼星爷。

打斗固然精彩，冲着李、成两位，票钱也不冤枉。必需要说的是，台词超经典。举一个例:

天行者："我们是不是不能走出沙漠了?...我都快要冻死了" 
默僧(李连杰)慢慢转头(深沉地)："不要忘了呼吸"

鲁彦(成龙)喝的那个长生不老药...绿色液体...看到那里我禁不住脱口而出，"好像雪碧"

最后的玉帝，演出效果和《食神》里面那个梦遗大师颇有神似之处。问天行者要实现啥愿望(这不就是按照上帝的功能来整嘛)，"我只想要回家"，玉帝长出一口气说道:"那我就放心了"。

--EOF--

相关文章|Related Articles

• 三步杀人曲
• 十二宫(Zodiac) 的主题歌很好听
• 士兵突击
• 太阳照常升起

评论数量(7)|Add Comments

本文网址：http://www.dbanotes.net/mylife/the_forbidden_kingdom.html
最近作者还说了什么? Follow Twitter / Fenng

I read very nice post by Matt today and it has many good insights though I can’t say I agree on all points.
First there is a lot of people out where which put it as disk is everything. Remember Paul Tuckfield saying “You should ask how many disks they have instead of how many systems they have” on MySQL UC2008 Scalability Panel ? Indeed disks MAY be the most important part in your system performance or it may not be. Different people get to deal with different systems and so acquire different feeling about percentage of cases when disk would be the problem.

However it is not always the case. There are whole classes of systems where Disk performance is not that important - consider for example systems where most of the database fits in memory. These days we can get 64G of memory for pretty commodity prices and this allows you to get a lot of data in. Many Web 2.0 sites in particular design for having everything they need in memory, which is not that hard after you got VC funding as soon as you can shard your data properly so you would not have to replicate everything

There are also cases when storage size becomes the limit rather than number of IOs it can handle. Imagine for example data archive storage. On BoardReader for example we’re limited by space and how much data we can comfortably put in MySQL Instance without getting in too much trouble with backups etc - because Sphinx is doing all search heavy lifting we only need to insert new data and fetch few rows to show search results which does not require too much IO capacity or high in memory fit.

For large number of applications optimizing IO performance will be number one problem do not get me wrong, I just it is not always the case.

Another mistake is to measure IO capacity in spindles - So your hard drive can do 200 random reads per second and your SSD drive can do 10000 does it mean if you have array with 50 hard drives it would perform as good as SSD ? Not really. Leaving all potential serialization issues along you need enough concurrency to utilize multiple hard drives and with 50 drives you would need at least 50 outstanding requests all the time to fully utilize them. So for example these 50 drives will unlikely be helpful solving replication delay or speeding up this 3 hours reporting query or 5 hours ALTER TABLE
command.

Let me also comment on the memory fit diagram - understanding your working set is paramount but taking number from other application is one of the worst mistakes you can make. One application can become CPU bound even with 5% of total data set in memory while over may require full 100%. Performance gains graphs in relation to fit in memory will also be different.

Now let me come to filesystem layout issues This is where a lot of roads meet and you have to consider a lot of topics - security, manageability, performance. I tend to advice different things depending on what is the most important. Assuming you have fixed amount of disk at your disposal and if you have BBU most likely data files is where all IO will go. If you dedicate RAID1 volume for Operating system and another one for Transactional Logs you will often wast 4 hard drives from performance standpoint without a good reason.

Having OS and tmp away of the data is a good idea as Matt says - you do not want your runaway logs or extreme temporary space usage to stop the database, but they usually can keep the same RAID volume.
I tend to keep Innodb data files (and whole datadir) and log files on the same partition as well because this makes it very easy to use LVM for backups while assuming you have BBU on the raid volume impact on transactional log write speed is usually minimal. At the same time if reliability is desired it might be good idea to keep Binary Logs on the separate volume - you may need them to do point in time recovery if you want to recover to last committed transaction in case your data RAID volume was trashed.

Regarding SWAP - I tend to have it but not optimize for it, putting it on shared drive (or OS+Stuff drive if there is one) - you do not want your database box to swap actively - if it does your’re in trouble anyway so it is better to fix the things rather than optimize swap performance.

I also fully support Matt in his views of black boxes. If you want to be able to resolve your performance problems you need transparency, you need to have X-Ray vision, you need to understand what is happening and why. A lot of pieces - filesystem, disk scheduling, database buffer management can be rather complex but you need to have at least basic understanding of the processes to be able to reliably identify problems.
At the same time I would be very careful with your assumptions - in many instances I’ve seen different parts of the system working not as I would expect them to and not as it would make sense in my opinion but quite differently.

Entry posted by peter | 6 comments

Add to: | | | |