回复： 回复： 回复： Why is creating indexes faster after inserting massive data rows?
Ok, OS cache. There isn't really a million of block writes. The record gets added to the block, but that gets modified in OS cache if we assume MyISAM tables and in the Innodb buffer if we assume InnoDB tables. As i known, the mysql writes the data to disk directly but does not use the Os cache when the table is updating. If it writes to the Os cache, which leads to massive system invoking, when the table is inserted a lot of rows one by one. 发件人： Karen Abgarian a...@apple.com 收件人： mysql@lists.mysql.com 发送日期： 2012年5月8日, 星期二, 上午 11:37 主题: Re: 回复： 回复： Why is creating indexes faster after inserting massive data rows? Honestly, I did not understand that. I did not say anything about being complicated. What does mysql not use, caching?? Judging by experience, creating a unique index on say, a 200G table could be a bitter one. On 07.05.2012, at 19:26, Zhangzhigang wrote: Karen... The mysql does not use this approach what you said which is complicated. I agree with ohan De Meersman. 发件人： Karen Abgarian a...@apple.com 收件人： mysql@lists.mysql.com 发送日期： 2012年5月8日, 星期二, 上午 1:30 主题: Re: 回复： Why is creating indexes faster after inserting massive data rows? Hi, A couple cents to this. There isn't really a million of block writes. The record gets added to the block, but that gets modified in OS cache if we assume MyISAM tables and in the Innodb buffer if we assume InnoDB tables. In both cases, the actual writing does not take place and does not slow down the process. What does however happen for each operation, is processing the statement, locating the entries to update in the index, index block splits and , for good reason, committing. When it comes to creating an index, what needs to happen, is to read the whole table and to sort all rows by the index key. The latter process will be the most determining factor in answering the original question, because for the large tables the sort will have to do a lot of disk I/O. The point I am trying to make is there will be situations when creating indexes and then inserting the rows will be faster than creating an index afterwards. If we try to determine such situations, we could notice that the likelihood of the sort going to disk increases with the amount of distinct values to be sorted. For this reason, my choice would be to create things like primary/unique keys beforehand unless I am certain that everything will fit in the available memory. Peace Karen On May 7, 2012, at 8:05 AM, Johan De Meersman wrote: - Original Message - From: Zhangzhigang zzgang_2...@yahoo.com.cn Ok, Creating the index *after* the inserts, the index gets created in a single operation. But the indexes has to be updating row by row after the data rows has all been inserted. Does it work in this way? No, when you create an index on an existing table (like after a mass insert), what happens is that the engine does a single full tablescan and builds the index in a single pass, which is a lot more performant than updating a single disk block for every record, for the simple reason that a single disk block can contain dozens of index entries. Imagine that you insert one million rows, and you have 100 index entries in a disk block (random numbers, to make a point. Real numbers will depend on storage, file system, index, et cetera). Obviously there's no way to write less than a single block to disk - that's how it works. You can update your index for each record in turn. That means you will need to do 1 million index - and thus block - writes; plus additional reads for those blocks you don't have in memory - that's the index cache. Now, if you create a new index on an existing table, you are first of all bypassing any index read operations - there *is* no index to read, yet. Then the system is going to do a full tablescan - considered slow, but you need all the data, so there's no better way anyway. The index will be built - in-memory as much as possible - and the system will automatically prefer to write only complete blocks - 10.000 of them. That's the exact same number of index blocks, but you only write each block once, so that's only 10.000 writes instead of 1.000.000. Now there's a lot more at play, things like B-tree balancing and whatnot, but that's the basic picture. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: 回复： 回复： Why is creating indexes faster after inserting massive data rows?
- Original Message - From: Zhangzhigang zzgang_2...@yahoo.com.cn The mysql does not use this approach what you said which is complicated. I agree with ohan De Meersman. Umm... It's not a matter of who you agree with :-) Karen's technical detail is quite correct; I merely presented a simplified picture for easier understanding of the basics. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: 回复： 回复： 回复： Why is creating indexes faster after inserting massive data rows?
- Original Message - From: Zhangzhigang zzgang_2...@yahoo.com.cn As i known, the mysql writes the data to disk directly but does not use the Os cache when the table is updating. If it were to use the OS cache for reading but not writing, then the OS cache would be inconsistent with the underlying filesystem as soon as you wrote a block, and you'd need some complicated logic to figure out which of the two was correct. No, the MyISAM engine will simply yield to whatever the kernel/VFS wants to do with the blocks; whereas InnoDB explicitly opens the files with O_SYNC and bypasses the OS cache entirely, because it manages it's own buffer cache. If it writes to the Os cache, which leads to massive system invoking, when the table is inserted a lot of rows one by one. From the code's point of view, you simply request a read or a write. Wether or not the OS cache gets in between is entirely a matter for the kernel to decide, assuming you specified no specific options at file open time. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL Community Server 5.5.24 has been released
Dear MySQL users, MySQL 5.5.24 is a new version of the 5.5 production release of the world's most popular open source database. MySQL 5.5.24 is recommended for use on production systems. MySQL 5.5 includes several high-impact enhancements to improve the performance and scalability of the MySQL Database, taking advantage of the latest multi-CPU and multi-core hardware and operating systems. In addition, with release 5.5, InnoDB is now the default storage engine for the MySQL Database, delivering ACID transactions, referential integrity and crash recovery by default. MySQL 5.5 also provides a number of additional enhancements including: - Significantly improved performance on Windows, with various Windows specific features and improvements - Higher availability, with new semi-synchronous replication and Replication Heart Beat - Improved usability, with Improved index and table partitioning, SIGNAL/RESIGNAL support and enhanced diagnostics, including a new Performance Schema monitoring capability. For a more complete look at what's new in MySQL 5.5, please see the following resources: MySQL 5.5 is GA, Interview with Tomas Ulin: http://dev.mysql.com/tech-resources/interviews/thomas-ulin-mysql-55.html Documentation: http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html Whitepaper: What's New in MySQL 5.5: http://dev.mysql.com/why-mysql/white-papers/mysql-wp-whatsnew-mysql-55.php If you are running a MySQL production level system, we would like to direct your attention to MySQL Enterprise Edition, which includes the most comprehensive set of MySQL production, backup, monitoring, modeling, development, and administration tools so businesses can achieve the highest levels of MySQL performance, security and uptime. http://mysql.com/products/enterprise/ For information on installing MySQL 5.5.24 on new servers, please see the MySQL installation documentation at http://dev.mysql.com/doc/refman/5.5/en/installing.html For upgrading from previous MySQL releases, please see the important upgrade considerations at: http://dev.mysql.com/doc/refman/5.5/en/upgrading.html MySQL Database 5.5.24 is available in source and binary form for a number of platforms from our download pages at: http://dev.mysql.com/downloads/mysql/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://forge.mysql.com/wiki/Contributing The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.5. It may also be viewed online at: http://dev.mysql.com/doc/refman/5.5/en/news-5-5-24.html Enjoy! On behalf of the MySQL Build Team, Joerg Bruehe Changes in MySQL 5.5.24 (2012-May-7) Functionality Added or Changed * Important Change: Replication: INSERT ON DUPLICATE KEY UPDATE is now marked as unsafe for statement-based replication if the target table has more than one primary or unique key. For more information, see Section 16.1.2.3, Determination of Safe and Unsafe Statements in Binary Logging. Bugs Fixed * Security Fix: Bug #64884 was fixed. * InnoDB: Replication: When binary log statements were replayed on the slave, the Com_insert, Com_update, and Com_delete counters were incremented by BEGIN statements initiating transactions affecting InnoDB tables but not by COMMIT statements ending such transactions. This affected these statements whether they were replicated or they were run using mysqlbinlog. (Bug #12662190) * If the --bind-address option was given a host name value and the host name resolved to more than one IP address, the server failed to start. For example, with --bind-address=localhost, if localhost resolved to both 127.0.0.1 and ::1, startup failed. Now the server prefers the IPv4 address in such cases. (Bug #61713, Bug #12762885) * mysql_store_result() and mysql_use_result() are not for use with prepared statements and are not intended to be called following mysql_stmt_execute(), but failed to return an error when invoked that way in libmysqld. (Bug #62136, Bug #13738989) References: See also Bug #47485. * On Windows, mysqlslap crashed for attempts to connect using shared memory. (Bug #31173, Bug #11747181, Bug #59107, Bug #11766072) -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz Amtsgericht Muenchen: HRA 95603 Komplementaerin: ORACLE Deutschland Verwaltung B.V. Utrecht, Niederlande Geschaeftsfuehrer: Alexander van der Ven, Astrid Kepper, Val Maher -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL Community Server 5.1.63 has been released
- Original Message - From: Baron Schwartz ba...@xaprb.com Bugs Fixed * Security Fix: Bug #64884 was fixed. * Security Fix: Bug #59387 was fixed. Anyone want to elaborate on the nature or severity of the security problem? Both are private / inaccessible to me. Bug #64884 was apparently also applicable to, and fixed in 5.5.24 - would be very good to know what the vulnerabilities were, so we know wether or not they apply to us. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
回复： 回复： 回复： 回复： Why is creating indexes faster after inserting massive data rows?
Ok, thanks for your help. 发件人： Johan De Meersman vegiv...@tuxera.be 收件人： Zhangzhigang zzgang_2...@yahoo.com.cn 抄送： mysql@lists.mysql.com; Karen Abgarian a...@apple.com 发送日期： 2012年5月8日, 星期二, 下午 6:07 主题: Re: 回复： 回复： 回复： Why is creating indexes faster after inserting massive data rows? - Original Message - From: Zhangzhigang zzgang_2...@yahoo.com.cn As i known, the mysql writes the data to disk directly but does not use the Os cache when the table is updating. If it were to use the OS cache for reading but not writing, then the OS cache would be inconsistent with the underlying filesystem as soon as you wrote a block, and you'd need some complicated logic to figure out which of the two was correct. No, the MyISAM engine will simply yield to whatever the kernel/VFS wants to do with the blocks; whereas InnoDB explicitly opens the files with O_SYNC and bypasses the OS cache entirely, because it manages it's own buffer cache. If it writes to the Os cache, which leads to massive system invoking, when the table is inserted a lot of rows one by one. From the code's point of view, you simply request a read or a write. Wether or not the OS cache gets in between is entirely a matter for the kernel to decide, assuming you specified no specific options at file open time. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: One table gets locked by itself
Chech your query log for queries hitting that tables. Myisam tables dont have row level locking. There is probably a slow query somewhere. Sent from my iPhone On May 8, 2012, at 10:04 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: One table gets locked by itself
Hi Thanks, Where can i find query log for previous one,or i have to do some config in my.ini file, please let me know, Thanks Abhi -Original Message- From: Darryle [mailto:dstepli...@gmail.com] Sent: 08 May 2012 19:42 To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself Chech your query log for queries hitting that tables. Myisam tables dont have row level locking. There is probably a slow query somewhere. Sent from my iPhone On May 8, 2012, at 10:04 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: One table gets locked by itself
In your my.conf or configuration file look for an attribute that says LOG_SLOW_QUERIES , that should point to the path of your slow query log. On Tue, May 8, 2012 at 10:19 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi Thanks, Where can i find query log for previous one,or i have to do some config in my.ini file, please let me know, Thanks Abhi -Original Message- From: Darryle [mailto:dstepli...@gmail.com] Sent: 08 May 2012 19:42 To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself Chech your query log for queries hitting that tables. Myisam tables dont have row level locking. There is probably a slow query somewhere. Sent from my iPhone On May 8, 2012, at 10:04 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: One table gets locked by itself
Also,... If it happens again, do SHOW FULL PROCESSLIST while it is happening. Usually the non-Locked entry is the villain. -Original Message- From: Darryle Steplight [mailto:dstepli...@gmail.com] Sent: Tuesday, May 08, 2012 7:32 AM To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself In your my.conf or configuration file look for an attribute that says LOG_SLOW_QUERIES , that should point to the path of your slow query log. On Tue, May 8, 2012 at 10:19 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi Thanks, Where can i find query log for previous one,or i have to do some config in my.ini file, please let me know, Thanks Abhi -Original Message- From: Darryle [mailto:dstepli...@gmail.com] Sent: 08 May 2012 19:42 To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself Chech your query log for queries hitting that tables. Myisam tables dont have row level locking. There is probably a slow query somewhere. Sent from my iPhone On May 8, 2012, at 10:04 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: One table gets locked by itself
Not really. If its a deadlock , they all are victims. (E.g. mutex wait) Or a long running query (sending data) might be the guy. Claudio On May 8, 2012 7:31 PM, Rick James rja...@yahoo-inc.com wrote: Also,... If it happens again, do SHOW FULL PROCESSLIST while it is happening. Usually the non-Locked entry is the villain. -Original Message- From: Darryle Steplight [mailto:dstepli...@gmail.com] Sent: Tuesday, May 08, 2012 7:32 AM To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself In your my.conf or configuration file look for an attribute that says LOG_SLOW_QUERIES , that should point to the path of your slow query log. On Tue, May 8, 2012 at 10:19 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi Thanks, Where can i find query log for previous one,or i have to do some config in my.ini file, please let me know, Thanks Abhi -Original Message- From: Darryle [mailto:dstepli...@gmail.com] Sent: 08 May 2012 19:42 To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself Chech your query log for queries hitting that tables. Myisam tables dont have row level locking. There is probably a slow query somewhere. Sent from my iPhone On May 8, 2012, at 10:04 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: One table gets locked by itself
You might run out of file desciptors. Check your open file limits, open table limits vars and corresponding syatus values On 8 May 2012 15:05, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi
Re: One table gets locked by itself
My plan B was basically what Rick and Claudio said. Check your my.conf file for this variable LONG_QUERY_TIME . That determines how long a query will run before it's considered slow. You may need to adjust that setting, but that will just get rid of the symptom and not the problem at hand. Finding the actual query is the first step. On Tue, May 8, 2012 at 1:42 PM, nixofortune nixofort...@gmail.com wrote: You might run out of file desciptors. Check your open file limits, open table limits vars and corresponding syatus values On 8 May 2012 15:05, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: One table gets locked by itself
Few more things. You can't have a deadlock on Mylsam table. You can check status of your tables in use with: SHOW OPEN TABLES WHERE IN_USE !=0 you might check mysqld error log ad well On 8 May 2012 18:42, nixofortune nixofort...@gmail.com wrote: You might run out of file desciptors. Check your open file limits, open table limits vars and corresponding syatus values On 8 May 2012 15:05, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi
Re: One table gets locked by itself
Right, Technically not a deadlock, Practically yes if hundreds of threads are waiting on the same mutex, Like key cache one or query cache or any other mutex. Claudio On May 8, 2012 7:51 PM, nixofortune nixofort...@gmail.com wrote: Few more things. You can't have a deadlock on Mylsam table. You can check status of your tables in use with: SHOW OPEN TABLES WHERE IN_USE !=0 you might check mysqld error log ad well On 8 May 2012 18:42, nixofortune nixofort...@gmail.com wrote: You might run out of file desciptors. Check your open file limits, open table limits vars and corresponding syatus values On 8 May 2012 15:05, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi
Re: 回复： 回复： 回复： Why is creating indexes faster after inserting massive data rows?
Hi, If MyISAM tables were being written directly to disk, the MyISAM tables would be so slow that nobody would ever use them.That's the cornerstone of their performance, that the writes do not wait for the physical I/O to complete! On May 8, 2012, at 3:07 AM, Johan De Meersman wrote: - Original Message - From: Zhangzhigang zzgang_2...@yahoo.com.cn As i known, the mysql writes the data to disk directly but does not use the Os cache when the table is updating. If it were to use the OS cache for reading but not writing, then the OS cache would be inconsistent with the underlying filesystem as soon as you wrote a block, and you'd need some complicated logic to figure out which of the two was correct. No, the MyISAM engine will simply yield to whatever the kernel/VFS wants to do with the blocks; whereas InnoDB explicitly opens the files with O_SYNC and bypasses the OS cache entirely, because it manages it's own buffer cache. If it writes to the Os cache, which leads to massive system invoking, when the table is inserted a lot of rows one by one. From the code's point of view, you simply request a read or a write. Wether or not the OS cache gets in between is entirely a matter for the kernel to decide, assuming you specified no specific options at file open time. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
回复： 回复： 回复： 回复： Why is creating indexes faster after inserting massive data rows?
Oh... I thought that it uses it's own buffer cache as same as the InnoDB. I have got a mistake for this, thanks! 发件人： Karen Abgarian a...@apple.com 收件人： mysql@lists.mysql.com 发送日期： 2012年5月9日, 星期三, 上午 2:51 主题: Re: 回复： 回复： 回复： Why is creating indexes faster after inserting massive data rows? Hi, If MyISAM tables were being written directly to disk, the MyISAM tables would be so slow that nobody would ever use them. That's the cornerstone of their performance, that the writes do not wait for the physical I/O to complete! On May 8, 2012, at 3:07 AM, Johan De Meersman wrote: - Original Message - From: Zhangzhigang zzgang_2...@yahoo.com.cn As i known, the mysql writes the data to disk directly but does not use the Os cache when the table is updating. If it were to use the OS cache for reading but not writing, then the OS cache would be inconsistent with the underlying filesystem as soon as you wrote a block, and you'd need some complicated logic to figure out which of the two was correct. No, the MyISAM engine will simply yield to whatever the kernel/VFS wants to do with the blocks; whereas InnoDB explicitly opens the files with O_SYNC and bypasses the OS cache entirely, because it manages it's own buffer cache. If it writes to the Os cache, which leads to massive system invoking, when the table is inserted a lot of rows one by one. From the code's point of view, you simply request a read or a write. Wether or not the OS cache gets in between is entirely a matter for the kernel to decide, assuming you specified no specific options at file open time. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
回复： Why is creating indexes faster after inserting massive data rows?
James... * By doing all the indexes after building the table (or at least all the non-UNIQUE indexes), sort merge can be used. This technique had been highly optimized over the past half-century, and is more efficient. I have a question about sort merge: Why does it do the all sort merge? In my opinion, it just maintains the B tree and inserts one key into a B tree node which has fewer sorted keys, so it is good performance. If it only does the sort merge, the B tree data structure have to been createdseparately. it wastes some performance. Does it? 发件人： Rick James rja...@yahoo-inc.com 收件人： Johan De Meersman vegiv...@tuxera.be; Zhangzhigang zzgang_2...@yahoo.com.cn 抄送： mysql@lists.mysql.com mysql@lists.mysql.com 发送日期： 2012年5月8日, 星期二, 上午 12:35 主题: RE: Why is creating indexes faster after inserting massive data rows? * Batch INSERTs run faster than one-row-at-a-time, but this is unrelated to INDEX updating speed. * The cache size is quite important to dealing with indexing during INSERT; see http://mysql.rjweb.org/doc.php/memory * Note that mysqldump sets up for an efficient creation of indexes after loading the data. This is not practical (or necessarily efficient) when incremental INSERTing into a table. As for the original question... * Updating the index(es) for one row often involves random BTree traversals. When the index(es) are too big to be cached, this can involve disk hit(s) for each row inserted. * By doing all the indexes after building the table (or at least all the non-UNIQUE indexes), sort merge can be used. This technique had been highly optimized over the past half-century, and is more efficient. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Monday, May 07, 2012 1:29 AM To: Zhangzhigang Cc: mysql@lists.mysql.com Subject: Re: Why is creating indexes faster after inserting massive data rows? - Original Message - From: Zhangzhigang zzgang_2...@yahoo.com.cn Creating indexes after inserting massive data rows is faster than before inserting data rows. Please tell me why. Plain and simple: the indices get updated after every insert statement, whereas if you only create the index *after* the inserts, the index gets created in a single operation, which is a lot more efficient. I seem to recall that inside of a transaction (thus, InnoDB or so) the difference is markedly less; I might be wrong, though. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
