Java-mysql 高负载应用程序崩溃
我的 html 抓取器有问题。 Html-scraper 是使用 HtmlUnit 在 Java 上编写的多线程应用程序，默认情况下它以 128 个线程运行。简而言之，它的工作原理如下：它从大文本文件中获取站点 url，ping url，如果可以访问，则解析站点，找到特定的 html 块，将所有 url 和块信息（包括 html 代码）保存到数据库中的相应表中，然后转到下一个站点。数据库是mysql 5.1，有4个InnoDb表和4个视图。表具有表连接中使用的字段的数字索引。我还有一个用于浏览和搜索解析数据的 Web 界面（为了搜索，我使用带有增量索引的 Sphinx），该界面是在 CodeIgniter 上编写的。
服务器配置：
CPU: Type Xeon Quad Core X3440 2.53GHz
RAM: 4 GB
HDD: 1TB SATA
OS: Ubuntu Server 10.04
一些 mysql 配置：
key_buffer = 256M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 128
max_connections = 400
table_cache = 64
query_cache_limit = 2M
query_cache_size = 128M
Java 机器使用默认参数运行，除了以下选项：
-Xms1024m -Xmx1536m -XX:-UseGCOverheadLimit -XX:NewSize=500m -XX:MaxNewSize=500m -XX:SurvivorRatio=6 -XX:PermSize=128M -XX:MaxPermSize=128m -XX:ErrorFile=/var/log/java/hs_err_pid_%p.log
当数据库为空时，scraper每秒处理18个url，并且足够稳定。但在 2 个弱项之后，当 urls 表包含 384929 条记录（约占所有已处理 url 的 25%）并占用 8.2Gb 时，java 应用程序开始工作非常缓慢，并且每 1-2 分钟崩溃一次。我猜原因是mysql，它无法处理不断增长的负载（解析器，它执行2+4*BLOCK_NUMBER查询每个处理的url；sphinx，它每10分钟更新一次增量索引；我不认为Web 界面，因为它只由一个人使用），也许它重建索引非常慢？但是 mysql 和 scraper 日志（也包含所有未捕获的异常）是空的。你对此有何看法？
I have a problem with my html-scraper. Html-scraper is multithreading application written on Java using HtmlUnit, by default it run with 128 threads. Shortly, it works as follows: it takes a site url from big text file, ping url and if it is accessible - parse site, find specific html blocks, save all url and blocks info including html code into corresponding tables in database and go to the next site. Database is mysql 5.1, there are 4 InnoDb tables and 4 views. Tables have numeric indexes for fields used in table joining. I also has a web-interface for browsing and searching parsed data (for searching I use Sphinx with delta indexes), written on CodeIgniter.
Server configuration:
CPU: Type Xeon Quad Core X3440 2.53GHz
RAM: 4 GB
HDD: 1TB SATA
OS: Ubuntu Server 10.04
Some mysql config:
key_buffer = 256M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 128
max_connections = 400
table_cache = 64
query_cache_limit = 2M
query_cache_size = 128M
Java machine run with default parameters except next options:
-Xms1024m -Xmx1536m -XX:-UseGCOverheadLimit -XX:NewSize=500m -XX:MaxNewSize=500m -XX:SurvivorRatio=6 -XX:PermSize=128M -XX:MaxPermSize=128m -XX:ErrorFile=/var/log/java/hs_err_pid_%p.log
When database was empty, scraper process 18 urls in second and was stable enough. But after 2 weaks, when urls table contains 384929 records (~25% of all processed urls) and takes 8.2Gb, java application begun work very slowly and crash every 1-2 minutes. I guess the reason is mysql, that can not handle growing loading (parser, which perform 2+4*BLOCK_NUMBER queries every processed url; sphinx, which updating delta indexes every 10 minutes; I don't consider web-interface, because it's used by only one person), maybe it rebuild indexes very slowly? But mysql and scraper logs (which also contain all uncaught exceptions) are empty. What do you think about it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我建议运行以下命令来检查一些状态。将该输出放在这里也会有所帮助：
dmesgtop检查每个进程的驻留内存与虚拟内存I'd recommend running the following just to check a few status things.. puting that output here would help as well:
dmesgtopCheck the resident vs virtual memory per processes那么应用程序变得无响应？ （与崩溃完全不同）我会检查您的所有资源是否都是免费的。例如，执行
jstack来检查是否有任何线程被占用。检查 MySQL 是否有预期的连接数。如果你不断地在 Java 中创建连接而不清理它们，数据库的运行速度会越来越慢。
So the application become non responsive? (Not the same as a crash at all) I would check all your resources are free. e.g. do a
jstackto check if any threads are tied up.Check in MySQL you have the expect number of connections. If you continuously create connections in Java and don't clean them up the database will run slower and slower.
谢谢大家的建议，问题的根源确实是mysql。通过在 my.conf 中启用慢速查询日志，我发现其中一个执行每次迭代的查询执行了 300 秒（1 个用于搜索的字段未建立索引）。
Thank you all for your advice, mysql was actually cause of the problem. By enabling slow query log in my.conf I see that one of the queries, which executes every iteration, performs 300s (1 field for searching was not indexed).