- android - RelativeLayout 背景可绘制重叠内容
- android - 如何链接 cpufeatures lib 以获取 native android 库?
- java - OnItemClickListener 不起作用,但 OnLongItemClickListener 在自定义 ListView 中起作用
- java - Android 文件转字符串
我的所有表都在 myISAM 中,但是当我长时间运行更新作业时,表级锁定开始让我崩溃。我将我的主表转换为 InnoDB,现在我的许多查询都需要 1 分钟以上才能完成,而在 myISAM 上它们几乎是瞬时完成的。它们通常卡在 Sorting result
这一步。我做错了什么吗?
例如:
SELECT * FROM `metaward_achiever`
INNER JOIN `metaward_alias` ON (`metaward_achiever`.`alias_id` = `metaward_alias`.`id`)
WHERE `metaward_achiever`.`award_id` = 1507
ORDER BY `metaward_achiever`.`modified` DESC
LIMIT 100
现在大约需要 90 秒。这是描述:
+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+
| 1 | SIMPLE | metaward_achiever | ref | metaward_achiever_award_id,metaward_achiever_alias_id | metaward_achiever_award_id | 4 | const | 66424 | Using where; Using filesort |
| 1 | SIMPLE | metaward_alias | eq_ref | PRIMARY | PRIMARY | 4 | paul.metaward_achiever.alias_id | 1 | |
+----+-------------+-------------------+--------+-------------------------------------------------------+----------------------------+---------+---------------------------------+-------+-----------------------------+
似乎现在我的查询中有 TONS 卡在了“排序结果”步骤中:
mysql> show processlist;
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 460568 | paul | localhost | paul | Query | 0 | NULL | show processlist |
| 460638 | paul | localhost | paul | Query | 0 | Sorting result | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met |
| 460710 | paul | localhost | paul | Query | 79 | Sending data | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met |
| 460722 | paul | localhost | paul | Query | 49 | Updating | UPDATE `metaward_alias` SET `modified` = '2009-09-15 12:43:50', `created` = '2009-08-24 11:55:24', ` |
| 460732 | paul | localhost | paul | Query | 25 | Sorting result | SELECT `metaward_achiever`.`id`, `metaward_achiever`.`modified`, `metaward_achiever`.`created`, `met |
+--------+------+-----------+------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
为什么这个简单的更新卡住了 49 秒?
如果有帮助,这里是模式:
| metaward_alias | CREATE TABLE `metaward_alias` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`modified` datetime NOT NULL,
`created` datetime NOT NULL,
`string_id` varchar(255) DEFAULT NULL,
`shortname` varchar(100) NOT NULL,
`remote_image` varchar(500) DEFAULT NULL,
`image` varchar(100) NOT NULL,
`user_id` int(11) DEFAULT NULL,
`type_id` int(11) NOT NULL,
`md5` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `string_id` (`string_id`),
KEY `metaward_alias_user_id` (`user_id`),
KEY `metaward_alias_type_id` (`type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=858381 DEFAULT CHARSET=utf8 |
| metaward_award | CREATE TABLE `metaward_award` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`modified` datetime NOT NULL,
`created` datetime NOT NULL,
`string_id` varchar(20) NOT NULL,
`owner_id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`description` longtext NOT NULL,
`owner_points` int(11) NOT NULL,
`url` varchar(500) NOT NULL,
`remote_image` varchar(500) DEFAULT NULL,
`image` varchar(100) NOT NULL,
`parent_award_id` int(11) DEFAULT NULL,
`slug` varchar(110) NOT NULL,
`true_points` double DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `string_id` (`string_id`),
KEY `metaward_award_owner_id` (`owner_id`),
KEY `metaward_award_parent_award_id` (`parent_award_id`),
KEY `metaward_award_slug` (`slug`),
KEY `metaward_award_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=122176 DEFAULT CHARSET=utf8 |
| metaward_achiever | CREATE TABLE `metaward_achiever` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`modified` datetime NOT NULL,
`created` datetime NOT NULL,
`award_id` int(11) NOT NULL,
`alias_id` int(11) NOT NULL,
`count` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `metaward_achiever_award_id` (`award_id`),
KEY `metaward_achiever_alias_id` (`alias_id`)
) ENGINE=InnoDB AUTO_INCREMENT=77175366 DEFAULT CHARSET=utf8 |
还有我的my.cnf里的这些
innodb_file_per_table
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 16M
innodb_flush_method=O_DIRECT
最佳答案
写在Should you move from MyISAM to Innodb ? (这是最近的):
Innodb Needs Tuning As a final note about MyISAM to Innodb migration I should mention about Innodb tuning. Innodb needs tuning. Really. MyISAM for many applications can work well with defaults. I’ve seen hundreds of GB databases ran with MyISAM with default settings and it worked reasonably. Innodb needs resources and it will not work well with defaults a lot. Tuning MyISAM from defaults rarely gives more than 2-3 times gain while it can be as much as 10-50 times for Innodb tables in particular for write intensive workloads. Check here for details.
所以,关于MySQL Innodb Settings,作者在Innodb Performance Optimization Basics中写道:
The most important ones are:
innodb_buffer_pool_size 70-80% of memory is a safe bet. I set it to 12G on 16GB box.
UPDATE: If you’re looking for more details, check out detailed guide on tuning innodb buffer pool
innodb_log_file_size – This depends on your recovery speed needs but 256M seems to be a good balance between reasonable recovery time and good performance
innodb_log_buffer_size=4M 4M is good for most cases unless you’re piping large blobs to Innodb in this case increase it a bit.
innodb_flush_log_at_trx_commit=2 If you’re not concern about ACID and can loose transactions for last second or two in case of full OS crash than set this value. It can dramatic effect especially on a lot of short write transactions.
innodb_thread_concurrency=8 Even with current Innodb Scalability Fixes having limited concurrency helps. The actual number may be higher or lower depending on your application and default which is 8 is decent start
innodb_flush_method=O_DIRECT Avoid double buffering and reduce swap pressure, in most cases this setting improves performance. Though be careful if you do not have battery backed up RAID cache as when write IO may suffer.
innodb_file_per_table – If you do not have too many tables use this option, so you will not have uncontrolled innodb main tablespace growth which you can’t reclaim. This option was added in MySQL 4.1 and now stable enough to use.
Also check if your application can run in READ-COMMITED isolation mode – if it does – set it to be default as transaction-isolation=READ-COMMITTED. This option has some performance benefits, especially in locking in 5.0 and even more to come with MySQL 5.1 and row level replication.
仅作记录,mysqlperformanceblog.com 背后的人员运行了一个比较 Falcon、MyISAM 和 InnoDB 的基准测试。基准测试本来应该突出 Falcon,但 InnoDB 赢得了这一天,几乎在每个测试中每秒查询次数都超过了 Falcon 和 MyISAM:InnoDB vs MyISAM vs Falcon benchmarks – part 1 .
关于sql - InnoDB 排序真的那么慢吗?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/1428678/
自己试试看: import pandas as pd s=pd.Series(xrange(5000000)) %timeit s.loc[[0]] # You need pandas 0.15.1
我最近开始使用 Delphi 中的 DataSnap 来生成 RESTful Web 服务。在遵循 Marco Cantu 本人和互联网上其他几个人的指导后,我成功地使整个“链条”正常工作。 但是有一
我一直在为操作系统类(class)编写以下代码,但结果有些奇怪。该代码创建x线程并同时运行它们,以便将两个平方矩阵相乘。每个线程将输入矩阵的Number_of_rows/Number_of_threa
我正在尝试确定何时使用 parallel包以加快运行某些分析所需的时间。我需要做的一件事是创建矩阵,比较具有不同行数的两个数据框中的变量。我在 StackOverflow 上问了一个关于有效方法的问题
我最近对我的代码进行了一些清理,并在此过程中更改了此内容(不完全是真实的代码): read = act readSTRef test1 term i var = do t v^!terms.
我正在计时查询和同一个查询的执行时间,分页。 foreach (var x in productSource.OrderBy(p => p.AdminDisplayName) .Wher
我正在开发一个项目 (WPF),我有一个 Datagrid 从数据库加载超过 5000 条记录,所以我使用 BackgroundWorker 来通知用户数据正在加载,但它太慢了,我需要等待将近 2分钟
我在查询中添加 ORDER BY 时遇到问题。没有 ORDER BY 查询大约需要 26ms,一旦我添加 ORDER BY,它大约需要 20s。 我尝试了几种不同的方法,但似乎可以减少时间。 尝试 F
我是 Android 开发新手,遇到了性能问题。当我的 GridView 有太多项目时,它会变得有点慢。有什么方法可以让它运行得更快一些吗? 这是我使用的代码: 适配器: public class C
这里的要点是: 1.设置query_cache_type = 0;重置查询缓存; 2.在 heidisql(或任何其他客户端 UI)中运行任何查询 --> 执行,例如 45 毫秒 3.使用以下代码运行
想象下表: CREATE TABLE drops( id BIGSERIAL PRIMARY KEY, loc VARCHAR(5) NOT NULL, tag INT NOT
我的表 test_table 中的示例数据: date symbol value created_time 2010-01-09 symbol1
首先,如果已经有人问过这个问题,我深表歉意,至少我找不到任何东西。 无论如何,我将每 5 分钟运行一次 cron 任务。该脚本加载 79 个外部页面,而每个页面包含大约 200 个我需要在数据库中检查
我有下面的 SQL 代码,它来自 MySQL 数据库。现在它给了我期望的结果,但是查询很慢,我想我应该在进一步之前加快这个查询的速度。 表agentstatusinformation有: PKEY(主
我需要获取一个对象在 Core Data 中数千个其他对象之间的排名。现在,这是我的代码: - (void)rankMethod { //Fetch all objects NSFet
我正在编写一个应用程序,我需要在其中读取用户的地址簿并显示他所有联系人的列表。我正在测试的 iPhone 有大约 100 个联系人,加载联系人确实需要很多时间。 ABAddressBookRef ad
我正在使用 javascript 将 160 行添加到包含 10 列的表格中。如果我这样做: var cellText = document.createTextNode(value); cell.a
我是 Swift 的新手,我已经设置了一个 tableView,它从 JSON 提要中提取数据并将其加载到表中。 表格加载正常,但是当表格中有超过 10 个单元格时,它会变得缓慢且有些滞后,特别是它到
我在 InitializeCulture 和 Page_PreInit 事件之间的 asp.net 页面中遇到性能问题。当我重写 DeterminePostBackMode() 时,我发现问题出在 b
我在 Hetzner 上有一个带有 256GB RAM 6 个 CPU(12 个线程) 的专用服务器,它位于德国。我有 CENTOS 7.5。 EA4。 我的问题是 SSL。每天大约 2 小时,我们在
我是一名优秀的程序员,十分优秀!