有没有办法让mysql处理大量并发写入？
我正在制作一个应用程序，陷入进退两难的境地。基本上，该程序会生成一个非常大的数据集（超过 10 GB，超出服务器上的内存，并且以后很难将该数据集从字典传输到其他服务器）。我决定使用数据库来存储数据，但遇到了一个问题，当我生成更多进程时，它们使用的可用核心不超过 10%，但 mysql 服务始终在 60-100% 左右。
使用 mysql 的核心运行在 60-100% 之间。我已经学习了基本的 mysql 课程，但我并不是这方面的专家，我实际上只是使用它来存储/分发程序的局部变量。关于如何解决问题或者即使这个问题可以解决有什么建议吗？我的一个想法是，虽然我的内存无法存储我计划写入的整个数据库，但我希望服务器使用一些内存来处理一些请求，然后可能在内存已满时写入它们（现在mysql使用的内存少于1）服务器内存的百分比）。
正如你知道我的目标，我不忠于任何特定类型的系统，所以如果 mysql 不是一个好的解决方案，我很乐意尝试另一个工具（我本来打算使用 hbase，但首先走上了 mysql 之路）。如果还有什么更好的，请告诉我。
I'm stuck inbetween a rock and a hard place with an application I was making. Basically the program generates a very large dataset(its over 10 gigs and exceeds memory on the server and its hard to transfer this dataset from a dictionary to other servers later on). I decided to use a database to store the data but am running into a problem in which as I spawn more proceses they do not use more than 10% of its available core but the mysql service is always around 60-100%.
The core that is using mysql is running between 60-100%. I have taken basic mysql courses but am no way an expert on it and I'm really just using it to store/distribute local variables of the program. Any suggestions of how I can troubleshoot or even if this problem is possible to solve? One idea I had was although my memory cannot store the entire database I plan to write, I would love for the server to use some memory to process some requests and then maybe write them as memory gets full(right now mysql is using less than 1% of server memory).
As you know my goal, I have no allegiance to any specific type of system so if mysql is not a good solution, I am happy to try another tool(I was going to use hbase, but went down the mysql road first). If anything else is better please let me know.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这完全取决于您实际想要做什么，但您需要提供更多信息才能获得好的建议。
大量写入是什么意思？ 10？ 100？ 1000？每秒？
您尝试写入的模式是什么？架构上实施了哪些索引？
您是否有大量的读取写入操作，或者这都是写入操作？
写作的时候你在做什么？您正在编写的是单行还是包含在事务中的多个操作？
服务器配置是什么？记忆？磁盘？ mySQL 的版本。
你使用 Innodb 作为数据库吗？
现在您可以尝试的一件事是运行 SHOW FULL PROCESSLIST;。这将向您显示是否发生某种类型的锁定 - 如果您看到许多进程处于锁定状态，则查找其中一个未锁定的进程，看看这是否可能导致锁定。请参阅http://dev.mysql.com/doc/refman/ 5.0/en/show-processlist.html 有关此命令的更多信息。
It all depends on what you are actually trying to do but you'll need to provide a lot more information in order to get good advice.
What do you mean by large number of writes? 10? 100? 1000? per second?
What is the schema you are trying to write to? What indexes are implemented on the schema?
Do you have heavy reads to writes or is this all writes?
What are you doing during your write? Is it a single row you are writing or is it a number of operations wrapped in a transaction?
What is the server configuration? Memory? Disk? Version of mySQL.
Are you using Innodb for the database?
Now one thing you can try is to run SHOW FULL PROCESSLIST;. This will show you if there is some type of locking occurring - if you see a lot of the processes in a locked state, then look for one of the processes that is not locked and see if that may be causing locking. See http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html for more info on this command.
看来您只是想保留代码生成（或接收）的大量数据，并且不想经常获取它们（在代码的这一部分中）。如果是这种情况，并且您的数据生成率如此之高，我建议使用 NoSQL 数据库，例如 MongoDB，而不是 MySQL。
它们只是为大量数据集创建的，它们是集群就绪的，并且......（只需转到它们的文档）。
It seems you just want to persist lots of data which is generated (or received) by your code, and you don't want to fetch them often (in this part of the code). If that's the case, and your data generation rate is so high, I recommend using a NoSQL database such as MongoDB instead of MySQL.
They are just created for large number of datasets, they are cluster-ready, and ... (just go to their documentations).