20:28 Poor SUN CPU speed for Oracle compress table (2949 Bytes) » DBA Tools

We are thinking to use Oracle compress table for data archiving via TTS, by creating a single table for every month, and then moving it to a cheaper storage. Since the history data will not be modified any more and will not be queried offten, it's possible to use compress table to save a lot of storage, and then save a lot of operation cost.

Two things we need to take into account. How much space we can save? How much faster we can convert non-compressed tables to compressed tables? We are quite happy with the space saving after we tested 5 millions rows.

TABLE_NAME               BLOCKS
------------------- -----------
CR_NOCOMPRESS             10987
CR_COMPRESS                2219

But after we tested the CTAS speed of 1gb data on SUN Netra-T12 machine, we really feel sad. It took 6 times longer compare to non-compress table.

17:37:13 SQL> CREATE TABLE CR_NOCOMPRESS NOLOGGING ...;
17:37:56 SQL> DROP TABLE CR_NOCOMPRESS; --43 seconds

17:38:01 SQL> CREATE TABLE CR_COMPRESS NOLOGGING COMPRESS ...;
17:41:13 SQL> DROP TABLE CR_COMPRESS; -- 253 seconds

Any good ideas to speed it up? Can you help to test 1gb data on various hardware platforms? Because we have too much data (several TB) to be compressed.

Compare column for different tables on different databases. (0)

Compare column for the same table on different databases. (0)

Compare column for different tables on the same database. (0)

Compare column script for tables on different databases. (0)

Leave New Comment(Current: 0)

Link: http://www.dbatools.net/experience/poor_cpu_by_sun.html

18:01 Oracle11新特性――虚拟列 (581 Bytes) » yangtingkun

打算写一系列的文章介绍11g的新特性和变化。Oracle11g增加了表的虚拟列，这个列的数据并没有存储在数据文件中，而是Oracle通过列数据的生成放到了数据字典中。看一个简单的虚拟列的例子：SQL> CREATE OR REPLACE FUNCTION F_GETTYPE(P_TYPE IN VARCHAR2) RETURN NUMBER 2 DETERMINISTIC AS 3 BEGIN 4 IF P_TYPE IN ('TABLE', 'INDEX', 'LOB', 'TABLE PARTITION', 'INDEX PARTITION', 'LOB PARTITION', 5 'TABLE SUBPARTITON', 'INDEX SUBPARTITION', 'LOB SUBPARTITION', 'CLUSTER') THEN 6 RETURN 1; 7 ELSE 8 RETURN 0; 9 END IF; 10 EN...

11:14 如何保护厕所 (4335 Bytes) » Fenng's shared items in Google Reader

《阿甘正传》导演罗伯特·泽梅基斯在1979年曾经拍过一部电影《一亲芳泽》（又名《我想握住你的手》），这个电影讲述了几个“披头士”追星族的故事，故事背景是1964年“披头士”去美国巡回演出，引发“披头士热”，然后有几个变态的歌迷疯狂追逐“披头士”，闹出了许多荒诞夸张的事情。比如，有人指着地毯说：“保罗·麦卡特尼曾经在这个地毯上走过。”一个歌迷立刻从口袋里掏出剪子，咔咔几下就把地毯剪了下来，然后装进包里。电影里这样的情景比比皆是。

我看这个电影的时候，多少还有点半信半疑，虽然以前有过歌迷枪杀列农的事件，但是像这样比较“生活化”的追星，我更相信是艺术的夸张，泽导只不过是想通过这一系列变态的故事告诉人们，人们疯狂的时候会是什么样而已。

后来，艺术的夸张在生活中再现了，比如杨丽娟，因为做了一个梦，梦见了她跟刘德华好上了，于是便开始了苦恋生涯，以至到最后闹出了悲剧。谢天谢地，她当初做梦没有梦见自己当了总理。

最近又有一个变态的新闻，说李宇春演出，玉米们都想用李宇春用过的厕所，后来酒店方面说要把这个厕所封存起来，留作纪念。以后可以变成一个旅游景点，供玉米们前来凭吊和瞻仰，这样厕所不仅可以收一部分门票，还可以提高酒店知名度。

然后我就想，怎样才能保存这间厕所呢？首先，必须保持原生态，比如李宇春用过的一切物品，都要保持原样，摆放在原来的位置；其次，温度、湿度必须恒定，想当年林彪的卧室温度一样，一定要保持在摄氏21度，湿度40%，这样，很多东西才不会发生物理性和化学性变化，几百年后，人们推开厕所那扇门，还能闻到李宇春当年留下的味道。但是做到这两点吧，其实挺难的，地球上的气候越来越恶劣，稍不留神，就会发生变化，这样的话多可惜。另外，从人类学角度来讲，把一样东西完好保存下去，而不受人的意识干扰和影响，是非常难的，你看我们有那么悠久灿烂的文化，后来不都消失了吗。这个“厕所遗迹”该怎么保存，我觉得当务之急是成立一个“李宇春厕所遗迹保护小组”，由生物学、化学、物理学、人文学、民俗学、历史学、考古学专家组成，先研究出一些方案来，然后实施，不能等遗迹遭到破坏后才想到去保护，那样什么都晚了。

在这个保护小组成立之前，我有两个好主意，叫做上天有路，入地有门，皆可达到保护之目的。

第一，大家都知道秦始皇陵，据说里面保护的很完好，至今没有开掘，就是因为怕打开之后有些东西保护不住，给毁了。所以，秦始皇老师一直在里面呆着不出来。我建议把这个酒店给埋起来，这个厕所的四周倒满水银，以防腐化。埋上后的酒店就变成了一座山，山上可以种玉米。几千年后，这地方就是一个传奇，会有人拍电影，比如《古今大战玉米情》，当然，它也是一个旅游景点。某个玉米的后代面对这座高山发出感慨，我奶奶的奶奶的奶奶当年就是玉米。会有很多专家天天琢磨怎么把这个厕所挖开，又会有很多人站出来反对，然后有更多专家开始研究这个厕所，有“自传说”和“索隐说”等几种说法，大家打得不可开交。这样丰富了我们的后现代文化。

第二，大家都知道，我们的嫦娥已经奔月，我看要不把这座酒店直接装到飞船上，发到月球上。在那上面，没有空气，什么都不会变质，等将来地球月球之间通航，会有一大批玉米往返于地月之间，也算是为人类征服太空作出了贡献。并且，我们用事实向全世界证明，中国人真正做到了和平利用太空，而不是用于称霸。

11:04 Tips and tricks: What are access privileges and how can I set them? (3315 Bytes) » Red Hat Magazine

Contributed by Celeste O’Connor

Privileges define the access level of the user, or what operations the user is allowed to do and the sections of the Command Center which the user is allowed to see. Every person entry in Command Center has some sort of privileges set. There are two major categories of privileges: administrator and user. Within the user privileges, there are three different access levels: monitor only, role-based, and view configuration. All these are set in the user’s profile.

Below are all the privileges, the administrator has:

View monitoring activities
Create reports
Manage monitoring configuration (add and modify hosts, host groups, checks, and URLs)
Manage people and other administration configuration (add and modify people, roles, contact groups, and methods)
Handle notification activities (set advanced notifications, check suites, and company defaults)

Below are all the privileges, the user has:

Monitor Only
- View monitoring activities
- Create reports
Role-based (Monitor Only)
- View monitoring activities of delegated areas
- Create reports on delegated entities
View Configuration
- View monitoring activities
- Create reports
- View monitoring configuration information

Watch a flash demo that outlines the process.

Red Hat’s customer service and support teams receive technical support questions from users all over the world. Red Hat technicians add the questions and answers to Red Hat Knowledgebase on a daily basis. Access to Red Hat Knowledgebase is free. Every month, Red Hat Magazine offers a preview into the Red Hat Knowledgebase by highlighting some of the most recent entries. The information provided in this article is for your information only. The origin of this information may be internal or external to Red Hat. While Red Hat attempts to verify the validity of this information before it is posted, Red Hat makes no express or implied claims to its validity.

09:30 Linux 上常见的 IO 基准测试工具比较 (3991 Bytes) » DBA notes

©作者：Fenng 发布在 dbanotes.net

经常要对一些新存储系统进行 I/O Benchmark 测试，每次测试又有可能针对不同的目的，但基本也都是围绕数据库转悠，心血来潮，对几个常见的工具做个比较。

(点击查看全图)

要强调的几点: ORION --Oracle I/O Numbers Calibration Tool 还是比较全面的针对数据库应用的 IO 测试工具。现在 Oracle 发布了不少平台的移植版本。该工具也比较好用。

数据库应用必需要考虑异步 I/O 的因素，否则结果会有很大偏差，当然如果只测试存储能力的话，到可以忽略。AIO 压力测试可以考虑以下 AIO-Stress

Unix 命令 dd 虽然很土，但还是一个测试 I/O 的基本手段和方法.有的时候即使没别的工具只用它也能发现很多问题。另外一个需要注意的就是字符设备和块设备的差别啦。更新: 就当我说得是 GNU dd 吧，谢谢下面留言的朋友。

有些工具因为用过很久了，记忆难免有问题，表格中会有误导。仅供参考。今天太累，等有空继续补充内容。

--EOF--

BTW: 我收集的关于 Benchmark 的书签内容。

相关文章|Related Articles

评论数量(2)|Add Comments

本文网址：http://www.dbanotes.net/database/linux_io_benchmark_tools_compare.html

05:49 Hacking to make ALTER TABLE online for certain changes (20683 Bytes) » MySQL Performance Blog

Suppose you want to remove auto_increment from 100G table. No matter if it's InnoDB or MyISAM, you'd usually ALTER TABLE `huge_table` CHANGE `id` `id` int(6) NOT NULL and then wait hours for table rebuild to complete. If you're unlucky i.e. you have a lot of indexes and not too much RAM - you could end up waiting days. If you want to make this happen quick - there's another way. Not documented, but works well with both - InnoDB and MyISAM.

Now that more and more folks hit the InnoDB auto-inc scalability issue with MySQL 5.0 and older versions, employing other techniques to maintain the PK auto incremental becomes more of an issue. One of the steps here is to remove current PK auto_incremental from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and huge tables take time to rebuild.

Disclaimer: try this at your own risk. It worked for me, it may work for you too, but always have a backup before doing that kind of stuff, as it is not the way MySQL would advice you to do it and we can't guarantee it will work well for you either.

So, in a nutshell, all you have to do is create another table with desired table structure and switch .frm table definition files. For safety, I'd recommend to flush tables with read lock while switching .frm files. When and how it works:

auto_increment (removing). Let's have a simple table with auto_increment we want to get rid of:

PLAIN TEXT

SQL:

CREATE TABLE `huge_table` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`text` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB

To remove auto_increment, we (1) create table with the same layout but without auto_increment, (2) flush tables with read lock, (3) swap .frm files while keeping mysql suspended and (4) unlock the tables afterwards:

PLAIN TEXT

SQL:

-- 1.
mysql> CREATE TABLE `huge_table_new` (
-> `id` int(6) NOT NULL,
-> `text` text NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)
-- 2.
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
-- 3.
mysql> ^Z
[1]+ Stopped mysql test
# mv huge_table.frm huge_table_old.frm && mv huge_table_new.frm huge_table.frm && mv huge_table_old.frm huge_table_new.frm
# fg
mysql test
-- 4.
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE TABLE `huge_table`\G
*************************** 1. row ***************************
TABLE: huge_table
CREATE TABLE: CREATE TABLE `huge_table` (
`id` int(6) NOT NULL,
`text` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row IN SET (0.00 sec)
mysql> INSERT INTO `huge_table` (text) VALUES ('test');
ERROR 1062 (23000): Duplicate entry '0' FOR KEY 1

Unfortunately, adding auto_increment does not work that way.

Enum values (add and remove). Enumerated values are added and removed the same way that auto_increment is removed. I've been a bit surprised, that removing value from enum() works as good as adding it - rows that have incorrect values are just returned as empty. But I suppose this does violate mysql data file structure, so be really careful with that one.

Default values. MySQL rebuilds table even if we only want to change the default value for new records so this may save one from a lot of trouble.

Table comment. I'm pretty sure that would work for changing table comment as well, however - changing a comment with a help of ALTER TABLE does not rebuild the table, so we better use the documented method for that.

What I'm surprised about is that changing a comment does not to require table to be rebuilt, while things like removing auto_increment or changing a default value still do even though this information is stored in table definition file.

If you'll ever try this, please leave a comment if it did work for you. Maybe you have discovered some new things to alter that way?

Entry posted by Aurimas | No comment

Add to: | | | |

04:42 Ext3 和 ext4 文件系统在线调整大小内幕 (605 Bytes) » Fenng's shared items in Google Reader

在 Linux 平台下很多用户都在使用 ext3 文件系统，主要原因是其可靠性，健壮性和兼容性。在 Linux 2.6 内核中已经包括了适合在服务器环境中使用的很多特性，譬如目录索引，块预留，在线调整大小。为支持更大容量的文件系统，其下一个版本 ext4 也正处于开发中。本文主要介绍当前 ext3 和 ext4 文件系统中在线调整大小的工作机制，以及如何使用 meta block group 来扩展其大小。

02:18 My Day in Office 2008 for Mac (527 Bytes) » Fenng's shared items in Google Reader

My Day 是 Office 2008 中新增的一个小巧强大的 GTD 工具，和 Entourage 2008 集成得非常好。个人感觉，实在是 Mac 上最好的 GTD 工具。推荐！！！

[ read more on livid.cn ]

01:43 The Pound program is a reverse proxy, load balancer and HTTPS front-end for Web server(s). [del.icio.us] (208 Bytes) » 车东[Blog^2]

Pound was developed to enable distributing the load among several Web-servers and to allow for a convenient SSL wrapper for those Web servers that do not offer it natively. Pound is distributed under the GPL.

20:57 年会周边酒店介绍 » Fenng's shared items in Google Reader