Well these days we see a lot of post for and against (more, more) using of MySQL and DRBD as a high availability practice.

I personally think DRBD has its place but there are far more cases when other techniques would work much better for variety of reasons.
First let me start with Florian’s comments on the issue as I think they are most interested ones.

First lets get to the point what we’re comparing here - it is mainly DRBD to MySQL Replication based techniques (lets leave MySQL Cluster and Continnuent alone for a while as these are a bit from different league). It is not the question if DRBD is better than SAN - it offers more independence compared to SAN and in my view surely superior from HA point of view but this is not the point of comparison.

“Failback could destroy the original master too”, however, is plain false. DRBD won’t “destroy the original master” any more than it already was if the filesystem on top of DRBD was fried beforehand.

Let us again compare MySQL Replication to DRBD in this case - in both cases due to some reasons you can have systems to run out of sync and have conflicting updates applied to them. With DRBD you have the choice of killing one of the nodes and re-syncing from another one while with MySQL Replication you can use Maatkit to merge the changes after all, also you can review binary logs to see which updates were applied to different nodes.

Transaction log replay, yes. But fsck? These days this amounts to running a journal replay. Takes under a second in most circumstance

I would put it 10 seconds but it does not matter. The transactional log reply is likely to take much longer than that. This is very bad property of DRBD - besides the well understood overhead of committing on both nodes instead of one you also meat the tough choice - you’ve got to pick either long recovery time or further degraded performance. In the large databases I run in production relaying on MySQL Replication for HA I often have 15+ minutes reply of Innodb Transactional log which would be a huge bummer with DRBD.

I would also say this implies hidden danger - the time it will take your database to do transactional log recovery is invisible until you get a crash, meaning if your production database size growths load changes or you happen to have failure during activity of certain kind you might have it taking much longer when expected. recovery time depends on a lot of variables.

The side question about it is of course the fact you have to be picky about storage engines you’re using - DRBD does not work with MyISAM (check required) so you need to have processes to ensure your application does not uses this storage engine which may be hard to guaranty in many environments when development has too much autonomy.

I must note in this aspect however DRBD goes on par with MySQL Statement Based Replication - it is also well too easy to use MySQL features which break replication.

The failover node is a hot standby, it’s just not a running slave node from the database’s standpoint. And, nothing stops you from running two databases on two servers on two DRBD devices laid out in a “criss-cross” fashion, converging on one node in case of node failure.

This actually goes to two topics. First - hot vs cold. If you’re using decent hardware and care about performance you use O_DIRECT with Innodb which makes it to bypass file cache. If you have it DRBD slave will be fully cold. But lets assume you’re ready to pay for yet another penalty DRBD introduce and do not use this option wast memory and CPU cycles on double caching. Even in this case DRBD slave node can’t be called hot because write load often does not touches the same data as read load. Here is simple example - assume you’re inserting the data at the same time as running reporting queries on last month. All last month will be hot on the slave which is doing reads but only last few hours will be hot on the standby box.

Running two instances on the server allows to reduce hardware waste with DRBD, though not eliminate because you get some disks which you can’t really use for anything else rather than HA. Two instances also complicate things - depending on infrastructure it can be seen as almost no complication or quite serious complication.

“Cannot do maintenance on cold standby database.”

But you can do anything you want with a database that you run off a DRBD LVM snapshot. Works on a Secondary node too.

I’m not sure Florian understood what was meant here. With MySQL Master-Master replication I can add and index on passive node, wait it to catch up and switch the roles (see another post) you can’t really do this with DRBD as this requires logical level of operation to work.

This is not to mention other things you can do with MySQL Replication, such as filtered replication or cross storage engine replication, though which are not typically used for HA purposes. Time Delayed replication is however something quite helpful for some environments. Though DRBD also could be extended to support one if needed.

Now, do not get me wrong DRBD is great, and thanks to Florian for following up and making sure myths about DRBD do not spread too wide.

So when I would recommend to use DRBD with MySQL ?

There some good reasons to use DRBD with MySQL though as I mentioned I do not view it as first choice solution.

First it is good choice for organizations which are got use to SAN based high availability solutions with active-passive management software. Quite often these guys would have be familiar with such HA concept and it would be very natural for them to use same approach for MySQL as they use for PostgreSQL for example instead of investing time to learn about MySQL Replication or just looking to keep MySQL infrastructure to be as close to one for other databases in use as possible.

Second - it is often inevitable choice when you can’t avoid loosing any transaction - period. Some people would rather stand longer fallback time (as with DRBD) but would not like to have lost transactions which may happen with async replication. Another similar case is when you’re looking to ensured consistency - MySQL Replication can out of sync - and there is bunch of tips in documentation of how to do it. With DRBD the chance of nodes running out of sync is minimal and can be caused by software and hardware bugs rather than known limitations.

You could argue depending on cases you spend most time working with how much cases do these correspond - some people mainly deal with systems which can’t accept any transactions loss and for these DRBD often comes as a first choice if you have more experience with traditional web shops - these usually would prefer to lose one user comment a year instead of paying extra performance costs.

It is worth to note DRBD also allows building very nice mixed environments with MySQL - for example you can replicate binary logs using DRBD so if master node fails you have not lost transactions - when you can use such logs to do point in time recovery or to catch up to last few not committed transactions on the slave. We should spend some time implementing such script sometime which could be helping to get best of both worlds.

But currently - You can’t have it all

The state of High Availability Solutions for MySQL these days is - you can’t have it all. There is no OpenSource solution out where which would offer you full redundancy use of both nodes at least for reads no transaction loss and automated fail over. Whenever you’re using MySQL Cluster, DRBD or MySQL Replication you have to have some compromises.

Entry posted by peter | One comment

Add to: | | | |

昨晚配合淘宝升级，候1:30左右开始干活，事情不多，很快就做完了。兄弟们还在继续奋斗。
我已经可以睡觉了，没想到一段时间不熬夜，晚上竟然失眠了，ft。
anysql培训完了，今天早上一来居然要我给培训下，我晕….

best oral
dog knot women
hot nasty amateur sexfree
brazil sex ass
hard disk data recovery
huge dildo monster big
puke on cock
extreme fucking machines
asian virgin
Oversized Boobies-2 CD-1
fat men masturbating
learn how to deep throat
amature video blog
how to fuck a girl
old guy ffm
12 year old models in thongs
girlfriend blowjobs
adult sex search engines
exotic filipina gro manila cebu hotels
deep river mp3
big horny brazil
jizz on me
iron giant hentai
classroom teacher tits
life teen
fff licking orgy
coed nude track meet galleries
internal creampie eating
stories of sex with secretary
oriental sex
sublimal penis enlargement
oriental lesbian asian orgy
asian anal clips
anal shit
tits farm
young sexy teens
tattoo skull art
lesbian pussy lick
tv moms nude
south korean tae kwon do schools
college lesbian
naked outdoors
help to quit smoking
download all new free online dbz games
lovers sex
nude erotic
tongue piercings painful
mature doggystyle
fist logo
Other Peoples Honey
fist fucking whores
pre teen fuck
mature fuck galleries
my wifes clit
horny military wives
horny lesbian sluts
free nude female athletes
Anal Massaker-4 CD-1
caught babysitter
fat indian girl
teen first anal
crazy sluts
ashley olsen naked
mouth lesions young children tongue
big tit lesbians
old cfnm
young girls pissing
interracial gay wrestling
Squirt Queens-12 CD-2
free spanking live clips
dog lover license plate frames
slut wife glory hole
cheerleader handjob
paris hilton free sex
balifff
teen in tights
sleep voyeur
cum kiss
adult halloween party ideas
dental mouth guards
chinese orgy
large cap wigs
hot latins
big black asses getting fucked
horny gay men in showers
does jacking off stunt the growth of a penis
male sexual abuse
lingerie dildo babe
fox hustler flex fit hat
shower boobs
naked coeds hard core
stupid drunk pussy
breast enlargement surgeon delaware
piercing the clitoris
grandma facial
monster tits free
free rape stories
fat people sex
female orgies
extreme tattoo
asian thong gallery
paparazzi celebrity
Blow It Out Your Ass CD-2
ass deep
lupus spanking
big milf tits
beijing blog
hairy strip
Xxxl Sex-18 CD-1
teen shower orgasm
bdsm japanese
men ass holes
korean teen nude
hot horny hunks
sexy nude blonde
young male actors
smell her stinky feet
free sample of penis enlargement
tiffany towers mpeg
korean girl girl
free male celebrities naked
sample teacher classroom evaluation
alyssa doll camel toe
family incest cartoons
panty hose pics
free female ejaculation videos
big and large pictures of penis
monster penis
free college sluts
lesbians xxx
live oak resort
hustler virtual girl
milfs in thongs
close up preteen pics
female public poop
breast fuck
granny milf
Big Boob Party-4 CD-2
sexy blonde fuck
jessica rockwell
xxx passwords xxx
Ill Toss Your Salad If You Butter My Buns CD-1
lg vx8300 hacks
free tranny porn
silvia tug job
beastiality tgp
perfect shemales
what vitamin can increase penis growth
led zepplin black dog
hot dirty sluts
mature mom son
amateur adult film
deep throat cock gag porn video
maid horny
free filipina video porn
hot lezzies riding strapon
better ways to masturbate
mature porn pics
asian girl feet
milf interracial

In my post on estimating query completion time, I wrote about how I measured the performance on a join between a few tables in a typical star schema data warehousing scenario.

In short, a query that could take several days to run with one join order takes an hour with another, and the optimizer chose the poorer of the two join orders. Why is one join order so much slower than the other, and why did the optimizer not choose the faster one? That's what this post is about.

Let's start with the MySQL query optimizer. The optimizer tries to choose the best join order based on its cost metric; it tries to estimate the cost for a query, then choose the query plan that has the lowest cost. The unit of cost for the MySQL query optimizer is a single random 4k data page read. In general, it's a pretty good metric, but it has one major weakness: the server doesn't know whether a read will be satisfied from the operating system cache, or whether it'll have to go to disk. (This distinction is abstracted away by the storage engine; the optimizer doesn't know how a given storage engine stores its data).

I'll try to omit the details and keep this clean. Let's take a look at the tables.

It's a big fact table and two fairly small dimension tables, which is normal. Here is the query:

There are indexes on all the columns in all the ways you'd expect: all the dimension columns are indexed on every table, and there's a separate index on every column in the WHERE clause. Here's the query plan initially.

This query will run for days and never complete. No one ever let it finish to see how long it would run.

How do I know it will run for days? Here's my train of thought:

• It's performing index lookups into the fact table, which is big.
• An index lookup is a random I/O.
• A modern disk can do about 100 random I/O's per second, as a rule of thumb.
• If you do the math with the rows column in EXPLAIN, you realize that this equates to about 18790 * 606 = 11386740 I/O operations, assuming that the indexes are fully in memory.
• When you divide this by 100 I/O operations per second, and then divide that by 86400 seconds in a day, you get about 2.6 days.

Ouch! That's slow.

Now let's look at the alternative: table-scan the fact table, and do index lookups in the two dimension tables. MySQL doesn't want to choose this join order, so we'll force it with STRAIGHT_JOIN:

As we saw in the previous post, which I linked at the top of this post, we can scan the fact table in less than an hour. And it turns out that joining to the dimension tables doesn't slow the query perceptibly, because these tables are small and they stay in memory, in the OS cache. (They don't get evicted from memory by the cache's LRU policy, because they are frequently used -- once per row in the fact table. The LRU policy evicts old blocks from the fact table instead, which is perfect -- these blocks are used only once and not needed again, so they can be replaced).

The difference between the two queries -- 55 minutes and 2.6 days -- is basically the difference between scanning data sequentially on disk and random disk I/O.

So now you know why one join order is faster than the other. But why didn't the optimizer know this, too? The optimizer does know that random access is slower than sequential access, but it doesn't know that the dimension tables will stay in memory, and this is an important distinction.

Let's put ourselves into the mindset of the optimizer. We'll assume that every join to the dimension tables will go to disk instead of being read from cache. Now the STRAIGHT_JOIN becomes a table scan of about 313 sequential reads (150 million rows / 117 bytes per row / 4096 bytes per read), plus about 150 million random I/Os for the first dimension table, plus 150 million random I/Os for the second dimension table. That's 300 million random I/O operations.

In contrast, the optimizer chose a plan that it thought would cause only 11.3 million random I/O operations.

The optimizer was being smart, given its lack of knowledge about the OS cache. This is why an expert is sometimes needed to provide the missing information. If the MySQL optimizer were right and each of these had to go to disk, our STRAIGHT_JOIN plan would take more than a month to complete! Good thing we know the difference between cache and disk!

Entry posted by Baron Schwartz | 3 comments

Add to: | | | |

“为什么我耗尽心血写出来的blog无人问津？而别人写出来看起来不怎么样的文章确备受推崇？？这是为什么？。。。。百思不得其解，苦恼～～～～”

不少的Blogger都有这样的疑问～

其实对于自己的blog来说，每一篇文章都是花费了不少心血堆砌而成～谁不想吸引更多人注意？谁不渴望获得更多的交流？

但这里面有个误区：

你认为优秀的   不等于  别人就认为优秀～

除了写出更多有趣、专业、风格化的文字外，还应该注意文章的表达方式。

就拿中国来说，大家都在说普通话，而你说方言，虽然观点很精辟，却很少有人理解，甚至很少有人去看～

在你和阅读者之间应该有一个沟通的桥梁，这就是你的blog文章结构～

一个好的结构可以：

• 节省阅读者的时间
• 让读者更迅速、更准确的了解你的观点
• 给读者留下更深刻的印象

1.文章的开头就摆明你的观点

一个普通的读者可能在每天早晨8：00-9：00的时间内阅读500左右的blog文章和新闻内容，因此，为了你和读者的时间，请你在文章的开头就摆明你的观点，我坚信这样的写作风格会赢得读者更多的信任和支持。

2.配上一幅有冲击力、符合主题的图片

一张图片的力量抵得过千言万语，如果是一张有冲击力、符合主题的图片，那更是如虎添翼～我就非常喜欢在blog中加入图片的说明，不仅仅是blog的标题图片，当我向读者解释比如“Forgetful-system流程图”时，就会用viso简单的画出一张图片，方便读者理解。

3.尽量引经据典或者引用名言

我们都是站在前人的肩膀上思考问题，因此，在你表述你的观点时，别忘记了某位名人是怎么说的～或者某本书里是怎么解释的，这样不仅增加了文章的威信，也能赢得读者的信任。

4.培养合理分段及使用项目符号的习惯

“我们使用毛笔的时候惜字如金，唐诗宋词是代表，当我们使用钢笔的时候，我们开始啰嗦，小说杂文是代表，当我们开始使用电脑的时候，我们开始胡言乱语，blog是代表”这段话是我的感慨，非经典。

在这个信息轰炸的时代，我们一定要表述的逻辑清楚、方便阅读，不仅为了别人，也是为了自己，请培养合理分段及使用项目符号的习惯吧

5.文章的最后进行总结，列出参考文章

最后归拢一下你的观点，很多读者阅读文章都有这样的习惯：

• 阅读标题
• 阅读第一段
• 阅读正文中有着重号和序号部分
• 阅读最后一段

一个好的观点，还需要一个好的表达方式，希望您的blog更加优秀

相关内容

• 五个和你的伴侣和睦相处的窍门 (评论数:1)
• 五个聆听的技巧 (评论数:2)
• 五十句毁灭创造力的话 (评论数:1)
• 七个有效利用通讯录的窍门 (评论数:1)
• 七种对你的职业生涯有害的坏毛病 (评论数:0)

There is an integrity checking software called AIDE shipped by Red Hat® Enterprise Linux® 5. AIDE provides some general strategy for implementing integrity checking to detect the intrusions when they have occurred.

To install AIDE, using following command:

# yum install aide

To configure AIDE, customize the /etc/aide.conf to meet the requirements of the system. For more detailed information about the AIDE configuration file, please refer the man page of aide.conf.

To generate a new database and install it on AIDE system:

# aide --init
# cp /var/lib/aide/aide.db.new.gz /var/lib/aide/aide.db.gz

By default, the program will create a new file aide.db.new.gz in the /var/lib/aide/ directory. This must be installed on the system manually by using the copy command. Typically, system administrator should create an AIDE database on a new system before the system is connected to any network.

To check the inconsistencies between the current system and the AIDE database, running following command:

# aide --check

or

# aide

If the check produces any unexpected output, investigate and fix it as soon as possible.

The aide command can also be run as a crontab job to do periodic integrity checking.

There is an integrity checking software called AIDE shipped by Red Hat® Enterprise Linux® 5. AIDE provides some general strategy for implementing integrity checking to detect the intrusions when they have occurred.

To install AIDE, using following command:

# yum install aide

To configure AIDE, customize the /etc/aide.conf to meet the requirements of the system. For more detailed information about the AIDE configuration file, please refer the man page of aide.conf.

To generate a new database and install it on AIDE system:

# aide --init
# cp /var/lib/aide/aide.db.new.gz /var/lib/aide/aide.db.gz

By default, the program will create a new file aide.db.new.gz in the /var/lib/aide/ directory. This must be installed on the system manually by using the copy command. Typically, system administrator should create an AIDE database on a new system before the system is connected to any network.

To check the inconsistencies between the current system and the AIDE database, running following command:

# aide --check

or

# aide

If the check produces any unexpected output, investigate and fix it as soon as possible.

The aide command can also be run as a crontab job to do periodic integrity checking.

Not long ago - I wrote about some frustrations with the state of software 'development'.  This morning I read an article written not too long ago "They Write the Right Stuff".  I liked it a lot.  Maybe not practical or reasonable for every piece of software (but then again, why not?) - but definition sound techniques and processes for everyone.

The sections are

• The product is only as good as the plan for the product.  Ah, they are talking specifications, communication, documentation...
• The best teamwork is a healthy rivalry.  Indeed!  I've said before the best was to test your ability to recover in a DBA team would be to set up two teams - one is responsible for damaging a test database in any way they want to.  The other team is responsible for recovering from that catastrophe.  Next week - switch sides.  Not only fun but very enlightening (when I poll audiences, less than 5%, way less, have done a recovery in the last six months - could it be they might not be able to today if needed?).
• The database is the software base.  Now, they did not mean the database is the center of the application itself (I would say something like that) but rather the history, change control, reason for all change is.  The history, the legacy of the code is as important as the plan for the product.  You need to understand why things were done the way they were - in order to safely change it later.
• Don't just fix the mistakes - fix whatever permitted the mistake in the first place.  My favorite one!  How many times will I have to hear in my life "we have to do X, but you cannot tell us to use method Y to do it - Y cannot be done".  I get that all of the time - tell me how to make it go faster, but don't look at or mention touching the application.  The mistakes are typically to be found there - in the application (typically means 99.99999999999999999% give or take a small amount).

As an aside, anyone that knows me, knows my mantra - written many times:

• You should do it in a single SQL statement if at all possible.
• If you cannot do it in a single SQL Statement, then do it in PL/SQL (as little PL/SQL as possible!).
• If you cannot do it in PL/SQL, try a Java Stored Procedure.  The number of times this is necessary is extremely rare today with Oracle9i and above.
• If you cannot do it in Java, do it in a C external procedure.  This is most frequently the approach when raw speed, or the use of a 3rd party API written in C is needed.
• If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it...

Therefore - I just loved this Oracle-WTF.  And you know what - it pairs up with the "write stuff" article nicely.  I'll bet you that that original stored procedure was not planned (no specs), peer reviewed (no health rivalry), change managed as it was tweaked over time, and until now - never "fixed".  Can you imagine how long it took to reverse engineer that into a single SQL statement (I'd guess a minimum of an hour - and likely more).  An hour well spent, but I know personally the frustration of that person for that hour - cursing every developer that touched the code before them.

And I cannot tell you how much I hate code like this:

    BEGIN
        v_temp_runs := 0;
        SELECT COUNT(*)
        INTO   v_temp_runs
        FROM   dual
        WHERE  EXISTS
               ( SELECT *
                 FROM   temp_runs );
    END;

    IF v_temp_runs > 0 THEN

At least the original code did not end in "when others then null;" - there is that.

I went to AskTom to add the site’s search engine to my Firefox’s Search Bar but too bad the search engine link was broken:

So, I went ahead and created a new AskTom search engine plugin.

If you are browsing this page in Firefox 2 or above or IE7 or above, click here to install the AskTom search plugin.

Check out this page for more Oracle related search engine plugins.

---
Related Articles at Eddie Awad's Blog:

• Oracle Firefox search plugin
• Oracle Metalink Firefox search plugin
• See How Easily You Can Search Oracle
• Bring your old posts to life
• Oracle docs virtual book Firefox plugin

今天,哦,不对,是昨天,是今年5.1所在周的第一天.这个星期俺晚班.听说这个星期只上3天班,很多人和我说,真爽,这个星期只上3天班.

可是我列,看看这个礼拜的计划吧.

礼拜一(2008-04-28),现场,要通宵,已经到了现在,礼拜二的凌晨2:29分.操作都已经完毕,等待测试结束,还不晓得啥时候可以回家睡觉,没事调戏了下brotherxiao,问问他为啥也要到现在还在干活,据说是为了配合他们的兄弟公司升级,那么想必他兄弟公司的DBA也在干活吧,原来辛苦的DBA多的是,稍微平衡了点.

礼拜二(2008-04-29),晚班,15:30-20:00上班,晚上无安排;

礼拜三(2008-04-30),现场配合,预计到礼拜四的3:00可以回家;

礼拜四(2008-05-01,伟大的劳动节),现场配合,预计礼拜五的3:00可以回家;

礼拜五(2008-05-02),online standby,全天.

礼拜六(2008-05-03),全天online standby,晚上现场配合,预计到礼拜日的0:00可以回家;

礼拜日(2008-05-04),全天online standby,晚上现场配合,预计到下个礼拜一的0:00可以回家;

 要劳动的劳动节,真美好!

PS:决定把5.1的调休假期攒起来,以后一起请假,估计我要是攒下所有假期,我一年可以休息2月了,嗯,赶上老师了,老师也不过休息3月.

前段时间一位同事收到某开发部门一位同事求助，希望帮忙优化一条Mysql的sql语句，大体如下：

select n.id ,nc.content
from news n force index (category1_status,category2_status,category3_status),news_content nc
where n.id=nc.id
  and n.status=2 and (n.category_id_1 in (5003107,5003108)
  or n.category_id_2 in (5003107,5003108)
  or n.category_id_3 in (5003107,5003108)

调试的时候发现怎么都不能走index_merge的执行计划（我们所期望的），后来临时给他们一个union的解决方案。后来下班吃完晚饭后一起找问题，发现即使只有单个表，也没办法走到index_merge的执行计划，不管是加提示还是不加提示，调试过程如下：

mysql> explain select n.id ,nc.content
    ->  from news n force index (category1_status,category2_status,category3_status),news_content nc
    ->  where n.id=nc.id
    ->  and n.status=2 and (n.category_id_1 in (5003107,5003108)
    ->  or n.category_id_2 in (5003107,5003108)
    ->  or n.category_id_3 in (5003107,5003108)
    ->  ) ;
+—-+————-+——-+——–+—————————————————-+———+———+————–+——–+————-+
| id | select_type | table | type   | possible_keys                                      | key     | key_len | ref          | rows   | Extra       |
+—-+————-+——-+——–+—————————————————-+———+———+————–+——–+————-+
|  1 | SIMPLE      | n     | ALL    | category1_status,category2_status,category3_status | NULL    | NULL    | NULL         | 552535 | Using where |
|  1 | SIMPLE      | nc    | eq_ref | PRIMARY                                            | PRIMARY | 4       | biznews.n.id |      1 |             |
+—-+————-+——-+——–+—————————————————-+———+———+————–+——–+————-+
2 rows in set (0.00 sec)
从上面可以看出，Mysql优化器已经识别到有三个索引可以用，但是没有选任何一个，然后去掉其中一个参与join的表，同时去掉提示，还是不行：

mysql>  explain select *
    ->  from news n
    ->  where
    ->  ( n.status=2 and n.category_id_1 = 5003107)
    ->  or ( n.status=2 and n.category_id_2 = 5003107)
    ->  or (n.category_id_3 =5003107 and n.status=2);
+—-+————-+——-+——+——————————————————————–+——+———+——+——–+————-+
| id | select_type | table | type | possible_keys                                                      | key  | key_len | ref  | rows   | Extra       |
+—-+————-+——-+——+——————————————————————–+——+———+——+——–+————-+
|  1 | SIMPLE      | n     | ALL  | news_ind_status,category1_status,category2_status,category3_status | NULL | NULL    | NULL | 552535 | Using where |
+—-+————-+——-+——+——————————————————————–+——+———+——+——–+————-+
1 row in set (0.00 sec)

单表，加提示效果：

mysql>  explain select *
    ->  from news n force index (category1_status,category2_status,category3_status)
    ->  where
    ->  ( n.status=2 and n.category_id_1 = 5003107)
    ->  or ( n.status=2 and n.category_id_2 = 5003107)
    ->  or (n.category_id_3 =5003107 and n.status=2);
+—-+————-+——-+——+—————————————————-+——+———+——+——–+————-+
| id | select_type | table | type | possible_keys                                      | key  | key_len | ref  | rows   | Extra       |
+—-+————-+——-+——+—————————————————-+——+———+——+——–+————-+
|  1 | SIMPLE      | n     | ALL  | category1_status,category2_status,category3_status | NULL | NULL    | NULL | 552535 | Using where |
+—-+————-+——-+——+—————————————————-+——+———+——+——–+————-+
1 row in set (0.00 sec)

后来，尝试了一下去掉一个or，，发现正常了，执行计划和我们预期的一样了：
mysql> explain select n.id ,nc.content
    ->  from news n force index (category1_status,category2_status),news_content nc
    ->  where n.id=nc.id
    ->  and ((n.status=2 and n.category_id_1 in (5003107,5003108))
    ->    or (n.status=2 and n.category_id_2 in (5003107,5003108))
    ->  );
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
| id | select_type | table | type        | possible_keys                     | key                               | key_len | ref          | rows  | Extra                                                            |
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
|  1 | SIMPLE      | n     | index_merge | category1_status,category2_status | category1_status,category2_status | 6,6     | NULL         | 54238 | Using sort_union(category1_status,category2_status); Using where |
|  1 | SIMPLE      | nc    | eq_ref      | PRIMARY                           | PRIMARY                           | 4       | biznews.n.id |     1 |                                                                  |
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
2 rows in set (0.01 sec)

ok，再将之前拿掉的参与join的表加入进来，也正常：

mysql> explain select n.id ,nc.content
    ->  from news n force index (category1_status,category2_status),news_content nc
    ->  where n.id=nc.id
    ->  and ((n.status=2 and n.category_id_1 in (5003107,5003108))
    ->    or (n.status=2 and n.category_id_2 in (5003107,5003108))
    ->  );
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
| id | select_type | table | type        | possible_keys                     | key                               | key_len | ref          | rows  | Extra                                                            |
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
|  1 | SIMPLE      | n     | index_merge | category1_status,category2_status | category1_status,category2_status | 6,6     | NULL         | 54244 | Using sort_union(category1_status,category2_status); Using where |
|  1 | SIMPLE      | nc    | eq_ref      | PRIMARY                           | PRIMARY                           | 4       | biznews.n.id |     1 |                                                                  |
+—-+————-+——-+————-+———————————–+———————————–+———+————–+——-+——————————————————————+
2 rows in set (0.00 sec)

莫非就是因为有三个or条件，而且三个条件都是不同的column上面，并对应上三个不同的索引所造成的？继续测试：
mysql> select * from t;
+——+————+———-+
| id   | name       | descs    |
+——+————+———-+
|    1 | abc        | x        |
|    2 | abcd       | xx       |
|    3 | abcde      | xxx      |
|    4 | abcdef     | xxxx     |
|    5 | abcdefg    | xxxxx    |
|    6 | abcdefgh   | xxxxxx   |
|    7 | abcdefghi  | xxxxxx   |
|    8 | abcdefghij | xxxxxxx  |
|    8 | a          | xxxxxxx  |
|    9 | ab         | xxxxxxxx |
+——+————+———-+

mysql> explain select * from t where id = 3;
+—-+————-+——-+——+—————+———-+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+—-+————-+——-+——+—————+———-+———+——-+——+————-+
|  1 | SIMPLE      | t     | ref  | t_id_ind      | t_id_ind | 5       | const |    1 | Using where |
+—-+————-+——-+——+—————+———-+———+——-+——+————-+
1 row in set (0.00 sec)
 
mysql> explain select * from t where name = ‘abc’;
+—-+————-+——-+——+—————+————+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+—-+————-+——-+——+—————+————+———+——-+——+————-+
|  1 | SIMPLE      | t     | ref  | t_name_ind    | t_name_ind | 23      | const |    1 | Using where |
+—-+————-+——-+——+—————+————+———+——-+——+————-+
1 row in set (0.00 sec)
 
mysql> explain select * from t where name = ‘abc’ or id = 3;
+—-+————-+——-+————-+———————+———————+———+——+——+———————————————–+
| id | select_type | table | type        | possible_keys       | key                 | key_len | ref  | rows | Extra                                         |
+—-+————-+——-+————-+———————+———————+———+——+——+———————————————–+
|  1 | SIMPLE      | t     | index_merge | t_id_ind,t_name_ind | t_name_ind,t_id_ind | 23,5    | NULL |    2 | Using union(t_name_ind,t_id_ind); Using where |
+—-+————-+——-+————-+———————+———————+———+——+——+———————————————–+
 
mysql> explain select * from t where id = 3 or descs = ‘xxx’;
+—-+————-+——-+————-+———————-+———————-+———+——+——+————————————————+
| id | select_type | table | type        | possible_keys        | key                  | key_len | ref  | rows | Extra                                          |
+—-+————-+——-+————-+———————-+———————-+———+——+——+————————————————+
|  1 | SIMPLE      | t     | index_merge | t_id_ind,t_descs_ind | t_id_ind,t_descs_ind | 5,23    | NULL |    2 | Using union(t_id_ind,t_descs_ind); Using where |
+—-+————-+——-+————-+———————-+———————-+———+——+——+————————————————+
1 row in set (0.00 sec)
 
mysql> explain select * from t where name = ‘abc’ or descs = ‘xxx’;
+—-+————-+——-+————-+————————+————————+———+——+——+————————————————–+
| id | select_type | table | type        | possible_keys          | key                    | key_len | ref  | rows | Extra                                            |
+—-+————-+——-+————-+————————+————————+———+——+——+————————————————–+
|  1 | SIMPLE      | t     | index_merge | t_name_ind,t_descs_ind | t_name_ind,t_descs_ind | 23,23   | NULL |    2 | Using union(t_name_ind,t_descs_ind); Using where |
+—-+————-+——-+————-+————————+————————+———+——+——+————————————————–+
1 row in set (0.00 sec)
mysql> explain select * from t where name = ‘abc’ or id = 3 or descs = ‘xxx’;
+—-+————-+——-+——+———————————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys                   | key  | key_len | ref  | rows | Extra       |
+—-+————-+——-+——+———————————+——+———+——+——+————-+
|  1 | SIMPLE      | t     | ALL  | t_id_ind,t_name_ind,t_descs_ind | NULL | NULL    | NULL |   10 | Using where |
+—-+————-+——-+——+———————————+——+———+——+——+————-+
1 row in set (0.00 sec)
 
mysql> explain select * from t force index(t_id_ind,t_name_ind) where name = ‘abc’ or id = 3 or descs = ‘xxx’;
+—-+————-+——-+——+———————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys       | key  | key_len | ref  | rows | Extra       |
+—-+————-+——-+——+———————+——+———+——+——+————-+
|  1 | SIMPLE      | t     | ALL  | t_id_ind,t_name_ind | NULL | NULL    | NULL |   10 | Using where |
+—-+————-+——-+——+———————+——+———+——+——+————-+
1 row in set (0.00 sec)
基本验证了上面的想法，只要是两个索引，都可以走index_merge，换成三个马上就不行了，即使是强行指定用某两个索引也不行，索引都能够认到，但优化器就是不使用任何一个。想一下，如果按照提示，使用了两个索引，那么会有剩下一个条件不会走索引，那么对于该条件的过滤还是要通过表查询，这样，对于mysql来说就相当于要两个索引的index_mereg后再读表，而且仍然要做一次全表扫描，那还不如就作一次表扫描，Mysql最终还是选择一次表扫描是可以理解的。在Mysql文档上面也说了，在提示了mysql用某一个索引后，也就相当于告诉了mysql不要用其他的相关的一些索引。估计Mysql也并没有去实现三个索引的index_merge，实际上想想就算是实现了，通过读三个索引然后做merge再去取表的记录，其消耗可能也并不会太小，对于Mysql的这个选择也无可厚非。

©作者：eygle 发布在 eygle.com

度过了一个繁忙的4月，准备开始进入一个炎热以及更加繁忙的夏日。

在4月和5月之间，开始了我的一个小小假期，现在和Julia一起回到广东，准备接下来到香港、澳门转一转，放松一下。

这几天没怎么上网，于是偶尔接到一些朋友的求助电话，问及一些技术问题，不过这都是小事情，这几天关注的是这样几件事。

1.安徽阜阳的儿童感染肠道病毒EV71事件
昨天周筠老师发短信告诉我要警惕小儿肠道疾病（可能没有Baby的朋友不太会关注这类新闻），今天看报纸才知道这一事件。转引一点新华网的官方报道：

4月27日，新华社向全国发布消息，称"阜阳市3月份以来已有789名儿童感染肠道病毒EV71，19人经全力抢救无效死亡。"至此，近一个多月来，安徽阜阳"怪病"夺取儿童生命、"小儿非典"、"人禽流感"、"口蹄疫"等传言不攻而破。经卫生部、安徽省和阜阳市专家的流行病学调查、临床诊断和实验室检测，确定该病为肠道病毒EV71感染。

这个事件有两大注意点，一是地点，安徽阜阳，在2004年，这个地点以毒奶粉事件而闻名于世，现在这个名地再次引起世人瞩目；
第二是新闻批露，据说这一次又是耗时长久无法遮掩后的事实公开。

在中国，人们什么时候才能够学会关注生命？勇于担当呢？

2.山东火车相撞事故
刚刚看电视新闻得知这一惨剧。时间是2008-04-28 ，转引新华网的官方新闻：

28日凌晨4时41分，北京开往青岛的T195次旅客列车运行至山东省境内胶济铁路周村至王村间脱线，与烟台至徐州的5034次客车相撞。来自铁道部济南铁路局的消息显示，据初步调查，胶济铁路火车相撞事故已造成66人死亡，247人受伤。

现在也许已经没有人关注家乐福、抵制了，我们多灾多难的祖国啊，让我们的善良每天都不得安宁！

3.淘宝网遭遇停电事故
这相对是一件小事，但是直接遭受影响的绝不比以上两起事件少，我的很多DBA朋友也直接亲历此事。
网上的报道多数来此猜测：

4月25日下午1点半左右，淘宝网发生故障，网站页面无法访问，淘宝会员的网上交易受到严重影响。
据统计，淘宝网去年的去年的交易量达到433亿，马云更是豪言2008年要突破1000亿。按照去年的交易量估算，日交易量将能达到1.18亿。据此推算昨日淘宝断网三个小时将直接损失约1500万的交易量，保守估算于10%的利润的话，淘友们昨日的损失至少有150万。

但是淘宝的DBA们实际上经历了更多的抉择，DataGuard的切换与否、数据的损失弥补、故障的持续时间、电力的恢复供应等等等等。
而已经实施的高可用性方案，在面对灾难和故障的考验时，更多的问题凸显出来，真正经受得住考验的方案才是好的方案。这个考验可能来自不同方面，DBA要思考的东西真是多多益善。

有一点可以达成共识：DBA这个职业绝对是考验人的。

5.1的假日即将到来，祝大家都有一个宁静、快乐的假期，也期望假日无新闻！

-The End-

相关文章|Related Articles

• 美丽的京剧 美丽的晋剧
  
• 奥运会、抵制 与 anti-cnn
  
• NVIDIA GeForce4 MX 440与液晶电视
  
• 决战顺义之边-CS真人对决
  
• 发现一个有趣的网站-www.breakthechain.org
  

评论数量(3)|Add Comments

本文网址：http://www.eygle.com/archives/2008/04/holiday_focus.html

前两天将数据仓库的日志数据库从10.1.0.3升级到了10.2.0.3，开发人员反映，有个存储过程以前10分钟跑完的，
现在要129分钟才跑完，这个存储过程的逻辑是对当天sqlloader进去的数据做个group by操作，插入到别的表里面去，
经过测试主要是停在(PGA是8G)
EXECUTE IMMEDIATE ‘DROP TABLE analog_tmp_search’;
    COMMIT;

EXECUTE IMMEDIATE ‘
    CREATE TABLE analog_tmp_search NOLOGGING
    TABLESPACE cnweblog_tmp2_dw
    AS
    SELECT session_id,
      step,
      MAX(search_kw) search_kw,
      MAX(search_type) search_type,
      MAX(search_category_id) search_category_id,
      MAX(search_res_cnt) search_res_cnt,
      MAX(search_click_id) search_click_id
    FROM
    (
      SELECT session_id, step,
        (CASE WHEN
          area_kw =”c-c_sek” THEN LOWER(cookieinfo) ELSE NULL END) search_kw,
        (CASE WHEN
          area_kw =”c-c_set” THEN NVL(cookieinfo, 0) ELSE NULL END) search_type,
        (CASE WHEN
          area_kw =”c-c_secid” THEN NVL(cookieinfo, 0) ELSE NULL END) search_category_id,
        (CASE WHEN
          area_kw =”c-c_srescnt” THEN TO_NUMBER(NVL(cookieinfo, 0)) ELSE NULLEND) search_res_cnt,
        (CASE WHEN
          area_kw =”c-c_sclkid” THEN NVL(cookieinfo, 0) ELSE NULL END) search_click_id
      FROM analog_tmp_listcookie
      WHERE report_time=’ || rpt_time || ‘
        AND area_kw in (”c-c_sek”, ”c-c_set”, ”c-c_secid”, ”c-c_srescnt”, ”c-c_sclkid”)
    )
    GROUP BY session_id, step
    HAVING MAX(search_kw) IS NOT NULL
    �