Recently I had a customer ask me about loading two huge files into InnoDB with LOAD DATA INFILE. The goal was to load this data on many servers without putting it into the binary log. While this is generally a fast way to load data (especially if you disable unique key checks and foreign key checks), I recommended against this. There are several problems with the very large transaction caused by the single statement. We didn't want to split the file into pieces for the load for various reasons. However, I found a way to load the single file in chunks as though it were many small files, which avoided splitting the file and let us load with many transactions instead of one huge transaction.

The smaller file is 4.1GB and has 260M lines in it; each row is just two bigints. The bigger file was about 20GB and had wider rows with textual data and about 60M lines (as I recall).

When InnoDB loads the file, it creates one big transaction with a lot of undo log entries. This has a lot of costs. To name a few:

• the big LOAD DATA INFILE clogs the binary log and slows replication down. If the load takes 4 hours on the master, it will cause the slave to fall 4 hours behind.
• lots of undo log entries collect in the tablespace. Not only from the load -- but from other transactions' changes too; the purge thread cannot purge them, so everything gets bloated and slow. Even simple SELECT queries might have to scan through lots of obsolete, but not-yet-purged, row versions. Later, the purge thread will have to clean these up. This is how you make InnoDB behave like PostgreSQL
• If the undo log space grows really big, it won't fit in the buffer pool and InnoDB essentially starts swapping between its buffer pool and the tablespace on disk.

Most seriously, if something should happen and the load needs to roll back, it will take a Very Long Time to do -- I hate to think how long. I'm sure it would be faster to just shut everything down and re-clone the machine from another, which takes about 10 or 12 hours. InnoDB is not optimized for rollbacks, it's optimized for transactions that succeed and commit. Rollback can take an order of magnitude longer to do.

For that reason, we decided to load the file in chunks of a million rows each. (InnoDB internally does operations such as ALTER TABLE in 10k row chunks, by the way; I chose 1M because the rows were small). But how to do this without splitting the file? The answer lies in the Unix fifo. I created a script that reads lines out of the huge file and prints them to a fifo. Then we could use LOAD DATA INFILE on the fifo. Every million lines, the script prints an EOF character to the fifo, closes it and removes it, then re-creates it and keeps printing more lines. If you 'cat' the fifo file, you get a million lines at a time from it. The code is pretty simple and I've included it in Maatkit just for fun. (It's unreleased as of yet, but you can get it with the following command: "wget http://www.maatkit.org/trunk/fifo").

So how did it work? Did it speed up the load?

Not appreciably. There actually was a tiny speedup, but it's statistically insignificant IMO. I tested this first on an otherwise idle machine with the same hardware as the production machines. First, I did it in one big 4.1GB transaction, then I did it 1 million rows at a time. Here's the CREATE TABLE:

Here's the result of loading the entire 4GB file in one chunk:

While this ran, I captured vmstat output every 5 seconds and logged it to a file; I also captured the output of "mysqladmin ext -ri5 | grep Handler_write" and logged that to a file.

To load the file in chunks, I split my screen session in two and then ran (approximately -- edited for clarity) the following in one terminal:

And this in the other terminal:

After I was done, I ran a quick Perl script on the vmstat and mysqladmin log files to grab out the disk activity and rows-per-second to see what the progress was. Here are some graphs. This one is the rows per second from mysqladmin, and the blocks written out per second from vmstat.

And this one is the bytes/sec from Cacti running against this machine. This is only the bytes out per second; for some reason Cacti didn't seem to be capturing the bytes in per second.

You can see how the curves are roughly logarithmic, which is what you should expect for B-Tree indexes. The two curves on the Cacti graph actually show both files being loaded. It might seem counter-intuitive, but the second (smaller) curve is actually the larger file. It has fewer rows and that's why it causes less I/O overall.

I also used 'time' to run the Perl fifo script, and it used a few minutes of CPU time during the loads. So not very much at all.

Some interesting things to note: the load was probably mostly CPU-bound. vmstat showed from 1% to 3% I/O wait during this time. (I didn't think to use iostat to see how much the device was actually used, so this isn't a scientific measurement of how much the load was really waiting for I/O). The single-file load showed about 1 or 2 percent higher I/O wait, and you can see the single-file load uses more blocks per row; I can only speculate that this is the undo log entries being written to disk. (Peter arrived at the same guess independently.)

Unfortunately I didn't think to log the "cool-down period" after the load ended. It would be fun to see that. Cacti seemed to show no cool-down period -- as soon as the load was done it looked like things went back to normal. I suspect that's not completely true, since the buffer pool must have been overly full with this table's data.

Next time I do something like this I'll try smaller chunks, such as 10k rows; and I'll try to collect more stats. It would also be interesting to try this on an I/O-bound server and see what the performance impact is, especially on other transactions running at the same time.

Entry posted by Baron Schwartz | 2 comments

Add to: | | | |

This article is a follow-up to Red Hat Enterprise Linux 5.1 utilizes nested paging on AMD Barcelona Processor to improve performance of virtualized guests.

With new hardware releases, customers are faced with situations in which they want to take advantage of increased speeds but are forced to stay on older hardware because their operating environments are not supported on the newer hardware. Virtualizing their operating environment helps them get past this issue. Virtualization also helps them:

• Consolidate hardware to
• improve utilization
• reduce floor space requirements
• reduce power consumption
• Take advantage of hardware speed up without having to upgrade the software environment
• Reduce downtime for upgrades
• Create development and test environments

RHEL 5 virtualization lets customers virtualize their existing systems and take advantage of the benefits mentioned above.

There are two modes to virtualize servers:

• Para-virtualized servers. In this mode, the virtualized server has direct access to the hardware/hypervisor and delivers performance close to bare metal. Systems running Red Hat Enterprise Linux 4.5 and newer can be deployed as para-virtualized guests.
• Fully-virtualized servers. In this mode, the virtualized server interacts with the hypervisor through a hardware abstraction layer. The hypervisor presents its hardware as generic hardware through the abstraction layer so most operating systems can be run in a virtual mode on it. However, all the I/O, network, and memory requests from the guest have to be translated by the hypervisor. This translation results in very poor performance by systems deployed as fully virtualized guests.

The I/O and network performance on fully virtualized guests can be improved by implementing para-virtualized drivers within these guests.

But memory access can still be an issue, particularly with process-based applications where processes need to modify virtual memory. Specifically, the guest operating system (guest OS) in the virtual machine creates page tables that translate virtual memory addresses to guest pseudo-physical addresses, which means that older CPUs cannot directly use them. To deal with that limitation, the hypervisor has to create so-called shadow page tables, which translate the same virtual memory addresses to real physical addresses. Maintaining these shadow page tables can cause performance and scalability problems with certain workloads. One particular cause of performance issues is the fact that the hypervisor needs to intercept each page table write by the guest OS in order to keep the guest page tables and the shadow page tables in sync.

The AMD Barcelona processor has a feature called Rapid Virtualization Indexing (RVI) which allows the processor to directly use the virtual to pseudo-physical address page tables created by the guest OS. This works because the CPU translates these pseudo-physical addresses to real machine physical addresses using a second set of page tables, which define this translation for each virtual machine. Because the CPU can do both of the memory address translations, no shadow page tables are required and the guest OS can alter its page tables directly, without trapping to the hypervisor. Context switches in the guest OS also benefit from RVI technology because the shadow page table gets flushed with most context switches on the guest and there is a cost incurred to re-populate it.

All systems running Red Hat Enterprise Linux 4.4 or older have to be run as fully virtualized guests, as do all other operating systems (e.g. Windows, Solaris, etc.).

A series of tests on RVI were carried out on an HP DL585 system using an OLTP workload in an Oracle database. Oracle was chosen as the database for the testing because it is a process-based workload, and it is also a widely used database. The testing was carried out with 8 and 16 CPUs to understand the effects of vertical scaling on fully virtualized guests and the benefits of RVI.

Hardware used for the testing

System: HP DL 585
Memory: 32 G
CPUs: 16 AMD Barcelona @ 2.3 GHz

Test results from 8 CPU testing

For the purpose of comparison, the transactions per minute generated with 20 users was baselined at 100, and all other numbers are relative to that number. The table and the graph above show the remarkable advantage that PV drivers and RVI provide to a fully virtualized guest. Line 2 in the chart, which represents the FV guests without RVI or PV drivers, shows a huge drop-off in the transactions per minute relative to line 1, which shows the baseline of transactions per minute on Dom0. By turning on the RVI feature, some of the performance is regained. But without the PV drivers, the performance remains way below the mark, as shown on line 3. Line 4 shows that by adding PV drivers without RVI, the performance remains way off. Finally line 5 shows that by using RVI and PV drivers, the performance of the workload gets within 80% of Dom0.

Test Results from 16 CPU testing

The results from the 16 CPU testing shows similar trends to the 8 CPU testing, but the delta gets wider as the user count is increased when RVI and PV drivers are not used.

Figure 3 below shows the comparison between the 8 and 16 CPU runs. All the numbers are relative to the 8 CPU numbers without PV drivers and RVI, which has been baselined at 1. The figure shows that as the guest size increases, the performance of the FV guest without RVI and PV drivers decreases. With RVI and PV drivers, performance inside the guest comes close to 80% of the performance on Dom0.

Comparison between 8 CPU and 16 CPU FV guests

Conclusion

From the results it is clear that when a process-based workload is run in a fully virtualized guest, performance takes a big hit due to the way the TLB information is maintained and the way I/O works inside the guest. But by adding PV drivers on an AMD Barcelona based system which uses RVI, performance aligns with Dom0 performance. With this feature, AMD lets customers take advantage of hardware speed-up without having to upgrade their software environments by letting them run their systems as fully virtualized guests on the Barcelona-based systems.

Shared by Fenng
这个版本字体终于小了一点 :)

作为一个活跃的博客（Blog）作者，三年多以来，我一直坚持更新一个专业博客《麦田的读书生活》。在这份基本上保持每周更新的博客上，我一直以“自媒体”的模式，几乎全部文章都围绕互联网网站运营，尤其是社区和电子商务方面内容。这份博客给我带来了一些所谓“名声”和快乐，带来了非常高的访问量——但现在，我却在认真思考一个问题：是不是应该关了这个博客。

因为，博客（Blog）已经过时了。

尘埃落定的博客。博客一诞生就存在两种属性，“自媒体”属性和“交互”属性。以国内来说，娱乐界的徐静蕾，文化界的王小峰、和菜头，IT界的keso等等，他们的博客全都是“自媒体”；而散落在qzone，百度空间千万普通人的博客日记，他们的博客全都是“交互”属性。这两类属性的博客无论从传播模式还是“功效”上来说，截然不同。在中文博客发展历史上，曾经出现过两类博客谁算“正宗”的争论，但现在看来，这是一个“伪问题”。两类博客都是博客。此外，中文博客网站排位之争，也尘埃落定——从“自媒体博客”来说，新浪做到了老大；从“交互博客”来说，qzone做到了老大。新浪和QQ，就是中文网络的——博客双雄。War is over。

现在反思这场时代性的“战争”，有一些比较有趣的结论：

1，  所有挟持“博客”概念（应用）的新兴网站，即使获得了投资，也都败于老牌网站的品牌优势（新浪）和用户优势（QQ）。即：概念（应用）+资本＜品牌或用户

2，  因为我自己的工作经历（曾就职于某博客网站），所以我认为，新兴网站其实有过可能赢得老牌网站的机会“窗口期”；但在关键时刻，新兴网站既没有建立起“品牌”，又没有建立起“用户”，错失良机

3，  “博客”这个当时的新兴概念，自身存在的上述“双重属性”，也客观上使得竞争的优势天平，偏向老牌网站——博客的“自媒体”属性，更有利于已经有“品牌”的网站，所以新浪会赢；博客的“交互”属性，更有利于已经有“用户”的网站，所以QQ会赢

4，  但最具有戏剧性的是，恰恰也是因为博客自身的“双重属性”，使得“博客”只是互联网的过渡性产品（应用）——“自媒体”属性，不如“网络媒体（门户）”有效；“交互”属性，对低端用户的要求又过高。

5，  其实我想说的是，博客，就是一个先天就“不完善”的应用；博客，就是一个缺乏商业价值的应用。因此无论“自媒体”，还是“交互”，哪条路走起来都很难，而且商业化方面，效率都不高。

6，  诸位，为什么这么多人，这么多年，做博客做的这么累；诸位，因为我们在一个几乎不可能成功的战场上，试图完成不可能之任务。

7，“博客过时了”并不是说个人以“博客”或“日记”这种方式，持续写作、表达的欲望会“过时”；每个人都有文字表达的欲望，这种欲望永远不会“过时”。只是在“博客时代”，这种欲望由“博客”工具来实现；但在“SNS时代”，这种欲望由sns网站“日记”工具来实现。事实上，几乎所有sns网站都有“日记”功能区块，即承担这种文字表达欲望。传统的博客应用，会成为sns应用的一个子集。

SNS是“博客终结者”。最近facebook火了，很多人看到的是“校园”或“白领”。我认为那只是表象。我曾经写过《巴别塔的倒掉：Facebook和Google之争的真相》，提到facebook的一些本质特性。但我现在认为，以Facebook为代表的SNS的真正力量，是“博客终结者”——SNS应用在“自媒体”和“交互”两方面，都比博客应用更具有效率。博客完成了网民的“主体性”，SNS将完成网民的“主体间性”。详细分析不展开了。

集中的门户——分散的博客——集中的SNS，互联网的发展就是这样不断螺旋上升、前进。

（完）

后记：

上文的思考仅是我对网站发展模式和脉络的思考，纯粹个人观点，不针对任何网站，尤其是博客网站。没准我的思考是错的呢，所以现在做着博客的哥们，不要介意。：）

另外透露一下，我确实在考虑永久关闭“麦田的读书生活”博客，而只在3个SNS类型网站交流，朋友们可以去那里找我：

蚂蚁网（http://www.mayi.com/people/41/）

5g（http://maitian.5gsns.com/）

Techweb同事录（http://home.techweb.com.cn/756）

©作者：eygle 发布在 eygle.com

今天有一个客户在升级数据库到10g之后遇到了如下错误：

ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01406: fetched column value was truncated
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 20971
ORA-1092 signalled during: ALTER DATABASE OPEN...

一般来说，见到bootstrap错误都是很严重的故障，bootstrap过程失败数据库肯定就无法打开。很多时候bootstrap$表损坏也会导致bootstrap失败。

以上一系列错误说明在升级的过程中出现问题，升级是不完全的，从而导致数据库无法bootstrap。
可以尝试从备份中恢复数据库，再次进行升级。

记以录之。

-The End-

相关文章|Related Articles

• 这是一个Oracle普及的时代
  
• 关于HDS的高端存储设备USPV
  
• 成功恢复案例:打开断电崩溃数据库
  
• 恢复8.1.6的数据库 勿忘svrmgrl
  
• 用Rman的Plus archvielog选项简化数据库备份
  

评论数量(0)|Add Comments

本文网址：http://www.eygle.com/archives/2008/07/ora_00704_bootstrap_failed.html