在mysql进程之间设置优先级 [英] Set priorities between mysql processes

问题描述

我正在与MySQL，Grails和Java打交道。

1. 我有应用程序服务器，在那里运行Grails应用程序。 MySQL数据库。
   
2. 我有一个Java应用程序，它连接到服务器以导出csv文件中的数据。

我在数据库中有很多数据（10百万个寄存器），每15分钟Grails应用程序连接到数据库，以检查并保存新的信息。正常的方式来运行。

我的问题是，虽然Grails应用程序工作，我想执行一个Java应用程序，导出数据库中的一部分信息。但是我的问题是这个过程很慢，但是有时候是这样。我将解释：

1. 如果Grails应用程序正在运行，我的Java应用程序需要大约4天才能导出所有数据。 Java proccess占用了CPU的0.3-0％左右。如果我关闭了Tomcat，并因此关闭了所有与DB的连接（在mySQL上显示processlist命令）需要大约40分钟
   才能完成整个过程。 Java proccess大约占用
   CPU的85％。
   





我不确定什么是问题，但我尝试了一切，而我的问题是，我无法停止所有我想要导出的Grails应用程序。正因为如此，在确保我的Java应用程序将具有最高优先级的过程中，有一些方法可以优先考虑这些过程。





感谢您提前答案，


解决方案

这听起来像是一个数据库锁定问题。我建议您购买像 innotop 这样的工具，并仔细查看数据库。特别是，当您的grails应用程序正在运行时，我会仔细查看开放表格，锁定和查询。




I'm fighting with MySQL, Grails and Java.

1. I have the app Server, with Grails app running there.
2. I have another server with mysql db.
3. I have one Java app, that connects to the server to export data in csv files.

I have a lot of data (10Million of registers) in the DB, and every 15 min the Grails app is connecting to the DB, to check and save new info. Normal way to function.

My problem is that while the grails app is working, i want to execute a java app that exports a part of the information in the db. But my problem is that the process is reaaly slow, but only sometimes. I will explain:

1. If the Grails App is working, my Java app needs around 4 days to export all the data.The Java proccess takes around 0.3-0% of CPU.

2. If I turned off the Tomcat, and consequently all the connections to the DB (show processlist command on mySQL) takes around 40 minutes to finish all the process. The Java proccess takes around 85% of CPU.

I'm not sure about what is the problem, but i tried everything, and my problem is that i can't stop the grails app all the times that i want to export. Because of this, there is some way to give priorities to the between processes to be sure that my Java app is going to have the highest priority?

Thanks in advance for your answers,

解决方案

This sounds like a database locking issue. I'd recommend getting a tool like innotop and taking a close look at what is happening in the database. In particular, I'd take a careful look at open tables, locks, and queries active when your grails app is running.

这篇关于在mysql进程之间设置优先级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！