大网数据广告活动
服务器运维问答

MySQL LOAD DATA INFILE: Better Server, Worse Performance

· 52CE 我爱测

I am testing out Microsoft Azure Database for MySQL and have run into a performance issue that I do not understand. I launched a "Basic" server with 1 vCore (2 GB RAM, "Standard Storage"), which is their lowest possible tier of s…

问题原文

原帖链接 · user3175864 · CC BY-SA 4.0

I am testing out Microsoft Azure Database for MySQL and have run into a performance issue that I do not understand.

I launched a "Basic" server with 1 vCore (2 GB RAM, "Standard Storage"), which is their lowest possible tier of server. I created a database, a table, and imported about 4 million rows (30 GB) with LOAD DATA INFILE. It took 56 minutes.

Next, I launched a "Memory Optimized" server with 8 vCores (80 GB RAM, "Premium Storage"). I repeated the exact same tasks and loaded the exact same file. This time it took 7 hours and 16 minutes.

Better server, much worse performance (on this task) -- not what I was expecting. To be certain I had not made a mistake, I repeated the steps above, but I got almost the exact same results again.

I suspect the issue is that the Memory Optimized server has different default server parameters than the Basic server which make this task perform more slowly (I haven't changed the parameters from the defaults that Azure sets). But I am not sure which parameters are the culprit. If anyone has insight into this issue, I'd appreciate it.

Basic server parameters: http://pastebin.zone/wRniyPm6

Memory Optimized server parameters: http://pastebin.zone/phuDcZj4

已采纳解答

原帖链接 · user3175864 · CC BY-SA 4.0

Here's what seems to have been causing this behavior:

Per the Azure documentation, the Basic tier server on Azure comes with "variable" IOPS whereas the Memory Optimized server comes with a fixed IOPS which is based on the amount of storage assigned to the database server.

I had 100GB assigned to the Memory Optimized server. This resulted in it having 300 IOPS, in accordance with Azure's 3 IOPS / GB ratio.

Presumably the "variable" IOPS on the Basic server ended up being significantly more than the 300 IOPS that the Memory Optimized server had.

Lesson learned: to get fast storage access on Azure Database, you need to assign plenty of storage capacity to your server (even if you don't need that much storage!).

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2019-07-02)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 4.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。