扩展写入密集型应用程序（LAMP、MySQL）（目录样式应用程序）
我想知道这里是否有人有因文件导入而导致写入密集型数据的经验。
主要要求是业务用户能够导入表示主表之间关系的数据。他们应该能够实时导出相同的内容（尽可能多）。
设置：
- 前端（php）应用程序写入 MASTER 数据库。
- 复制设置 - 主数据库被复制到两个从数据库服务器。
- 其中一台 SLAVE 服务器用作前端 UI 交互（大量查询）的“读取”数据库。
- 相同的从属服务器还用于基于已在前端预览的查询“导出”数据。 （很多 JOIN 表）。
主要挑战是复制日志。用户对性能和性能不满意即使导入的文件已经被处理，数据在前端也不可用。复制 LAG 是罪魁祸首。
迁移到 NoSQL ie 是长期目标。目前仍想提高性能。顺便说一下，该应用程序是内部使用的，但托管在一家知名的托管公司。用户数量约为150名用户。
导入的数据约为 200k - 800k 行。每条线代表一行。
任何意见将不胜感激:)
I was wondering if there's anyone out here who has experience with write-intensive data due to file import.
The main requirement is for the business users to be able to import a data that represents relationships between master tables. They should be able to export the same in real-time ( as much as possible).
Setup:
- front-end (php) application writes on a MASTER database.
- Replication Setup
- Master DB is replicated to two SLAVE DB servers. - one of the SLAVE server is used as "read" database for front-end UI interactions (heavy queries).
- The same SLAVE server is also used for "EXPORTING" data based on a query that has been previewed on the front-end. (Lots of JOIN table).
The main challenge has been replication log. The users are not happy with the performance & data not being available on the front-end even though the files they've import has been processed already. Replication LAG is the culprit.
Moving to NoSQL i.e. is the LONG Term goal. Still want to improve the performance for now. By the way, the application is used internally but is hosted in a well-known hosting company. Number of users is around 150 users.
Imported data is around 200k - 800k lines. Each line represent a single row.
Any inputs would be appreciated :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
有很多因素可以改善 MySQL 复制延迟。也许他们的 DBA 在 Youtube 上发布的有关 MySQL 复制的播客可能会为您提供一些提示和信息。指针：
http://itc.conversationsnetwork.org/shows/detail3299.html
希望这有帮助。
There are many factors that play a part in improving MySQL replication lag. Perhaps this podcast about MySQL replication on Youtube by their DBA might provide you with some tips & pointers:
http://itc.conversationsnetwork.org/shows/detail3299.html
Hope this helps.
@Wrikken，
（通过答案框回答）
是的，很多“插入”和更新。应用程序利用临时表（即带有某些前缀的真实物理表）来插入初步数据。并执行大量 INSERT INTO .. SELECT * FROM ..
这导致大量语句被复制到 SLAVE，最终临时表被删除。我已经建议这种类型的表应该从要复制的表列表中排除，而不是使用 INSERT INTO ... SELECT * FROM 应用程序应该只从应用程序上构建 INSERT、UPDATE、DELETE 语句中选择所有内容内存空间并执行SQL语句。它应该减少复制的临时表相关语句的数量。
@Wrikken,
(Answering via the answer box)
Yes, lots of "inserts" and updates. The application makes use of temporary tables (i.e. real physical table with some prefix) to insert preliminary data. And does a lot of INSERT INTO .. SELECT * FROM ..
This is causing a lot of statements being replicated to the SLAVE which in the end temporary tables are being dropped. I have already recommeded that such type of table should be excluded from the list of tables to be replicated and instead of using INSERT INTO ... SELECT * FROM the application should just SELECT everything from build the INSERT, UPDATE, DELETE statements on the application memory space and execute the SQL statment. It should lessen the number of temp table related statements from being replicated.