Yesterday I helped someone who was seeing a lot of "server has gone away" error messages on his website. While investigating this problem, I noticed several things amiss, which appeared to be related but really weren't. The biggest measurable sign was

These two status variables are actually unrelated (see the manual page that explains them). The first was related to the errors the client was seeing: the server was closing inactive connections after a while, and I fixed it by increasing the wait_timeout configuration variable.

The second error does not indicate that an active connection is closed at all. Rather, it shows that a connection cannot be made for some reason. Perhaps it's networking, or perhaps there's an issue with permissions or something else. The first thing I did was look for packet loss between the database server and the web server; the network appeared to be working fine.

With that ruled out (at least, to my satisfaction) I turned to tcpdump to see what was happening with these connections. I ran the following command in one window of my screen session, so I could see when a connection was aborted:

And then I started tcpdump in another window:

After I saw an aborted connection, I cancelled tcpdump and looked at the resulting file. Inspecting the session with tcpdump -r showed that there was a complete TCP session; nothing bad was happening at that layer. So I used the strings utility to look at the text sent in the packets:

I've anonymized the offending IP address. However, I checked the server's grant tables and indeed. that IP address (which is a machine in the local network) is not allowed to connect.

I don't actually use tcpdump much, but this was a fun little exercise that I thought I'd share with you.

Entry posted by Baron Schwartz | No comment

Add to: | | | |

We've discussed today how we should implement MySQL Version advisory in mk-audit tool. One obvious questions was to look at the end of life - it is often bad idea to run MySQL versions past end of life as even security bugs may not be fixed in these (though do not get paranoid, if you're running MySQL in isolated environment the risk may be low).
So how does EOL schedule looks ?

MySQL defines Active Lifecycle and Extended Lifecycle for release where first one is 2 years since initial GA release and second is further 3 years of life in "critical bug fixes only" mode with releases available for premium (Silver+) Support offerings.

For MySQL Community users this means only releases within Active Life Cycle will be made. For example MySQL 4.1 had end of its Active Lifecycle in the end of 2006. and indeed Latest MySQL 4.1 available for the public is 4.1.22 while as Manual Says there were number of further releases with last one in March 2008 containing fixes for security and critical bugs.

It is also worth to note even though MySQL 5.0 successor (MySQL 5.1) is still not released as GA, MySQL 5.0 Active LifeCycle will end in end of 2008, unless there are changes means. If same policies as of MySQL 4.1 are followed we'll soon see stop in MySQL community releases of MySQL 5.0 most likely before MySQL 5.1 will proven MySQL 5.0 replacement.

There is no blame on MySQL - it is no fun to support these old versions both for Support team (remembering these all old versions limitations) and for development team, and it costs, so somebody has to pay for this and this is exactly what premium MySQL Support levels are for.

My main point is - make sure you understand MySQL Release Policy and so what to expect whenever you're MySQL customer or community user.

Shameless Plug: I guess hundreds of Percona customers are reading this blog so I should say how Percona treats old versions. We obviously recommend to upgrade when it makes sense while at the same time we have no restrictions in terms of supported versions. If customer chooses to run older version he may have more problems and these may take more time to deal with, so the bill would be higher. We are also happy to provide builds based on updated trees and backport fixes from the newer releases if MySQL has chosen not to backport bug because of its severity. We believe in freedom of choice.

Entry posted by peter | 4 comments

Add to: | | | |

After my previous post there were questions raised about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most cases when query can use both of the ways using multiple column index would be faster but I also went ahead to do some benchmarks today.

I'm using couple of simple tables:

I have populated this table with random data for i and j having both of them having 1000 of distinct values, independent on each other. I also created couple of other tables with same data just with really low cardinality with i and j having just 3 values each. The table contained about 18M rows though was small enough to fit in the systems memory.

I've benchmarked simple queries using where clause which covers multiple columns:

As some of them there way too fast if run once I ran them multiple times and measured time appropriately. To remove the overhead of starting MySQL etc from equation I also measured execution of "SELECT 1" query using same script and subtracted this time from result in the table.

time for ((i=0;i<100;i+=1)); do mysql test -e "SELECT sum(length(val)) FROM t1000idxmerge WHERE i=2 AND j=1"; done > /dev/null

In the result table I compute per query results and present results in milliseconds.

Note1: Q1 will not use Index Merge technique for low cardinality table but instead pick to do single index scan. I'm not aware of the optimizer hint which would allow to force index merge as you can do with index accesses in general.

Note2 Q2/Q3 can't use Index Merge however as it is currently implemented so they would use single index range scan. The 2 indexes however benefits to Q3 because it can only use first keypart of index (j,i) to resolve BETWEEN part of the clause which may not be very selective.

Note3 You may be surprised why 2 column index is faster for Q3 in case of low cardinality even though MySQL can't use index well. You're right MySQL can't and MySQL does not - Full table scan is performed and in this case turns to be faster than scanning 1/5th of the table using index. Also Full Table Scan is preferred for Q4 in all cases but in case of high cardinality multiple index configuration.

Note4 Q5 was just run on high cardinality tables to show what difference large BETWEEN can make.

Conclusion: For benchmarked queries we can see Multiple Column index beats Index Merge in all cases when such index can be used. It is also worth to watchout a MySQL may decide not to do Index merge (either intersection or union) but instead do full table scan or access table picking only one index on the pair.

Entry posted by peter | One comment

Add to: | | | |

好了，与几个VC谈起来了，下一步是什么？设法拿到一个term sheet！Term sheet是融资的一个最重要的里程碑。拿到term sheet，你的融资完成了一半。

你可能问，“为什么要term sheet？难道不是谈好个价钱，给我钱不就完了吗？” Term Sheet起到三个很重要的作用。

第一，为创业者，它要求VC表明他们的态度。虽然term sheet不是binding的（没有约束性的），但是好的VC给了term sheet后，一般不会反悔，除非他们发现你骗了他。50-90%的可能性，他们最终会投。当然，也有VC给term sheet很随便，很多反悔的 - 这种VC，敬而远之。你拿到一个VC term sheet，告诉别的VC，别的VC给你term sheet的可能性会高很多，因为，VC是群居动物。有几家抢得时候，你会发现，谈判容易很多，VC回复你的email和电话会很快。只有一个term sheet的时候，你会发现VC会让你焦急的等。

第二，为VC，term sheet可以锁定一个项目。一般的term sheet都会有exclusivity clause。签了以后，你就不能与别的VC谈。这样，VC就可以从容地做due diligence（尽职调查），更详细地了解你的公司的所有情况，以及市场和竞争对手。也因为是这样，有些不道德的VC，随随便便，并不很了解公司的时候，就给个term sheet，拖你几个月，然后跟你说bye bye。这比一个男生耽误女朋友的青春更可恶。如何避免这种不负责任的男生？打听一下，他有没有花花公子的名声？问他，他的term sheet有没有经过他的investment committee（投资委员会，VC的最高决策团体）的通过？问他，从term sheet到最终签约（final financing documents）和汇款，还要几道批准，几个人批准？跟你每天谈的，你这个项目的负责人，是investment committee上的一个人吗？Investment committee上的其他人，了不了解你这个项目，有没有跟你谈过，到你的同时来过？

第三，term sheet把投资优先股（Preferred Shares）的众多条款简明地列出来，让VC和创业者做一次初步的谈判，避免浪费几个月的时间做due diligence和做final financing documents。

Term sheet的几十条条款，我这里不一一介绍。我曾写过几篇：Vesting 创始人股份兑现，BASICS: valuation 公司估价，Term Sheet: Drag Along Right。还有太多可以些得了。

有两个好的网站我向大家推荐：美国VC协会的term sheet模板，和一个美国VC写得很详尽的term sheet里大多数条款的分析和惯例。

拿到term sheet后，就可以请律师了。切记要问这个律师有没有代表公司向VC融资的经验，融资的金额，还可以打电话给他以前的客户。

最后，告诉大家一个小秘密：不要急着签term sheet！很多创业者，因为觉得Term sheet是融资的一个最重要的里程碑，急着要签。但要记住，你有term sheet后，可以找别的VC要term sheet。第一个VC会很急，因为他怕被别人抢走。主动权在你手里。签了term sheet后，主动权就回到VC手中。有了排他权后，VC就可以慢慢的做尽职调查，甚至（坏VC）变脸改变条款。