21:03 我的天使在成长 (2123 Bytes) » Oracle Life

©作者：eygle 发布在 eygle.com

国庆的这段时间，已经能够感觉Baby开始长大。

不肯再乖乖的躺在床上，嘴里开始学着发出各种声音，小脑袋能够跟着爸爸转，眼睛里满是好奇的纯真。
我的天使在成长。

他已经知道用微笑来表达自己的喜悦：

我的天使在成长
慢慢的微笑轻轻的舞蹈

我的天使在成长
今天在怀抱明天也许就能奔跑

我的天使...在成长...

相关文章|Related Articles

评论数量(0)|Add Comments

本文网址：http://www.eygle.com/archives/2007/10/baby_bigger.html

18:40 session allocation latch问题 (9160 Bytes) » 人生就是如此

昨天晚上一回家上msn，一朋友就抛了个问题过来：

我的系统严重的latch free等待，系统运行极度缓慢，几乎hang了。系统出帐出不了……

latch free中,最为严重的sleep的是session allocation

LATCH# NAME GETS MISSES SLEEPS ---------- -------------------- ---------- ---------- ----------

row cache objects 7309905930 1076355567 24799351 156

shared pool 1.2642E+10 168368806 28928269 3

process allocation 102171945 9233300 29316107 157

library cache 2.9539E+10 1234502998 87366744

session allocation 3091150764 514908537 244535123

没见过那个session allocation

这个为什么会是最高的,比其他的高一个数量级.

看见这个现象，我的第一反应是应用不断地在连接数据库，或者有并行查询。

让他查询 v$session.logon_time 和 listener.log 都没有异常

trace session 说没有任何 trc 文件产生，一trace session就没了。

然后查询 v$px_session 果然不断有创建和产生

由于要修改初始化参数需要停机，显然不现实，于是查看是否有表或者索引的 parallel degree 大于1

果然有一个索引的 parallel degree 是15。

嘱咐其小心修改（会导致sql 重新parse），一改果然系统速度恢复正常。

我没追问系统以前是否正常，但我估计可能是最近有人创建了一个索引，并且创建的时候并行度使用了15（多个进程并行创建），创建之后就没再注意了。结果月初出帐的时候就遇到麻烦了。

-- session allocation 这个latch我也从来没有看见过，朋友说metalink搜索过也没什么说明。但根据字面描述我直接就定位到连接的问题或者并行查询问题，然后其实诊断方向就很明确，最后以修改索引并行度解决问题。看起来整个过程非常简单…… 但是不是大家都会这么直接呢？

14:01 UNION vs UNION ALL Performance (18173 Bytes) » MySQL Performance Blog

When I was comparing performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION in my benchmarks, and he was right. Numbers would be different but it should not change general point of having optimization of moving LIMIT inside of union clause being cool thing.

But So is UNION ALL indeed faster than UNION DISTINCT (the UNION is shortcut for UNION DISTINCT) ?

Indeed it is. I did not have the same data as I used for the other test but I created similar test case - table with separate indexes on "a" and "b" columns with cardinality of 100, having about 40.000.000 of rows

PLAIN TEXT

SQL:

SELECT * FROM test.abc WHERE i=5 union SELECT * FROM test.abc WHERE j=5

This original query was taking about 22 seconds.

As I modified it:

PLAIN TEXT

SQL:

SELECT * FROM test.abc WHERE i=5 union ALL SELECT * FROM test.abc WHERE j=5 AND i!=5

The query time dropped to about 6 seconds which is 3.5 times faster - quite considerable improvement.

As you can notice I added "i!=5" clause - this is what allows us to ensure we do not have duplicate rows in result set matching both conditions and so result will be same as query with "i=5 or j=5" where clause.

I also tried this original query (which uses index merge method in MySQL 5.0):

PLAIN TEXT

SQL:

SELECT * FROM test.abc WHERE i=5 OR j=5

Such query takes 4 seconds so if you do not need to trick with order by and limit using index merge is faster than UNION as it indeed should be.

So why UNION ALL is faster than UNION DISTINCT ?

The first informed guess would be - because UNION ALL does not need to use temporary table to store result set, however this is not correct - both UNION ALL and UNION distinct use temporary table for result generation. Perhaps one more thing for Optimizer Team to look into.

Interesting enough the fact UNION and UNION ALL require temporary table can only be seen in SHOW STATUS - EXPLAIN does not want to tell you this shameful fact:

PLAIN TEXT

SQL:

mysql> EXPLAIN (SELECT * FROM test.abc WHERE i=5) union ALL (SELECT * FROM test.abc WHERE j=5 AND i!=5) \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
TABLE: abc
type: ref
possible_keys: i
KEY: i
key_len: 5
ref: const
rows: 348570
Extra: USING WHERE
*************************** 2. row ***************************
id: 2
select_type: UNION
TABLE: abc
type: ref
possible_keys: i,j
KEY: j
key_len: 5
ref: const
rows: 349169
Extra: USING WHERE
*************************** 3. row ***************************
id: NULL
select_type: UNION RESULT
TABLE: <union1,2>
type: ALL
possible_keys: NULL
KEY: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
3 rows IN SET (0.00 sec)

In fact EXPLAIN output is the same for UNION and UNION ALL (which is too bad as execution for them is obviously different).

The difference in execution speed comes from the fact UNION requires internal temporary table with index (to skip duplicate rows) while UNION ALL will create table without such index.

This also explains why difference becomes larger when on disk table is required (as in this case) - Hash indexes used by MEMORY table are very efficient and do not give so much overhead.

Entry posted by peter | No comment

Add to: | | | |

10:35 What did we want Interns to do (3744 Bytes) » MySQL Performance Blog

About one month ago we posted a call for interns to work with us on various MySQL Performance related things.

We got good number of replies and a lot of people wondered what tasks exactly we could offer as surely this sort of involvement should be fun and should teach you something.

Really we did not have a list - Cool opportunities and
ideas constantly pop up plus we expect you to bring your ideas on what
you would be interested to work on and we will see if there is an
interest for us to mentor and sponsor your work.

Generally the tasks we see Interns working on are either Open Source
projects or research which would be published so besides minor
financial compensation there is publicity and education components.

Here are few things we have in mind:

MySQL Benchmarks we have a lot of things we wanted to take a look but never found a time. These are hardware, operation systems, file systems, mysql storage engines, settings, MySQL versions, etc
MySQL Patches - there are a lot of little cool things you can do to improve performance monitoring in MySQL
Innodb Performance Experiments - There are a lot of hard coded values which Heikki picked on his laptop 10 years ago, not all of them are still optimal now. We'd like to see if we can pick better values for some workloads.
Web Application Database Benchmark - we would like to get few benchmarks
which present typical web LAMP application workloads
Full Text Search Benchmark - we would like to get
comparison for MySQL build in full text search, Sphinx, Lucene, PostgreSQL Searches.

Entry posted by peter | No comment

Add to: | | | |

05:31 Will open source change Canada? Democratizing sustainable housing in Canada (part 2) (5926 Bytes) » Red Hat Magazine

Will democratizing sustainable housing be enough to change Canada? It’s too early to tell, but there’s a start. Open source can make sustainable designs available. Nobody owns it, everybody can use it, and anybody can improve it. The Now House is one sustainable housing design project created by one small team. What would happen if one hundred teams created projects like this?

Toronto-based firm Work Worth Doing created Now House™, a project whose mission is to turn a 60-year-old WWII house into a near zero energy home—one that produces as much energy as it uses. The idea for this one house, coupled with the benefits of open source, has the potential to affect millions of homes and demonstrate how open source thinking can be applied to one of the biggest problems facing the world today. Canada shoulders its own share of this burden and is currently way off-track from meeting its Kyoto Protocol goals.

Over the next ten years, how could open source thinking continue to play a role in the Now House project?

Figure 1: Wartime housing rollout. Source: Now House research

The process for retrofitting one house to become a near zero energy home, will serve as the basis for future phases of the project.

The homes within a wartime housing community are similar in structure, making the national rollout of the Now House model possible. There are an estimated one million wartime homes across Canada (see figure 1). As the project gains momentum, the scale will necessitate the participation of many different players located throughout the nation. Creating an open source platform allows multiple partners–builders, governments and homeowners–to contribute input and provide resources.

Figure 2: What if all existing houses used the Now House model?

Sources for the data in figure 2:

About 12 million occupied homes in 2004. Source: “Population and dwelling counts, for Canada provinces and territories, 2006 and 2001 censuses — 100% data,” Statistics Canada.
57% are single detached homes, equaling 6.84 million homes. Source: “Improving Energy Performance in Canada” page 17, Figure 3-1.
GHG emission reduction per year in tons: The residential sector produces 77 mega tons of GHG emissions. Source: “Improving Energy Performance in Canada” page 17.
57% of the residential sector (single detached homes) produces 43.89 mega tons of GHG emissions. 43.89 mega tons of GHG emissions x 60% estimated Now House GHG reduction for detached housing.

In the hypothetical rollout to all existing houses, the retrofitting teams could apply their wartime housing knowledge to other single-detached homes (see figure 2). By creating a platform for participation, there’s an opportunity for a community of collaborators to build on past work because of open source knowledge. Could this knowledge empower Now House participants to create a sustainable, profitable marketplace while positively changing Canada’s GHG emissions?

Canadians may want to spend more time saving the environment given their current ecological footprint, which would require four planet Earths to sustain if everyone on the planet lived like them. It takes 7.6 global hectares of resources to support each Canadian according to the latest World Wildlife Fund’s Living Planet Report (see figure 3). An open source platform for sustainable housing could pull groups from all over Canada and provide them with vibrant connections and resources through which they can share ideas, best practices and make a living through creating near zero energy homes. The Now House team thinks that small changes can equal big results. Small changes and collaboration on a national scale through an open source platform is one way to do just that.

Figure 3: Ecological footprint. Source: WWF International. 2006 Living Planet Report, 3.

02:18 5 Useful Links You Should Check Out Today (2007-10-05) (3675 Bytes) » Eddie Awad's Blog

Google Advanced Operators Cheat Sheet

A handy cheat sheet that you can print and hang over your desk.
SEOUC Presentations

Useful presentations from the SouthEastern Oracle Users Conference.
XML DB FAQ

This is the official XML DB FAQ thread on forums.oracle.com.
FAQ: Ruby on Rails with Oracle

Provides basic information about connecting to Oracle, installation, and creating Oracle Database XE + RoR applications.
SQL University.net

Free extracts of SQL University.net courses covering a variety of topics.