如何在InnoDB引擎中使用insert延迟并对insert语句使用更少的连接？

Question

我正在开发一个应用程序，它涉及大量的数据库写入、大约70%的插入和30%的读取。这一比率还将包括更新，我认为是一读一写。通过insert语句，多个客户端通过insert语句在数据库中插入数据如下：

$mysqli->prepare("INSERT INTO `track` (user, uniq_name, ad_name, ad_delay_time ) values (?, ?, ?, ?)");

问题是我应该使用insert_delay还是使用mysqli_multi_query机制，因为insert语句使用服务器上的~100% cpu。我在我的数据库上使用InnoDB引擎，所以插入延迟是不可能的。服务器上的插入值为36k/hr，读取率为99.89%，我还使用select语句在单次查询中检索数据七次，此查询在服务器上执行需要150秒。我可以使用什么样的技术或机制来完成这项任务？我的服务器内存是2GB，我应该扩展内存吗？看一下这个问题，任何建议都会感谢我的。

表的结构：

+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment |
| user            | varchar(100) | NO   |     | NULL              |                |
| uniq_name       | varchar(200) | NO   |     | NULL              |                |
| ad_name         | varchar(200) | NO   |     | NULL              |                |
| ad_delay_time   | int(11)      | NO   |     | NULL              |                |
| track_time      | timestamp    | NO   | MUL | CURRENT_TIMESTAMP |                |
+-----------------+--------------+------+-----+-------------------+----------------+

我的数据库当前状态，它显示了41k插入(写)，这对于我的数据库来说非常慢。

Answer 1

由于您有更多的写，然后读，我想推荐以下几点

体面的InnoDB调优将是关键的

缓冲池(由诺姆b调整大小)

自InnoDB不支持延迟插入以来，使用大型InnoDB缓冲池最接近于插入延迟。所有DML (插入、更新和删除)都将缓存在InnoDB缓冲池中。写入的事务信息将立即写入重做日志(ib_logfile0、ib_logfile1)。发布在缓冲池中的写操作通过ibdata1 (用于辅助索引的InsertBuffer，双写缓冲区)定期从内存刷新到磁盘。缓冲池越大，可以缓存的插入量就越大。在具有8GB或更多内存的系统中，使用75%-80%的RAM作为innodb_buffer_pool_size。在一个RAM很少的系统中，25% (以适应操作系统)。

注意:您可以将诺姆b设置为0以加快写入速度，但这可能会带来数据完整性的风险。您还可以通过将诺姆b_同花顺_方法设置为O_DIRECT来加快速度，以防止将InnoDB缓存到操作系统。

重做日志(由诺姆b调整大小)

默认情况下，重做日志名为ib_logfile0和ib_logfile1，每个日志为5MB。大小应该是innodb_buffer_pool_size的25%。如果重做日志已经存在，请在my.cnf中添加新设置，关闭mysql，删除它们，然后重新启动mysql。。

日志缓冲区(由诺姆b调整大小)

日志缓冲区保存RAM中的更改，然后将它们刷新到重做日志中。缺省值为800万。日志缓冲区越大，磁盘I/O越少，请小心处理非常大的事务，因为这可能会使提交速度减慢毫秒。

访问多CPU

MySQL 5.5和MySQL 5.1 InnoDB插件具有使InnoDB存储引擎访问多个CPU的设置。下面是您需要设置的选项：

• 诺姆b_线程_并发性设置InnoDB可以打开的并发线程数的上限。通常建议将其设置为(2×10×CPU)+磁盘数。去年，我从Percona会议上第一手了解到，您应该将其设置为0，以便提醒InnoDB存储引擎找到它正在运行的环境中的最佳线程数。
• 诺姆b_并发性_门票设置可以绕过并发检查而不受惩罚的线程数。在达到这个限制之后，线程并发检查再次成为常态。
• 诺姆b_提交_并发性设置可以提交的并发事务数。由于缺省值为0，所以不设置此设置可以同时提交任意数量的事务。
• 诺姆b_线程_睡眠_延迟设置InnoDB线程在重新进入InnoDB队列之前可以休眠的毫秒数。默认为10000 (10秒)。
• 诺姆b_朗读_io_线程 (将其设置为3000)和诺姆b_写_io_线程 (将其设置为7000) (自MySQL 5.1.38以来)为读写分配指定数量的线程。默认值为4，最大值为64。把这些调到64。另外，将诺姆b_io_容量设置为10000。

升级到MySQL 5.5

如果您有MySQL 5.0，请升级到MySQL 5.5。如果您有MySQL 5.1.37或更高版本，请升级到MySQL 5.5。如果您有MySQL 5.1.38或更高版本，并且希望保留在MySQL 5.1中，请安装InnoDB插件。这样，您就可以利用InnoDB的所有CPU。

Answer 2

INT(2)仍然使用4个字节--也许您的意思是没有签名的TINYINT？

在setno中有多少不同的值？如果它很小，则永远不会使用键(Setno)。INSERTing必须更新该索引；删除键将加快插入速度。

( 10 ) -- flag总是有10个字符长吗？那么在utf8呢？也许您可以使用标志VARCHAR(10)字符集ascii

批处理您的插入--一次100次将运行10倍的速度。(超过100英镑的收入正在进入“递减回报”。)

自动提交的价值是什么？您是否在BEGIN...COMMIT中对每个插入进行包装？innodb_flush_log_at_trx_commit的价值是什么？

Answer 3

排好队。应用程序将一次写入队列1行，然后根据上次插入以来传递的时间的行数，将行取出并批量插入数据库。

我已经看到了一次批量插入10,000块是最快的，所以您需要进行测试才能找到合适的位置。

您可以创建自己的简单队列系统，也可以使用现有的队列系统。下面是一些例子：HornetQ和档案：：队列。这里有一篇关于SE的文章，列出了其他一些好的选择：perl、php、python中的消息队列。