首页 \ 问答 \ 在MySQL中加载一个表是非常慢的(Loading one table in MySQL is ridiculously slow)

在MySQL中加载一个表是非常慢的(Loading one table in MySQL is ridiculously slow)

清除数据库中的所有其他表按预期工作,并在几分之一秒内加载〜200万行。 只有约600行的一个表需要10分钟才能加载到navcat中。

我想不出任何可能的原因。 只有4列。 其中一个一个大文本字段,但我之前使用过大文本字段,它们从来没有这么慢。

运行explain select * from parser_queue我得到了

 id  setect type  table     type  possible keys  key  key len  ref  rows  extra
 1   SIMPLE    parser_queue  ALL  -              -    -        -    658   - 

该配置文件告诉我453秒花费'发送数据'我也在'状态'选项卡中得到了这个。 我不了解大部分内容,但这些数字比我的其他表格要高得多。

Bytes_received            31
Bytes_sent                32265951
Com_select                1
Created_tmp_files         16
Handler_read_rnd_next     659
Key_read_requests         9018487
Key_reads                 3928
Key_write_requests        310431
Key_writes                4290
Qcache_hits               135077
Qcache_inserts            14289
Qcache_lowmem_prunes      4133
Qcache_queries_in_cache   983
Questions                 1
Select_scan               1
Table_locks_immediate     31514

存储在文本字段中的数据平均约为12000个字符。 有一个主要的自动增量int id字段,一个tinyint状态字段,一个text字段和一个带有on update current timestamptimestamp字段。


好的我会尝试两个答案,但我可以先快速回答问题:

ID字段上的主键是唯一的键。 此表用于排队,每小时添加/删除约50条记录,但我昨天才创建它。 它会在如此短的时间内被破坏吗?

这是MyISAM


尝试隔离问题的更多工作:

repair table没有做任何optimize table没有创建临时表。 查询在临时表上慢了约50%。

删除了表并重建了它。 SELECT *只用了4行就需要18秒。

这是我用来创建表的SQL:

CREATE TABLE IF NOT EXISTS `parser_queue` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` tinyint(4) NOT NULL DEFAULT '1',
  `data` text NOT NULL,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

更奇怪的是,我当地的盒子上的一切似乎都很好。 缓慢只发生在开发站点上。

为清楚起见:开发站点上有超过100个表,这是唯一一个时髦的表。


好的我已禁用所有使用此表的cron作业。 SHOW PROCESSLIST不会在表格上显示任何锁定。

将引擎更改为InnoDB没有产生任何显着的改进(对于MyISAM,86秒对94)

还有其他想法吗? 。 。 。

在查询期间运行SHOW PROCESSLIST显示它花费大部分时间writing to net


Fro clarity all other tables in the DB work as expected, and load ~2million rows in a fraction of a second. The one table of just ~600 rows is taking 10+minutes to load in navcat.

I can't think of any possible reason for this. There are just 4 columns. One of them is a large text field, but I've worked with large text fields before and they've never been this slow.

running explain select * from parser_queue I get

 id  setect type  table     type  possible keys  key  key len  ref  rows  extra
 1   SIMPLE    parser_queue  ALL  -              -    -        -    658   - 

The profile tells me that 453 seconds are spent 'sending data' I've also got this in the 'Status' tab. I don't understand most of it, but these numbers are much higher than my other tables.

Bytes_received            31
Bytes_sent                32265951
Com_select                1
Created_tmp_files         16
Handler_read_rnd_next     659
Key_read_requests         9018487
Key_reads                 3928
Key_write_requests        310431
Key_writes                4290
Qcache_hits               135077
Qcache_inserts            14289
Qcache_lowmem_prunes      4133
Qcache_queries_in_cache   983
Questions                 1
Select_scan               1
Table_locks_immediate     31514

The data stored in the text field is about 12000 chars on average. There is a primary, auto increment int id field, a tinyint status field, a text field, and a timestamp field with on update current timestamp.


OK I will try out both answers, but I can answer the questions quickly first:

Primary key on the ID field is the only key. This table is used for queuing, with ~50 records added/deleted per hour, but I only created it yesterday. Could it become corrupted in such a short time?

It is MyISAM


More work trying to isolate the problem:

repair table did nothing optimize table did nothing created a temp table. queries were about 50% slower on the temp table.

Deleted the table and rebuilt it. SELECT * takes 18 seconds with just 4 rows.

Here is the SQL I used to create the table:

CREATE TABLE IF NOT EXISTS `parser_queue` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` tinyint(4) NOT NULL DEFAULT '1',
  `data` text NOT NULL,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

Stranger still, everything seems fine on my local box. The slowness only happens on the dev site.

For clarity: there are more than 100 tables on the dev site and this is the only one that is funky.


OK I have disabled all cron jobs which use this table. SHOW PROCESSLIST does not reveal any locks on the table.

Changing the engine to InnoDB did not produce any significant improvement (86 seconds vs 94 for MyISAM)

any other ideas? . . .

Running SHOW PROCESSLIST during the query reveals it spends most of its time writing to net


原文:https://stackoverflow.com/questions/5843312
更新时间:2020-05-08 20:23

相关文章

更多

最新问答

更多
  • jsPlumb draggable element javascript函数(jsPlumb draggable element javascript function)
  • MVC4:ViewModel(带有radiobuttonlist)在HttpPost之后为空(MVC4: ViewModel (with radiobuttonlist) is empty after HttpPost)
  • 如何在同一帐户上设置“Dev repo”(在prod和团队之间)(How to set up a “Dev repo” (between the prod and the team) on the same account)
  • 如何在tcl中将eth0配置为发送方udp端口(how to configure eth0 as a sender udp port in tcl)
  • 如何在datarow []中的列中找到最大值?(How to find max value in a column in a datarow[] ?)
  • 如何使用预定义文本替换来自数据库的部分结果(How do I replace part of result coming from Database with predefined text)
  • Selenium Java注入了新的Javascript函数(Selenium Java inject new Javascript function)
  • 使用.on的多个下拉菜单选择文本仅适用于第一个下拉列表(Multiple Dropdowns Menu Selection text using .on works only on first dropdown)
  • 快速将黄土曲线添加到大型数据集图中的方法(Quick way to add loess curve to large data set graph)
  • FilteringSelect in mvc(FilteringSelect in mvc)
  • 在Delphi XE2中开发Mac或iOS应用程序需要哪些硬件/软件?(What hardware/software is necessary to develop Mac or iOS apps in Delphi XE2?)
  • 在原型的构造函数中初始化属性时获取“未定义”(Getting 'undefined' when a property is initialized in the constructor of a prototype)
  • 通过越狱加载的应用程序的Documents文件夹位置(Location of Documents folder for an app loaded via jailbreak)
  • 在OpenGL中使用可编程和固定管道功能(Using both programmable and fixed pipeline functionality in OpenGL)
  • 将任何用户输入重定向到单独的底层程序(redirect any user input to a separate underlying program)
  • 编辑文本不能正常工作android(Edit texts not working properly android)
  • “user_denied”Facebook应用页面上的Facebook用户区域设置(Facebook user locale on “user_denied” facebook app page)
  • 在大图像中找到小的部分透明图像的坐标(find coordinates of small partially-transparent image within a large image)
  • 我如何在cakephp 3.1中获得完整的相对路径?(How i can get full relative path of image in cakephp 3.1?)
  • 如何保存拖动标记的新本地化?(How to save new localization of dragged marker?)
  • MySQL UPDATE vs INSERT和DELETE(MySQL UPDATE vs INSERT and DELETE)
  • 在执行查询之前,在SQLAlchemy模型中将datetime转换为unix时间戳?(Convert datetime to unix timestamp in SQLAlchemy model before executing query?)
  • OpenCL与OpenGL互操作的优势(Advantage of OpenCL interoperability with OpenGL)
  • 如何解析用点和等分隔的数据然后添加到listview(How to parsing data from delimited with dot and equal then add to listview)
  • 带调试输出的X3解析器段错误(BOOST_SPIRIT_X3_DEBUG)(X3 parser segfaults with debug output (BOOST_SPIRIT_X3_DEBUG))
  • 将文件夹名称添加到fgrep结果(Add folder name to fgrep result)
  • 在MySQL中加载一个表是非常慢的(Loading one table in MySQL is ridiculously slow)
  • 如何将JSON放入PHP变量?(How do I put JSON into a PHP Variable?)
  • 如何绕过Microsoft.Speech.Recognition中的不流畅?(How to bypass disfluencies in Microsoft.Speech.Recognition?)
  • 原点的最后一行是什么?(What is the last row of an origin for?)