Personal blog of Yzmir Ramirez

I have run into a number of cases recently that all had a similar look and feel. In most of these cases, the symptoms were very complicated, but they boiled down to just a few problems that can be prevented in very simple ways.

If you are not doing any of the following three simple things, you probably should. These are essential practices for building successful applications, which everyone should be doing. And the best part is how easy it is to do them — read on for the details.

1. Archive or Purge Your Database

Data archiving is crucial. There is a difference between data that your application needs now, and data that your application used to need. Do not co-mingle the two. Plan from the outset to archive or purge data that is no longer needed. Everybody wants to know how to deal with an application that is growing. The simplest way is to prevent it from growing. Archiving and purging unnecessary data is probably one of the highest value activities that you could ever do.

At a more technical level, archiving reduces the size of your workload’s “working set” — the data that the server really needs to keep in memory to function well. That’s because unused rows can bloat and dilute your indexes and tables. Storage engines such as InnoDB also don’t have features such as midpoint insertion algorithms for cache control, so a single table scan can flush really interesting data out of your LRU list and replace it with a bunch of stuff that’s needed only once for the table scan. Keeping those indexes small enough to fit in memory is a big step towards better performance, and archiving can help do that.

2. Monitor Your Servers

Everybody should have an automated monitoring system that is watching key elements of their systems. This can be very simple, even just an external service such as Pingdom or PagerDuty. Or it can be your own in-house hosted Nagios installation. The point is, your users should not be telling you when the service is down. Be the first to know, and have enough information to help you find the root of the problem.

Setting up monitoring can seem to be a daunting task, in part because of the steep learning curve that comes with most tools worth using. Another problem is the plethora of well-meaning but misguided advice and plugins for such tools, which will lead you to believe that you should monitor every ratio and counter there is in your systems — a sure recipe for an inbox flooded with useless noise, a monitoring screen that looks like the scene of a crime, and ultimately people who are numb to the monitoring system and ignore it when something really does go wrong. Keep it simple and monitor things that matter to your business: does the website load in the expected time and contain the expected text? Does the database server respond okay to a sample query that your application actually uses? Is the disk getting full?

3. Capture and Store Historical Data

Likewise, it is vital to have something recording metrics over time. A lot of people use an RRDTool-based system, such as Cacti. Again, it doesn’t really matter which one you use. At some point in the future, you are going to be very grateful that you have historical metrics to understand changes in your application, or understand what happened just prior to a crash. Here’s an example from a customer: the server crashed, and we could see in the log when that happened, but not why. The Cacti graph for disk space had a sharp curve — it showed the disk space suddenly filling up at the rate of gigabytes per minute. We looked at the disk and found it’d filled up with files due to an infinite loop in some code. How long would it have taken us to notice this otherwise, while checking dozens of possible causes?

Unlike with monitoring, you should measure and store everything you can, for as long as you can. You will never stay awake at night kicking yourself for measuring too much about your application.

All of the above are quite simple and fast to do once you’ve gone through the process a few times. I’d say that a practiced consultant can do a pretty comprehensive job in most applications in 8 hours or less. If it’s your first time through, it will take longer, and some good books might help. Either way, it is an investment with a very high ROI.

Entry posted by Baron Schwartz |
3 comments

Add to: | | | |

Couple of days ago I was looking for a way to improve update performance for the application and I was replacing single value UPDATE with multiple value REPLACE (though I also saw the same problem with INSERT ON DUPLICATE KEY UPDATE)

As I went from 1 value to 3 or 10 in the batch performance improved, especially for network tests, however going to batches of 100 and 1000 values performance started to degrade badly – the process which was taking 45 seconds with single value statements was taking over 12 minutes with 1000 values in a batch. This was a big surprise for me as I’ve used batching with great performance gains a lot of times.

I had 2 thoughts – either something is going on with parser or it is something nasty going on on Innodb level, so I tried running the test with MyISAM tables instead. The process completed in 12 seconds for 1000 rows in a batch vs 40 seconds for single row statements. So with single statement MyISAM was about 10% faster, which is quite expected but with 1000 rows in the batch the difference grew to 60 times !

I looked at the oprofile results:

samples % image name app name symbol name
312528 53.8091 mysqld mysqld dict_scan_to
150327 25.8823 libc-2.5.so libc-2.5.so __ctype_toupper_loc
71924 12.3834 mysqld mysqld .plt
18071 3.1113 no-vmlinux no-vmlinux (no symbols)

Which shows some funky function “dict_scan_to” taking over 50% of the time – not the function you would expect to see on top for update workload !

I asked Yasufumi, our Innodb code ninja to take a look what it could be and here is what he tells me:

This may be kind of performance bug of 5.0.

At row0sel.c:row_search_for_mysql() “PHASE 3:”,

InnoDB judge whether the SQL is “SELECT” or not.

5.0 does scanning the SQL for each time.

if (trx->isolation_level <= TRX_ISO_READ_COMMITTED
&& prebuilt->select_lock_type != LOCK_NONE
&& trx->mysql_query_str) {

/* Scan the MySQL query string; check if SELECT is the first
word there */
ibool success;

dict_accept(*trx->mysql_query_str, “SELECT”, &success);

if (success) {
/* It is a plain locking SELECT and the isolation
level is low: do not lock gaps */

set_also_gap_locks = FALSE;
}
}

The “REPLACE 1000 rows” SQL doesn’t have “SELECT” so, InnoDB scan all of the SQL each times…
(* “INSERT” may not use this function? “UPDATE” may be also affected)

On the other hand, current 5.1 at the same place is,

if (trx->isolation_level <= TRX_ISO_READ_COMMITTED
&& prebuilt->select_lock_type != LOCK_NONE
&& trx->mysql_thd != NULL
&& thd_is_select(trx->mysql_thd)) {
/* It is a plain locking SELECT and the isolation
level is low: do not lock gaps */

set_also_gap_locks = FALSE;
}

looking up to the flag of mysql_thd….

Scanning the statement for each update… that is a bummer ! It is great to see however it is fixed in MySQL 5.1
It also explains why the problem took a while to uncover – the most typical statement of the giant size which we use is INSERT, and it does not use this function so it did not have a bug.
INSERT ON DUPLICATE KEY UPDATE, REPLACE, UPDATE all should be affected, though I have not tested this carefully.

There is one more interesting thing with this code – The scan is only run if trx->isolation_level <= TRX_ISO_READ_COMMITTED - so the problem would not exist in case you use default REPEATABLE-READ isolation level - if you try to save by lowering isolation mode you get such unexpected regression. This is why I typically recommend people to stick to default settings (which everybody uses) unless there is proven measurable gain by doing the change. This especially applies to behavior changing settings which can cause a more significant impact than settings which apply to size of anything.

After putting isolation mode back to REPEATABLE-READ We get the batch with 1000 rows per statement run in 15 seconds instead of 12 minutes. What a change !

Entry posted by peter |
2 comments

Add to: | | | |

Jason Austin & Garrison Locke have a story to tell. They attended the 2nd Annual ZendCon, thinking that they knew PHP really well. They were on top of things, and would be on par, or even more knowledgeable than the average attendee. Once they sat in their first session, by Matthew Weier O’Phinney, they realized that they were sorely lacking and that their world was changing out from under them. This is their story.

The Chair of this year’s ZendCon UnCon (Keith Casey) has published details about how the UnConference will be run this year, and how to start submitting papers!

On the Affinity Bridge blog today there’s a great post looking at the use of Xdebug and KCachegrind to pinpoint bottlenecks and possible refectoring points in your code in a bit more visual way. They use an example from their own development – a script that took around twenty minutes to run.

A series of blog posts have been created covering the development of an extendable, modular CMS system, currently up to part 7 with ACL, Database, Navigation, Config, Bootstrapping, and Translate, already built in

Preparations for ZendCon 2009 are moving along and it’s less than 2 months away! Plus the Early Bird registration discount ends tomorrow, (Friday, 8/28). Read on to learn about some of the sessions and keynotes we have planned, as well as how to keep in touch with other ZendCon attendees.

Recently I had a chance to take a look at Redis project, which is semi-persistent in memory database with idea somethat similar to memcache but richer feature set.

Redis has simple single process event driven design, which means it does not have to deal with any locks which is performance killer for a lot of applications. This however limits it scalability to single core. Still with 100K+ operations a second this single core performance will be good enough for many applications. Also nothing stops you from running many Redis instance on single server to get advantage of multiple cores.

I call Redis semi-persistent because it does not store the data on disk immediately but rather dumps its all database every so often – you have a choice of configuring time and number of updates between database dumps. Because dump is basically serial write Redis does not an expensive IO subsystem. Also because this dump is background it does not affect read/write performance to the database which is in memory. In the tests I’ve done I’ve seen Redis doing writes some 4MB/sec for probably 50% of test duration where Innodb had to write 50MB/sec for about third of throughput and doing a lot of random IO as it was doing it. This is among other things because Innodb has to flush full 16K pages while doing flush.

The background flush in Redis is designed the following way – Redis process forks and justs dumps the database it has in the background. Unix copy on write takes care of getting another copy of pages as they are modified. This keeps overhead rather low. The database is dumped in temporary file which is renamed only after fsync which means if you crash during the dump you simply discard partial file.

I also liked full pipelining support in the protocol – you can send multiple commands at once – any commands and redis server will process them in order and returns results to you. This not only allows for multi-get and multi-set but any of batches of commands being submitted. The API support for this is relatively week but the features are there.

When it comes to data types – Redis supports simple key-value storage just as memcache but it also adds support for Lists, which are similar to the linked list data type you would have as well as sets which allow to store sets of strings with support of various set operations.

I kind of miss support for something like associative array/hash/map in the data types but I guess there is nothing in architecture which would stop it from being added later.

Redis also has support for “databases” which are basically key spaces inside the server. This should allow for using server by different applications, different versions, testing or some other features. Though you’ve got to be careful with these – there is only simple per instance password based authentication in place, so if application can talk to the instance it can access all databases.

I am also a bit surprised why databases are numbered instead of named. Naming the databases would make it more simple to avoid unwanted conflicts etc.

Redis also supports master/slave replication out of the box and it is extremely simple. You just specify from which node to replicate and this is it. It is even more simple than with MySQL as you do not need to deal with snapshot or binary log position. Replication is asynchronous and low overhead – redis will perform the database dump and store the commands on the data since the start of the process. Slave can get the data (which is basically set of commands to populate database itself) and when get the data from the master as it comes. I did not benchmark the replication capacity but I’d expect it to be close to 100K of writes/sec the single instance can handle.

The benchmarks I’ve done were for applications which is very update intensive with updates being pretty much random single row updates which are hard to batch. With MySQL/Innodb I got server being able to handle some 30.000 updates/sec on 16 core server with replication being able to handle 10.000 updates/sec. This was using about 5 cores so you could probably get 4 MySQL instances on this server and get up to 100K updates/sec with up to 40K updates/sec being able to replicate.

With Redis I got about 3 times more updates/sec – close to 100.000 updates/sec with about 1.5 core being used. I have not tried running multiple instances and I’m not sure the network and TCP stack would scale linearly in this case but anyway we’re speaking about hundreds of thousands of updates/sec.

I think Redis can be great piece of architecture for number of applications. You can use it as the database or as cache (it supports data expiration too)

I have not benchmarked it against memcache in terms of performance and memory usage. This may be another project to look at.

Entry posted by peter |
19 comments

Add to: | | | |

I wanted to write a few administrative updates in one so I didn’t spam everyone’s feed readers too much. Here we go:

• We’ve had reports of some lost comments.  We reported this via Twitter a while ago, but thought it was fixed.  We’ll try and pay more attention to spam filtering, but we wanted to reach out and say – let us know if you are one of the people having problems.  We’re listening.  (Of course if you have any suggestions on how to manage/filter through thousands of spam items/week, we’d like to know as well).
• Pingbacks were recently dropped when we upgraded to WordPress 2.8.  Miss them?  We’ve turned them back on.
• Our training sales for Santa Clara and San Francisco are going well.  As a result we’re planning to have an east coast location at some point in the future.  Please feel free to comment where you would like that to be!
• We have notified the winners of of our training competition.  I wanted to share their comments:
  My favorite Percona patch : innodb fast recovery patch. Reason: Innodb recovery has always been a big headache for any DBA since it used to take ages to complete a recovery after crash. Now with the patch I am able to do a recovery in less than a tenth of the time it used to take. Great job by Yasufumi in hacking the code(not just this patch ) which has been a nightmare for all DBA's.

  And:

  My favorite patch is "Micro second slow query patch" as it actively allows me to identify the queries which takes few seconds to execute but their frequencies is just too high i.e. few hundred times a hour. By optimizing these queries I can freeup the server resources to do other useful work.


• And the last item is a reminder: the deadline for our early bird registration on training is Monday.

Entry posted by Morgan Tocker |
10 comments

Add to: | | | |

Fabien Potencier has a suggestion for developers out there – don’t just look at what you write as something functional that just does a job, be an artist!