DBActive

IBM Rational Rose users seeking increased productivity, improved collaboration capabilities, and enhanced functionality should consider the more advanced features and functionality of the IBM Rational Software v7.0 tools. Learn why it pays to consider using or migrating existing Rational Rose projects to Rational Software Architect, Rational Software Modeler, and other tools.

During last one and a half year we had pretty good track record with MySQL Performance Blog - there were times when site was slow (especially when backup was running) but I do not remember significant downtime, until today we went down for few hours.

All this time the site was running on dedicated server which I rented from APLUS about 3 years ago. It is rather slow Celeron box with single disk and 512MB running Fedora Core 2. Despite its age (it was used “Value” server even when I got it) the server had very good track record with basically zero failures during this time - there were some network disruptions at Aplus but this is about all problems we had.

As the OS on the box become rather outdated plus server was old and had no RAID and I did not want to rely just on daily backups we got new server from 1and1 couple of weeks ago. Some people recommended them to me plus they had good price for hardware with decent base specs - 64bit CPUs, RAID remote reboot and serial console, backup etc. Plus we had one server hosted with them in Europe for couple of months for tracking European traffic in ClickAider and it work reasonably well.

This time I got less lucky and in about a week after we moved MySQL Performance Blog to the new server, it stopped responding. I went to the control panel and reboot the system - it does not come back with no messages at serial console at all. Worst of all it gets into situation when It thinks reboot is in progress forever so I can’t even boot it again to rescue mode.

I call 1and1 and explain them the problem. The guy checks the system by rebooting it in rescue mode and back to normal and as it does not boot in normal mode he tells me his dedicated server team has to take a look at it and I should expect answer from them in 3-4 hours. Come one! 3-4 hours before anyone even starts looking at your problem this is as good as never for any passionate online business. The guy also tells me I can write to the Server Support team by email and they should get back to me quickly - as you may guess I’m yet still to get reply from them.

I call again in a few hours and get to another guy. This one he tells me he can’t reboot the server and I should try to boot it in “Last known good configuration” for which I point him it is not Windows. He tells me “OK you’ve got to run hardware diagnostics when while in rescue mode” ok, I ask him what command should I run - he tells me to run “fdisk -l” (which lists partitions) I ask him to spell that to me carefully and then politely ask him to pass me to someone less clueless then he is. He refuses to do that (silly as I just can call again) I ask him to talk to his manager and he also refuses sending me to nameless complain service (which I would imagine goes directly to trash).

A side note: This actually may be the worse part. In organization your stuff members can be wrong or there may be misunderstanding with the customer so passing to the different guy or to the manager is a must for any reasonable customer service. This was the case in MySQL and Tom Basil had a magic of calming down most of rare offended customers. In our Consulting Work we also follow the same principle - if customer requests second opinion he always gets it.

So what is the best way to deal with clueless support stuff working for big companies/big call centers ? Of course call again. I call again and get to the different guy. This person is not so clueless which is good. Though now they can’t find any record of a call I made 3 hours ago. Anyway he goes ahead and comes back with same result - Server Team has to take a look at the server and this time I get even better time estimate - tomorrow and there is nothing he can do other than escalating the case in the system.
As you may guess my blood is boiling at this point.

The next joke comes just 10 minutes ago - An email from 1and1 about closed case asking me to tell them how happy I was with service provided. How does one suppose to feel having his case solved without problem being resolved and which feedback one would provide. Of course there may be internal ticket created for mysterious “Server Team”.

At this point I did not expect any help to match my timing so I went ahead and returned web site back to old server which happily was still available. Now I will wait just for sake of interest how long will it take 1and1 to finally solve my problem before canceling their service.

Request for Advice: As this move did not work I’m looking for other hosting location in US I would either rent 2-3 servers or best get some rack space and buy my own servers instead.

P.S Some may tell me I’m just paying for being cheap. Well it is true I’m trying to get good value at good price,
and it really works in most cases. I’m very happy with Aplus Value Server which we’ve been used as well as with our racks with Hurricane Electric and Black Lotus both are not perfect but pretty good value for the price. Hostik on other hand was absolutely horrible.

As you may already have seen announcement by Baron we’re working on major rewrite of High Performance MySQL book - the most famous book about MySQL Performance on the market… which is getting old though. We’ve been slowly working on the book for over half a year now and were later joined by Arjen Lentz and Baron

I think we make a great team and will be able to provide great in depth book on MySQL Performance topic to the market. Me and Vadim have great internals insight and a lot of practical experience in MySQL Scaling and Performance Tuning but our Russian-Ukranian English needs a lot of editing and we’re far from experts writers plus we’re quite busy doing MySQL Consulting.

Arjen has great writers experience - he worked on MySQL Documentation for many years as well as contributed a lot to MySQL Training experience which gives him an edge on how to make material understandable to the people. Arjen also did fair amount of MySQL Support and Consulting while working for MySQL so he can offer us another competent point of view.

Baron has a experience in performance optimization for MySQL and other databases which gives a very important angle plus being author of large amount of popular MySQL tools such as MySQL Toolkit he is surely the man for the job. Not to mention his excellent writer skills - many articles you find at his blog are very smart in depth and elaborate.

OK at this point you should have already seen the outline, if not read Baron’s announcement so we would appreciate hearing your comments on it and what is the most important we would like to hear your ideas about things which need to be covered.

Do you think some MySQL Performance related things are not covered yet in MySQL manual or there is some interesting optimization trick you’re using or anything else - let us know and we may be able to add material about it in the book. Just make sure it is something interesting to wide audience and not specific to a very niche application.

We also will be looking for reviewers. Let us know if you’re interested in reviewing one or more chapters.

We’ll keep you posted as book progresses when we have any more information

The semantic web is about machine-processable metadata. As you accumulate this information, where do you plan on putting it, and how do you plan on accessing it? Check out this open source solution.

Vadim asked me yesterday to update Microslow patch for 5.1.21, because the previous one I wrote for 5.1.20 failed to apply correctly on the new MySQL release. Imagine the expression on my face after I unpacked the sources and found out that MySQL incorporated the patch to their release. So it’s a built-in feature now in the 5.1 line. So far I noticed one addition to my code which is logging the microtime statistics into TABLE type log storage.

Maciek

The PHP development team would like to announce the immediate availability of PHP 5.2.4. This release focuses on improving the stability of the PHP 5.2.X branch with over 120 various bug fixes in addition to resolving several low priority security bugs. All users of PHP are encouraged to upgrade to this release. Further details about the PHP 5.2.4 release can be found in the release announcement for 5.2.4, the full list of changes is available in the ChangeLog for PHP 5. Security Enhancements and Fixes in PHP 5.2.4:Fixed a floating point exception inside wordwrap() (Reported by Mattias Bengtsson)Fixed several integer overflows inside the GD extension (Reported by Mattias Bengtsson)Fixed size calculation in chunk_split() (Reported by Gerhard Wagner)Fixed integer overflow in str[c]spn(). (Reported by Mattias Bengtsson)Fixed money_format() not to accept multiple %i or %n tokens. (Reported by Stanislav Malyshev)Fixed zend_alter_ini_entry() memory_limit interruption vulnerability. (Reported by Stefan Esser)Fixed INFILE LOCAL option handling with MySQL extensions not to be allowed when open_basedir or safe_mode is active. (Reported by Mattias Bengtsson)Fixed session.save_path and error_log values to be checked against open_basedir and safe_mode (CVE-2007-3378) (Reported by Maksymilian Arciemowicz)Fixed a possible invalid read in glob() win32 implementation (CVE-2007-3806) (Reported by shinnai)Fixed a possible buffer overflow in php_openssl_make_REQ (Reported by zatanzlatan at hotbrev dot com)Fixed an open_basedir bypass inside glob() function (Reported by dr at peytz dot dk)Fixed a possible open_basedir bypass inside session extension when the session file is a symlink (Reported by c dot i dot morris at durham dot ac dot uk)Improved fix for MOPB-03-2007.Corrected fix for CVE-2007-2872. For users upgrading to PHP 5.2 from PHP 5.0 and PHP 5.1, an upgrade guide is available here, detailing the changes between those releases and PHP 5.2.4.

“Basically a small company has a flavor to it, whereas a big company is sort of like checking into the Bellagio in Las Vegas. It”s a nice hotel but it has 5,000 rooms, so don”t expect anybody to remember your name. A small company is more like a bed and breakfast. You”re going to have a great time because you get along with people and it”s a much friendlier experience. You don”t really mind that the bathroom is down the hall because the people made a special vegetarian meal for you and then showed you around town. On the other hand, you might be at a bed and breakfast where they have weird leather implements and lots of cats.”

– From A Conversation with Joel Spolsky in ACM Queue.

Not loving your job? Visit the Joel on Software Job Board: Great software jobs, great people.

When we optimize clients’ SQL queries I pretty often see a queries with SQL_CALC_FOUND_ROWS option used. Many people think, that it is faster to use this option than run two separate queries: one - to get a result set, another - to count total number of rows. In this post I’ll try to check, is this true or not and when it is better to run two separate queries.

For my tests I’ve created following simple table:

Test data has been created with following script (which creates 10M records):

First of all, let’s try to perform some query on this table using indexed column b in where clause:

Results with SQL_CALC_FOUND_ROWS are following: for each b value it takes 20-100 sec to execute uncached and 2-5 sec after warmup. Such difference could be explained by the I/O which required for this query - mysql accesses all 10k rows this query could produce without LIMIT clause.

Let’s check, how long it’d take if we’ll try to use two separate queries:

The results are following: it takes 0.01-0.11 sec to run this query first time and 0.00-0.02 sec for all consecutive runs.

And now - we need too check how long our COUNT query would take:

Result is really impressive here: 0.00-0.04 sec for all runs.

So, as we can see, total time for SELECT+COUNT (0.00-0.15 sec) is much less than execution time for original query (2-100 sec). Let’s take a look at EXPLAINs:

Here is why our count was much faster - MySQL accessed our table data when calculated result set size even when this was not needed (after the first 5 rows specified in LIMIT clause). With count(*) it used index scan inly which is much faster here.

Just to be objective I’ve tried to perform this test without indexes (full scan) and with index on b column. Results were following:

1. Full-scan:
   • 7 seconds for SQL_CALC_FOUND_ROWS.
   • 7+7 seconds in case when two queries used.
2. Filesort:
   • 1.8 seconds for SQL_CALC_FOUND_ROWS.
   • 1.8+0.05 seconds in case when two queries used.

So, obvious conclusion from this simple test is: when we have appropriate indexes for WHERE/ORDER clause in our query, it is much faster to use two separate queries instead of one with SQL_CALC_FOUND_ROWS.

I believe we wrote about this before, but this topic popups again and again.
Today I’ve read opinion that if we have clause WHERE has_something=1 we should have index on column `has_something` (the column has two values 0 and 1).

In reality the right answer is not so simple.

Let’s look next table

with 20.000.000 records.

And in first case has_something=0 for 90% of rows (with random distribution)

Let’s check execution time with and without index


mysql> select count(name) from testr force key (has_something) where has_something=0;
+————-+
| count(name) |
+————-+
| 18001245 |
+————-+
1 row in set (35.96 sec)



mysql> select count(name) from testr ignore key (has_something) where has_something=0;
+————-+
| count(name) |
+————-+
| 18001245 |
+————-+
1 row in set (10.46 sec)


As you see with index the time is by 3.5 times slower.

Good that mysql in this case choose do not use index

Let look the case when has_something = 0 for 50% of rows.


mysql> select count(name) from testr force key (has_something) where has_something=0;
1 row in set (20.27 sec)



mysql> select count(name) from testr ignore key (has_something) where has_something=0;
1 row in set (10.62 sec)


query with index is still 2 times slower.

and this time mysql is going to use index in execution plan:

What about 30% rows with has_something=0 ?

mysql> select count(name) from testr force key (has_something) where has_something=0;
1 row in set (12.36 sec)



mysql> select count(name) from testr ignore key (has_something) where has_something=0;
1 row in set (10.51 sec)


Still query without index is faster.

And finally for case with 20% rows with has_someting=0


mysql> select count(name) from testr force key (has_something) where has_something=0;
1 row in set (8.39 sec)



mysql> select count(name) from testr ignore key (has_something) where has_something=0;
1 row in set (10.43 sec)


So only in the last case we really need the index on column `has_something`

Although most professional search engine optimizers agree that the green PageRank display has little to do with the actual performance of a website in Google and other search engines, many webmasters still focus on websites with a high PageRank when it comes to link building and website promotion. The PageRank a website can be faked easily.