最近处理一个大学的课程管理系统数据库，他们的一个日志表达到2000万行记录的水平，已经有了严重的性能问题，mysql有很多死掉的查询，请问MySQL何时会在记录数上出现瓶颈，有什么合适的解决办法吗？（这里不可能使用NoSQL）
› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
This topic created in 5688 days ago, the information mentioned may be changed or developed.
 |
flytwokitesApr 7, 2011
1
我想是索引没有用好，打开mysql的慢查询功能看看哪些sql有性能问题。
|
 |
sophonApr 7, 2011
2
可以考虑做下拆表
|
 |
3
@flytwokites 索引没问题，第一件事就是检查了索引
|
 |
virushuoApr 7, 2011
4
我听说过的最大表里面有几个亿，每天增量还在百万级别
|
 |
muxiApr 7, 2011
6
2000w而已，不至于出现什么瓶颈，我所用过的表多的会有10亿+的记录，像你这种日志表，只要有足够的内存（我一般给MySQL 8G以上的内存）、查询能使用主键或者索引应该不会出现什么问题，或许你换成Innodb引擎会更快一点，而且大学的课程管理系统，应该不会有很大的并发量，超级大的表，一般慢在磁盘和CPU上，或许你可以换个好点的服务器，如果你熟悉MySQL的话， 最新的5.5版本应该能显著提升你系统的性能
|
|
8
@muxi 现在最大的问题是日志表的查询非常非常频繁，因为这个系统非常依赖日志表显示来显示课程的中最近的活动，大量的查询都卡死在这里，导致很多页面都打不开。数据量可能跟你说的10亿没法比，但这个大学对该系统依赖非常高，所有的授课和考试都通过这个系统进行，并发很大。
可能真的让他们升级硬件或者升级MySQL（现在是5.0），我个人和他们都不倾向升级软件，数据量极大，可能得折腾一星期，还可能耽误授课计划。还有个烦人的问题是升级mysql还要升级linux，mysql5.5用的是新glibc，升级系统要走的流程就更多了，烦。 多谢你的建议。 |
 |
bedApr 7, 2011
11
大学的选课系统都是悲剧。
某些得体的老湿基本上都是被"秒杀" |
|
12
@Livid 谢谢
Gist: https://gist.github.com/907013 Indexes: http://cl.ly/0C2X3s3U2Z213J0e3b0Z 这个表完全没有join查询，都是直来直去的 |
 |
TheOnly92Apr 7, 2011
13
主要是做何种查询？
|
|
14
@TheOnly92
比如 SELECT COUNT(*) FROM log; SELECT * FROM log WHERE course=1 AND module=2 AND action="view"; |
 |
15
@dongsheng 先说一个我觉得奇怪的地方：
bigint(10) 你们的应用中会出现超过 32 位范围的 64 位整数么？如果不会的话，把所有的 bigint 改成 int 可以节约内存。 另外，bigint 的长度应该是 20 而不是 10。 |
|
TheOnly92Apr 7, 2011
16
这个时候做些 alter table 会卡掉整个系统吧
那两个查询的执行时间是多少？ |
|
flytwokitesApr 7, 2011
18
象
SELECT * FROM log WHERE course=1 AND module=2 AND action="view"; 这类语句应该做个组合索引。 |
|
19
@flytwokites 这个做了，你看我发的截图。
|
|
20
@TheOnly92 这个我用mysql客户端执行是瞬间完成的，问题是当并发大的时候，很多这种查询会死掉，用show processlist会看到很多SELECT COUNT(*) FROM log; 锁死在那里了。
|
|
TheOnly92Apr 7, 2011
21
这个难道不能存入缓存吗？看到很多查询锁死，不表示该查询的问题，而是之前的查询还没完，接下来的查询都不能顺利进行。所以还得找出最根本的问题。
|
|
22
@Livid
现在这个表是myisam的，换成innodb会不会有后遗症？ 我严重怀疑mysql不争气，同样的系统，有人用postgresql做存储，大致一个级别的网站，没有出现log表锁死的问题，不过它的服务器要稍好些。 |
 |
liweiApr 7, 2011
23
@dongsheng 是MyISAM的引擎吧，我觉得是频繁的写操作导致大量读操作被锁住，MyISAM的表级锁粒度太大。可以试着分表，把旧的数据独立出来给读取用，新数据写入一个其他的表，然后定期批量写入。或者采用 InnoDB，它用行级锁，锁效率要好的多
|
|
24
@TheOnly92
其实最根本的原因是我不是mysql专家 :-) 实在是缺乏优化的经验。 据我所知mysql有内置的query cache吧，系统里有不少复杂查询，第一次很慢，之后会从缓存取，而不是再跑一遍查询。把SELECT COUNT(*) FROM log;缓存起来不太现实，这个表每分钟都能增长几千几万条记录，其他的查询也是在时时变化的。 |
|
25
@dongsheng 你的这张表有多个 bigint 的索引，按照目前的数据规模，所有的这些索引加起来可能就占用了数 G 的内存。服务器一共有多少内存？现在是否已经开始吃交换分区？这些都是需要看的。
|
|
26
@liwei 这个也考虑过了，两个月前把所有新的log写入log_temp表，然后让cron把log_temp里的东西转移到log表里，这样算是解决了insert锁死的问题，没想到现在连读都能锁死了
|
 |
AntiGameZApr 7, 2011
27
请问一下，moodle有没有什么特殊含义，是系统在moodle上改的，还是随便取的？
|
|
28
@Livid 这个竟然有这么大的区别？我现在没法进produciton server，那边的管理员在另一个半球睡觉，等他们上线让他们查查mysql吃掉的内存。
|
|
29
bigint 是 8 个字节，int 是 4 个字节。
|
|
TheOnly92Apr 7, 2011
30
当时就应该考虑换成 innodb 了吧，如果只是要搞定 COUNT(*) 的问题，可以建立另一个列表在每次 insert 时记录下来，然后之后需要的时候从这个列表读取而不是 COUNT(*) 就行了吧。
但是 myisam 问题确实很大啊。。。 |
|
muxiApr 7, 2011
35
囧，看了表的定义，全都是bigint，连time都是bigint，浪费的不止一点点内存啊
具体你要看服务器本身的负责，io延迟等参数，内存使用量 |
 |
PlatinumApr 7, 2011
39
05 年我面试一人，临走前最后一个问题是你用 slowlog 么，那哥们说 恩，我从来都是设成1
先 slowlog，再抓出语句 explain 又及，你的 key_buffer 用了多少 网上优化的文章一大堆，碰到问题先考虑自己会不会用 MySQL，然后再去考虑 MySQL 的极限是多少 推荐所有想用 MySQL 的人都买本《高性能 MySQL》 http://book.douban.com/subject/4241826/ |
 |
arelessApr 7, 2011
41
SELECT * FROM log WHERE time>* && time<* && ...。
每个查询都提前缩小要查询的范围。 定期OPTIMIZE TABLE。 my.cnf concurrent_insert=2 |
|
PlatinumApr 7, 2011
42
@dongsheng 说的就是你这标题，再配合提问内容，感觉有点不知天高地厚了。碰到问题先想的是不是自己的问题，而是 MySQL 的问题，习惯做这种假设的人实在是不着人喜欢
你要是写成“现在有2000万条记录的表，该如何优化”，我自然也不会提会不会用 MySQL |
|
arelessApr 7, 2011
43
一般来说索引跟SQL语句都没问题的话。MySQL的瓶颈在磁盘IO上。可以用RAID 0来提升磁盘读写速度。或者，设从服务在第二块第三块硬盘。通过mysql proxy分别用不同的从服务去处理查询。当mysql操作swap了，那查询效率肯定下来了，所以内存首先要富裕。
|
 |
kingwkbApr 7, 2011
45
争吵没意思，按时间分区应该管用
|
|
TheOnly92Apr 7, 2011
46
目前楼主能做的是什么？可以暂时让网站的作业停顿下来吗？4～5 小时？
|
 |
bigbrotherApr 7, 2011
47
根据业务对某个字段进行分区就OK了。一般是时间。
我处理过一个亿级的表，分区后查起来飕飕的，而且还有连表查询 |
|
49
@kingwkb @areless 不吵，只是有点不好意思我这些愚笨且不知天高地厚的问题惊动了上面的大神，以后问问题一定先读上一斗书再考虑是否该问 :D
@areless：程序里面的query都是有条件，我上面举的select例子其实也不完全，不光是有course module action的约束也有时间的约束，因为这个表的主要功能就是显示最近的课程活动。刚才跟联系上那个大学的sysadmin，那个服务器的配置确实不佳，内存就挺紧张，以至于不得不用脚本监控进程，达到一定数量就需要重启。我再多研究下数据库，也得建议他们对内存进行升级，谢谢。 我并不是质疑mysql处理不了2000万的数据，而是怀疑我可能错过某些可能很简单的问题导致mysql表现如此槽糕。不管怎么说，感谢楼上所有提出建设性意见的朋友。 |
 |
magic22cnApr 7, 2011
51
大表好解决：Partitioning，单表2000万不算什么，选择好合适的键分区就好了。不过mysql的分区功能很弱，不是所有想法都能实现的
|
 |
no2xApr 7, 2011
52
Try this: SELECT COUNT(id) FROM log;
|
 |
darasionApr 7, 2011
53
唉。这样的问题太像上次要我回答的面试题了。。。可惜我只知道原理，不知道术语以及具体如何去做。没说好。
|
 |
bruceApr 7, 2011 via Android
54
按时间分表分库，分磁盘
|
 |
xmbaoziJun 2, 2017
55
这个全表 count 可以单独用计数器
|
|
xmbaoziJun 2, 2017
56
刚刚在处理一个 5 亿条记录的表。。
|
Select Language