blocks|key|267196|text|好吧，我从来没有找到一个节流的方法，但我确实找到了一个方法来缓解我的问题。这个解决方案对我的问题来说是独一无二的，但我会把它贴出来，以防其他人发现它有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|267197|我写了一个名为CautiousIndexer的类。|offset|length|style|CODE|267198|267199|首先，我存储了create+table语句，以重新创建不带索引的表结构。我存储了一个读从数据库数组，循环访问它们，将包含未索引数据的表重命名为prevent_indexing_($name)。然后，我只在从服务器上运行了create+|ordered-list-item|267200|语句。这有效地将数据移出了将在主服务器上发生的索引语句的方式。|267201|，然后我对主节点运行了索引查询。由于新创建的表为空，因此在主服务器进行索引时，读从服务器不会对性能产生任何影响。|267202|当主服务器完成索引时，我从生产循环中取出1个从机，删除空表，将整个表移回原位，然后在非生产从机上索引该表。|267203|，|267204|完成后，我将其放回生产环境中，并在剩余的从机上重复从机索引过程。|267205|当所有从机都建立了索引后，我将表投入生产。|267206|267207|这在效率方面仍然很好，但是在主服务器上的索引期间，写性能变得不可接受地变慢了。仍在寻找一种使用节流进行索引的方法。|267208|entityMap^0|0|7|F|0|0|1Z|O|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@$D|14|E|15|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|17|8|@$D|18|E|19|F|G]]|9|@]|A|$]]|$1|L|3|M|5|K|7|1A|8|@]|9|@]|A|$]]|$1|N|3|O|5|K|7|1B|8|@]|9|@]|A|$]]|$1|P|3|Q|5|K|7|1C|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1D|8|@]|9|@]|A|$]]|$1|T|3|U|5|K|7|1E|8|@]|9|@]|A|$]]|$1|V|3|W|5|K|7|1F|8|@]|9|@]|A|$]]|$1|X|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1H|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]]|11|$]]

Well, I never found a way to throttle, but I did figure out a way to alleviate my problem. 
The solution was unique to my problem, but I'll post it in case someone else finds it useful. 

I wrote a class named <code>CautiousIndexer</code>. 

<ol>
<li>First I stored the create table statement to recreate the table structure without indexes. I stored an array of read slave databases, looped through them renaming the table with the unindexed data to <code>prevent_indexing_($name)</code>. </li>
<li>Then I ran the create table statement on the slaves only. This effectively moved the data out of the way of indexing statements that would happen on the master. </li>
<li>Then I ran the index query against the master. Read slaves had no performance impact while the master was indexing because the newly created tables were empty. </li>
<li>When the master finished indexing, I took 1 of the slaves out of production rotation, dropped the empty table, moved the full table back in place, then indexed the table on the out of production slave. </li>
<li>When that finished I put it back in production and repeated the slave indexing procedure on the remaining slaves. </li>
<li>When all slaves were indexed, I put the table into production.</li>
</ol>

This was still fine in terms of efficiency, but during the indexing on the master server write performance was unacceptably slowed. Still looking for a way to index with throttling.

blocks|key|2967713|text|一个以前没有尝试过的想法也不是关于索引节流的，如果你创建一个备份表并用你提到的方式更新它，会不会比转换/重命名表的时间跨度更短。我鼓励你写下我的想法，你需要知道一种方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2967714|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

An idea not tried before also not about index throttling , what if you make a backup table and update it with the way you mentioned has shorter time span and than convert/rename the tables. I encourage to write my thoughts b/c you need to know a way.

blocks|key|267240|text|您可以在插入时禁用任何非唯一索引，并在完成后重新启用它们。看看disable+keys+/+enable+keys。但它只适用于非唯一索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|267241|如果使用多值insert语句(insert+into+table(...)值(...)、(...)、(...)...|267242|顺便说一句，加载数据文件似乎是在mysql中插入大量数据的最快方法。|267243|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

You can disable any non-unique indexes while inserting, and re-enable them after you finish. Take a look at disable keys / enable keys. But it works only for non-unique indexes.

You can speed up inserts as well if you use multi-values insert statements (insert into table(...) values(...),(...),(...)...

By the way, load data infile seems to be the fastest way to insert a lot of data in mysql.

blocks|key|272849|text|这并不是您正在寻找的确切解决方案，但是您可以在这个单独的盒子上构建a+second+mysqld+instance+as+a+slave，并根据需要将SELECT查询重定向到它。有MySQL+Proxy可以帮助你在不重写客户端应用的情况下实现这一点。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|272850|您还可以从FriendFeed+usage+of+MySQL收集一些想法。它们将实际的索引存储在其他表中，并使用它们进行搜索。如果您将数据副本存储在其他表中，甚至存储在其他服务器上，并在那里运行索引，您将能够以最快速度访问主数据，并在以后使用其他服务器获得更快的查询。|272851|这就像在从服务器上为搜索类型的查询添加索引，而只在主服务器上运行主键查找一样。|272852|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/replication.html|1|http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy.html|2|http://bret.appspot.com/entry/how-friendfeed-uses-mysql^0|23|6|X|Z|0|2I|B|1|0|5|P|2|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@$9|W|A|X|B|C]]|D|@$9|Y|A|Z|1|10]|$9|11|A|12|1|13]]|E|$]]|$1|F|3|G|5|6|7|14|8|@]|D|@$9|15|A|16|1|17]]|E|$]]|$1|H|3|I|5|6|7|18|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|K|$L|$5|M|N|O|E|$P|Q]]|R|$5|M|N|O|E|$P|S]]|T|$5|M|N|O|E|$P|U]]]]

This is not an exact solution you're looking for, but you can bing up <a href="http://dev.mysql.com/doc/refman/5.1/en/replication.html" rel="nofollow">a second mysqld instance as a slave</a> on this single box and redirect <code>SELECT</code> queries to it as needed. There is <a href="http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy.html" rel="nofollow">MySQL Proxy</a> which can help you to accomplish this without rewriting client apps.

You can also gather some ideas from <a href="http://bret.appspot.com/entry/how-friendfeed-uses-mysql" rel="nofollow">FriendFeed usage of MySQL</a>. They store actual indexes in other tables and use them for search. If you store a copy of you data in other table even on other server and run indexes there, you'll be able to access master data ASAP at full speed and get speedier queries later using other server. 

It's like if you add indexes on a slave for search-type queries and run only primary key lookups on the master.

blocks|key|2967805|text|一个很好的解决方案是使用一个执行滚动更新的脚本。您将以非复制的方式将索引应用于每个从属服务器。下面是一个粗略的说明：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2967806|for+host+in+$hosts
do
++++mysql+-h+$host+-e+"STOP+SLAVE;\
++++++SET+sql_log_bin=0;\
++++++FLUSH+TABLE+t;\
++++++ALTER+TABLE+t+ADD+INDEX+a+(b,c);\
++++++SET+sql_log_bin=1;\
++++++START+SLAVE;"
done|code-block|syntax|javascript|2967807|通过关闭复制，应减少磁盘活动量并提高索引操作的速度。如果您对从服务器有数据库延迟要求，那么您可能希望完全解除从服务器的池，并在从服务器恢复零秒延迟时包含重新池化从服务器的逻辑。|2967808|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

A good solution to this is a script that performs a rolling update. You would apply the index to each slave in a non-replicating manner. A rough illustration:

<pre><code>for host in $hosts
do
 mysql -h $host -e "STOP SLAVE;\
 SET sql_log_bin=0;\
 FLUSH TABLE t;\
 ALTER TABLE t ADD INDEX a (b,c);\
 SET sql_log_bin=1;\
 START SLAVE;"
done
</code></pre>

By turning off replication, the amount of disk activity should be reduced and increase the speed of the indexing operation. If you have database lag requirements for your slaves, you might want to entirely de-pool the slave and include logic to re-pool the slave when it resumes zero seconds lag.

blocks|key|272879|text|您是否尝试过为导入操作提升索引设置？这可以显著提高导入性能。sort_buffer_size适用于任何表类型，myisam_sort_buffer_size适用于MyISAM表。innodb_buffer_pool_size在某种程度上是Innodb的“密钥缓存”。根据您的表类型，为导入增加这些值。您要做的是避免在索引创建过程中对文件进行排序。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|272880|您可以将导入/索引时间缩短到10-15分钟或更短。它不是节流，但它将显着缩短影响期限。|272881|或者，如果您使用的是MyISAM表，也许可以选择合并表？创建一个新表，执行导入，然后将新表添加到合并表中。导入过程中不会对数据库造成影响。除了执行任务的服务器之外。|272882|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Have you tried bumping up your index settings for the import? That can increase import performance significantly. sort_buffer_size is for any table type, myisam_sort_buffer_size is for MyISAM tables. innodb_buffer_pool_size is sort of your "key cache" for Innodb. Bump those up for the import depending on your table type. What you are trying to do is avoid file sorting during index creation.

You may be able to get your import/index time down to 10-15 minutes or less. It's not throttling, but it will significantly shorten the impact period.

Or, if you are using MyISAM tables, maybe a MERGE table is an option? Create a new table, perform the import, than add the new table to the MERGE table. There will be no impact on the database during import. Aside from the server performing a task.

I need to load a large data set onto a production database. 

15 files need to each be uploaded and inserted into a table. 
Each is about 500 Mb. 

I have two ID columns that need to be indexed. 
If I load the files with indexes in place, the upload takes around 3 hours. 
If I drop indexes, load data local infile, then re-add the indexes, the whole operation takes about 30 minutes. 

The problem is, database responsiveness takes a big hit while indexing the freshly imported data. Is there a way to make the indexing run at a "low priority" so that other queries still get 95-100% speed and the indexing kind of chugs along in the background?

I'm using Amazon RDS, so I don't have the option of just loading on a different server then copying over the table files.

Adding a bounty to this as I still want to see if there is a way to get good performance while indexing on a specific box.

is there a way to throttle the indexing of Mysql tables so overall performance is not impacted?

数据库

服务器

我需要将一个大型数据集加载到生产数据库中。每个15文件都需要上传到并插入到一个表中。每个约为500Mb。我有两个ID列需要编制索引。如果我加载带有索引的文件，上传大约需要3个小时。如果我删除索引，加载数据本地infile，然后重新添加索引，整个操作大约需要30分钟。问题是，在索引新导入的数据时，数据库的响应性对造成了很...

问有没有办法限制Mysql表的索引，这样整体性能就不会受到影响？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问有没有办法限制Mysql表的索引，这样整体性能就不会受到影响？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问有没有办法限制Mysql表的索引，这样整体性能就不会受到影响？
EN