［翻译］INSERT ON DUPLICATE KEY UPDATE and summary counters

INSERT ON DUPLICATE KEY UPDATE and summary counters

INSERT ... ON DUPLICATE KEY UPDATE 是非常强大但是往往被遗忘的mysql的功能。它在mysql4.1的时候引入，但我仍然不断见到人们不知道它。

我自己是很喜欢这个功能的，因为它的设计是真正mysql风格的----对于常见任务的提供高效解决方案，同时保持优雅和易用。

这个功能好在哪儿呢？它可以很好的处理任何类型的计数统计。对于流量的统计，它可以记下通过特定的端口或者ip地址的流量和包数；对于web应用，可以记下每个页面或者ip的访问次数，特定关键字被搜索的次数等等。

这个功能也让增量单一通过日志文件的处理（incremental single pass log file processing ）和创建汇总表变得简单。

看下面这个例子：

SQL:

CREATE TABLE ipstat(ip int UNSIGNED NOT NULL PRIMARY KEY,

hits int UNSIGNED NOT NULL,

last_hit timestamp);

INSERT INTO ipstat VALUES(inet_aton('192.168.0.1'),1,now())

ON duplicate KEY UPDATE hits=hits+1;

这个例子其实展示了mysql一个更简洁的特性--inet_aton 和inet_ntoa 函数，他们可以实现ip地址（字符串）和数字之间的互相转换。这样在保存ip地址的时候，可以用4个字节而不是15个字节，可以大大节省空间。（如果存贮ip的数据量很大，推荐用这种方法，如果量不大，几十几百条，还是直接存字符串看着直观）

这个例子第三个特点是利用了数据类型TIMESTAMP。默认情况下，表中的第一个TIMESTAMP列会自动更新为insert和update时的时间戳。（TIMESTAMP值返回后显示为'YYYY-MM-DD HH:MM:SS'格式的字符串，显示宽度固定为19个字符。如果想要获得数字值，应在TIMESTAMP 列添加+0）。在insert子句中，我们之所以没有使用默认值，而是用了now(),是因为如果省略，那么在insert的时候要指明插入的字段名。

那么这个例子是如何运行的呢？和你期望的一样。如果这个ip地址不存在，会增加一条记录，并把hits置为1；如果存在（注意ip是主键），hits会加一，时间戳更新为现在的时间。

使用这个特性来替代INSERT + UPDATE，带来优化依赖于新增行数和数据集的大小。一般情况下会提速30%。性能的提高并不是它带来的唯一好处----更重要的是应用的代码会更简单----减少出错的几率，更加易读。

［翻译］［注解］Innodb Performance Optimization Basics

原文链接地址如下：http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
这篇文章写于2007年11月

翻译参考了这篇译稿：http://yahoon.blog.51cto.com/13184/76592

推荐详细阅读原作者的这篇演讲稿

Innodb性能优化基础

面试别人的时候我喜欢问一个基础的问题：如果你有一个16G内存，专用于mysql大型innodb的数据库服务器，对于典型的web负载，你应该怎样调整mysql的设置？有趣的是其中大多数并不能提出任何有益的建议。所以我决定公布答案，并且我很乐意在硬件，操作系统和应用方面谈谈基础的一些优化。

这篇文章的标题是‘Inodb性能优化基础’，所以这里面的是一些普遍的准则，适用于很多的应用场景，当然最佳的设置要依据具体的应用而定。

硬件

如果你的Innodb数据库很大,那么内存是最重要的。现在16-32G的内存性价比就不错。

From CPU standpoint 2*Dual Core CPUs seems to do very well, while with even just two Quad Core CPUs scalability issues can be observed on many workloads.
CPU方面，两个双核的CPU，似乎就不错了，而即使只有两个四核心CPU的可扩展性问题都可以观察到很多的工作量，但是这跟应用也有很大的关系。（这里翻译的很别扭，大家看原文）

第三是IO系统－－DAS和RAID是很好的选择.一般来说6-8块硬盘就够了,有时可能需要更多。同时注意新的2.5"的SAS硬盘,小却速度快。RAID10对于数据存储和主要是读的场合下十分合适。需要冗余性的话RAID5也不错，但注意对于RAID5的随机写操作。

操作系统

首先－－运行64位的操作系统。现在不少有大内存的服务器，上面还跑着32位的操作系统。建议不要这么做。

如果系统是linux，对数据库的目录使用LVM可以获得更高效的备份。

ext3文件系统大部分情况下都不会出问题，如果碰到问题的话，试试XFS。

如果你使用innodb_file_per_table而且表很多的话可以使用noatime和nodiratime选项，但是这样做效果不是很大。

同时注意给系统留出足够的内存，防止mysql和系统发生内存竞争导致被交换出内存。

MYSQL 的Innodb 设置
（关于更多更详细的参数说明，请参考这里（中文文档））

最重要的地方有:

innodb_buffer_pool_size 设为内存的70%-80%都是安全的。我在一个16G的服务器上把它设成12G。
UPDATE： 如果你想了解更多的细节，请查看tuning innodb buffer pool

innodb_log_file_size 这取决于你需要的出错恢复速度。256M是合理的恢复时间和良好性能之间不错的一个平衡值。

innodb_log_buffer_size=4M 大多数情况4M就够了。如果你有大量的事务处理，这个数值可以增加一点儿。

innodb_flush_log_at_trx_commit=2 如果你不是很关心ACID，可以容许在系统完全崩溃的情况下丢失最后一两秒的事务，那么可以设置这个值为2。它可以极大的提高短的写事务的效率。

innodb_thread_concurrency=8 即使目前的InnoDB可扩展性修复后，对并发的支持也是有限的。这个值取决于你的程序，可能高或者低一些。8是可以接受的默认值。

innodb_flush_method=O_DIRECT 避免双缓冲(double buffering)和降低swap的压力，大多数情况下可以提高性能。但是注意如果你RAID cache不够的话，写IO的操作会有麻烦。

innodb_file_per_table 如果你的表不多可以使用这个选项。这样你就不会有不受控的innodb主表空间的增长，这个主表空间是不能重新定义的。这个选项在4.1版中引入，现在可以放心使用。

查看你的程序是否可以运行在READ-COMMITED 隔离模式下，如果可以，就可以设为默认的transaction-isolation=READ-COMMITTED。这个选项有一些性能的优势，特别是在5.0，5.1版本的行级复制方面。

还有很多的参数选项需要调整，今天我们就只关注关于和Innodb相关的。其他的可以参考 tuning other options 和 MySQL Presentations.

应用程序的优化

如果原来是MyISAM，现在你可能需要对应用做一些修改。首先确保你在进行数据库更新的时候使用事务，这对数据一致性和性能都有好处。

其次如果你的应用有写操作的话要注意处理死锁问题。

第三你要重新检视你的表结构，尽可能利用Innodb的优势--簇集主键索引(clustering by primary key)，在所有的索引里面有主键（所以要保持主键简短）。使用主键来快速查询(试着在joins时使用)，large unpacked indexes (try to be easy on indexes)。(这一句不懂)

With these basic innodb performance tunings you will be better of when majority of Innodb users which take MySQL with defaults run it on hardware without battery backed up cache with no OS changes and have no changes done to application which was written keeping MyISAM tables in mind.

北京欢迎你

觉得《北京欢迎你》比《油和米》好听的多， 也更符合我的审美观。这界奥运会，大家都记得《油和米》这个名字了，也都会唱《北京欢迎你》这首歌了。

[翻译][注解]InnoDB memory usage

翻译纯属个人学习所用，原文版权属于原作者Vadim。原文链接

－－－－－－－－－－－－－－－－－－－－－－－－－－－

There are many questions how InnoDB allocates memory. I’ll try to give some explanation about the memory allocation at startup.

对于IonoDB的内存分配，有人存在不少疑问。我下面会试着解释一下innodb内存分配的入门知识。

Some important constants:

一些重要的常量：

NBLOCKS=count of block in innodb_buffer_pool = innodb_buffer_pool_size / 16384

NBLOCKS ＝ innodb_buffer_pool的分块数 ＝ innodb_buffer_pool_size / 16384

OS_THREADS= if (innodb_buffer_pool_size >= 1000Mb) = 50000
else if (innodb_buffer_pool_size >= 8Mb) = 10000
else = 1000 (it’s true for *nixes, for Windows there is a bit another calculation for OS_THREADS)

So InnoDB uses:（详细的参数意义可以查看MySQL手册）

• innodb_buffer_pool_size(原文为innodb_buffer_pool，疑为笔误) 指定InnoDB索引和数据的缓存池大小，如果是数据库专用服务器，推荐设置为物理内存的70%-80%
• innodb_additional_mem_pool_size 指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小
• innodb_log_buffer_size 推荐设置为4M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交，如果有大的事务处理，可以再稍微增加一点儿，一般不超过8M
• adaptive index hash, size= innodb_buffer_pool_size / 64 (原文为innodb_buffer_pool，疑为笔误) 所谓adaptive index hash（自适应索引哈希）是指MySQL会分析应用对索引的访问模式，有选择性的自动建立内存中的哈希索引，提高索引访问的效率。但是MySQL在实现这一特性时并没有充分考虑到并发性能问题，导致MySQL在多核机器上面性能下降。涉及到复杂的数据结构和算法，我也不懂:(有时间再分析一下
• system dictionary hash, size = 6 * innodb_buffer_pool_size / 512 系统字典哈希，不知道做什么用的
  
• memory for sync_array, which is used for syncronization primitives, size = OS_THREADS * 152 太专业，不懂是同步什么信息的
  
• memory for os_events, which are also used for syncronization primitives, OS_THREADS * 216 太专业，不懂是同步什么信息的
• and memory for locking system, size=5 * 4 * NBLOCKS 系统出现锁时需要的内存
  

So the final formula for innodb:

所以，最后innodb使用内存的计算公式是：

innodb_buffer_pool_size + innodb_log_buffer_size + innodb_additional_mem_pool_size + 812 / 16384 * innodb_buffer_pool_size + OS_THREADS * 368

For simplicity we can use: 简单一点儿我们可以这样算：

812 / 16384 * innodb_buffer_pool_size ~~ innodb_buffer_pool_size / 20
and OS_THREADS*368 = 17.5MB if innodb_buffer_pool > 1000MB
= 3.5MB if > 8MB

For example if you have innodb_buffer_pool_size=1500M, innodb_additional_mem_pool_size = 20M, innodb_log_buffer_size = 8M, InnoDB will allocate = 1500M + 20M + 8M + 1500/20M + 17.5M = 1620.5M.

以我们的数据库为例,

innodb_buffer_pool_size = 1324M,(这个值设置的偏低，服务器内存为4G)

innodb_additional_mem_pool_size = 16M,innodb_log_buffer_size = 8M,

那么InnoDB使用的内存就是 1324M ＋ 16M ＋ 8M ＋ 1324/20M + 17.5M = 1431.7M

Take the additional memory into account when you are planning memory usage for your server.

当你规划服务器内存使用时，记得把额外的于数据库无关的内存占用算上。