Personal blog of Yzmir Ramirez

In so many cases troubleshooting applications I keep thinking how much more efficient things could be going if only there would be a good instrumentation available. Most of applications out there have very little code to help understand what is going on and if it is there it is frequently looking at some metrics which are not very helpful.

If you look at the system from bird eye view – system needs to process transactions and you want it to successfully complete large number of transactions it gets (this is what called availability) and we want it to serve them with certain response time, which is what is called performance. There could be many variables in environment which change – load, number of concurrent users, database, the way users use the system but in the nutshell all what you really care is having predictable response time within certain range. So if we care about response time – this is exactly what our instrumentation should measure

Response Time Summary We want to understand where exactly response time comes from. For example if we define transaction as the time it took to generate HTML page we want to understand how much time was spent waiting on the database, memcache, other external services, as well as how much CPU time it consumed.

Now what is important we need this information for individual transactions. It may be every transaction which is best and easily achievable for small-medium systems or at least for large enough sample. It is very important this information is available for individual transactions not the average. Average is useless because 100 transactions taking 1 sec and 99 transactions having 1ms and 1 taking 99.1 sec will have the same average while for sake of performance analyzes these are completely different. When you have transaction sample make sure it contains fair population of transaction – getting only transactions which are slow is not helpful as we might want to compare them to the fast transactions to understand why they are slow.

What kind of components do you need to have in response time summary – all components which are significant enough. If your instrumentation has 95% of response time unaccounted for it is useless. You also want blocks not to mix apples with oranges. For example “mysql and memcache” block would not be helpful. Even further I would prefer to split “mysql time” in the “connect time” and “query time” as there are situations when one but not other would be affected.

In is important for response time summary stored in the logs which are easy to query so you can analyze data in a lot of various ways. Sometimes you may find the response time is impacted by queries from certain user, in others it may be attributed to different application/web server.

The goal for Response Time Summary is to quickly point direction where problem happens. Whenever you have spike in response time or it is bad response time for certain kind of request you can quickly understand where does it come from ? Is it wasted CPU time Slow response from MySQL or Memcache.

I also like to see numbers of calls stored together with attributed response time. For example I’d like to see number of mysql calls in addition to MySQL response time. This helps to understand if it is the issue with number of queries or their performance. If I see 2 queries taking 30 seconds it is clearly slow queries. If it is 10.000 queries executed and total response time is 4 sec I know it is pretty much as good as it gets with standard Ethernet network and finding a ways to reduce number of queries is going to be more helpful.

The Glue Our applications involve multiple layers and typically higher layer can only report response time it took to call lower layer, but not the reason for that response time. For example we can report time it took to execute SQL query from PHP application side, but we can’t say why it has taken so long. Was it row level lock ? waiting on disk IO or was it simply question of burning a lot of CPU. On the other hand this information may be available in the instrumentation stats from that lower layer – for example in MySQL Query Log. What is important is however to be able to connect the data from these logs – glue them together. The easy way to do it is to provide an unique identifier to all requests and put it in the logs with request of the lower levels. With mySQL the simple way to do it is to put it in the comments for queries you execute.

Optional Tracing The information in lower layers logs is very helpful however it typically have two problems. First not every layer has good logs. For example if you’re running memcached you probably do not have the logs detailing all requests and their response time. Second – the lower layer may only know response time from its vantage point, which in many cases does not include network communication time which can be very important.

Tracing should be optional and normally applied to the small sample of requests, though it needs to be detailed. Typically you would include the calls to the lower level services together with timestamp, the response summary with timestamp again. The information about request has to be complete enough to identify target action and response completely. For example if I’m speaking about memcache I’d like to know which server:port request was issued to which key was requested, and on response I’d like to know if it was hit, miss or error.

The way I use it may be as follows. I see the increased response time for given kind of request. I see response time is coming from MySQL. I check the number of MySQL Queries and it is 5x when it usually is for this kind of request. Looking at memcache stats I can see high number of misses. Looking at some available traces shows the server memcache01 has very high miss ratio. Checking what is going on with memcache01 shows it just was restarted (and hence has almost empty cache). This is important example as it shows your increased response time from MySQL may not have anything to do with MySQL itself but you would not know unless you’re capturing the right data.

If you’re looking for nice example framework for instrumentation, check out instrumentation for PHP – It has everything mentioned by tracing which is trivial to add.

Entry posted by peter |
One comment

Add to: | | | |

Percona is pleased to officially welcome Justin Swanhart to our team of consultants.

Before joining Percona, Justin worked as a MySQL DBA at Gazillion, Yahoo, and Kickfire. Justin has become a regular contributor here on the MySQL Performance Blog as well as being an active blogger at http://swanhart.livejournal.com/. He is very active in the community, maintaining FlexViews, a stored procedure managed solution for materialized view creation and maintenance in MySQL 5.1 as well as instrumentation-for-php, a suite of PHP classes for easing the implementation of instrumentation in applications.

Justin, a big welcome – we are fortunate indeed you’re working with us!

Entry posted by Ryan Lowe |
2 comments

Add to: | | | |

This is my last post in series on FlashCache testing when the cache is placed on Intel SSD card.

This time I am using tpcc-like workload with 1000 Warehouses ( that gives 100GB of data) on Dell PowerEdge R900 with 32GB of RAM, 22GB allocated for buffer pool and I put 70GB on FlashCache partition ( just to simply test the case when data does not fit into cache partition).

Please note in this configuration the benchmark is very write intensive, and it is not going be easy for FlashCache, as in background it has to write blocks to RAID anyway, and write rate in final place is limited by RAID. So all performance benefits will come from read hits

The full report and results are available on benchmark Wiki
http://www.percona.com/docs/wiki/benchmark:flashcache:tpcc:start.

Short version of results are on graph:

In summary:
on RAID final result: 2556.592 TpmC
on Intel SSD X25-M: 7084.483 TpmC
on FlashCache with 20% dirty pages: 2632.892 TpmC
on FlashCache with 80% dirty pages: 4468.883 TpmC

So with 20% dirty pages the benefit are really miserable, and it is quite explainable ( see note above about write intensive workload), but really on the graph we can see that probably 2h was not enough to warmup FlashCache enough.
And this is interesting problem with FlashCache what I see. Warmup by simple copying files does not work (you need O_DIRECT with proper blocksize), and you only rely on InnoDB in this case, and it takes about 30min+ to fill FlashCache. Solution there would be PRELOAD TABLE / INDEX, and it is in our roadmap for XtraDB.

With 80% dirty pages the performance gain in 1.7x and it is pretty decent, as you can get it for 500$ ( price of Intel X25-M card) ( for this particular workload, your experience may vary!).

On this stage I consider FlashCache as pretty stable and ready for an evaluation on real workloads ( kudos to Facebook team, they provide really stable release).

I actually did pretty bad test – just turned off power on SSD drive in the middle of tpcc-mysql run,
just SSD power, not whole server. No wonder FlashCache complained on failed writes, and after that I restarted full system. I was expecting that database is going to trash, but after restart FlashCache was able to attach previous cache, and MySQL was able to start and finish crash recovery. I am impressed.

In my next rounds I am looking to run similar benchmarks on FusionIO card.

P.S. And if CentOS team reads this post – please change default IO scheduler from CFQ to Deadline. Seriously, it makes so much damage on performance on servers with IO intensive workloads, so it should be the first action after CentOS installation. And I doubt that there big usage of CentOS on desktop systems anyway.

Entry posted by Vadim |
10 comments

Add to: | | | |

There are some seats free for my upcoming german symfony training session (). This training takes place mid-june (16-18th June) in a training center in Heilbronn.

Es sind noch ein paar Plätze für meine kommende offene Symfony-Schulung verfügbar () Diese findet am 16. bis 18. Juni in einem Trainingszentrum in Heilbronn statt.

InnoDB has an oft-unused parameter innodb_concurrency_tickets that seems widely misunderstood. From the docs: “The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread is allowed to enter InnoDB, it is given a number of “free tickets” equal to the value of innodb_concurrency_tickets, and the thread can enter and leave InnoDB freely until it has used up its tickets. After that point, the thread again becomes subject to the concurrency check (and possible queuing) the next time it tries to enter InnoDB. The default value is 500…”

What this means from a practical perspective is that each query is allocated 500 tickets when it begins executing. Each time it enters InnoDB, this number is decremented until it reaches zero (“entering InnoDB” appears only to occur when a row is accessed). When it reaches zero, it may-or-may-not be put into a queue and wait to continue execution. InnoDB doesn’t provide us a way in which to determine how many concurrency tickets a query uses, making this parameter notoriously difficult to tune. It is important to note that this variable only comes in to play when innodb_thread_concurrency is greater than zero.

On a stock install of MySQL, here are some example queries and the corresponding number of concurrency tickets used for each:

And now on to a more interesting scenario: foreign keys

So, how can we put this into practice, since this information isn’t available to most users?

INSERT w/PRIMARY KEY defined: Number of rows inserted – 1
INSERT w/FOREIGN KEY constraint: Number of rows inserted – 1
SELECT: 1 ticket per row returned
UPDATE: 1 ticket per row examined + 1 ticket per row updated
DELETE: 1 ticket per row examined + 1 ticket per row deleted
ALTER: (2 * rows in the table) – 1

As with any performance optimization effort, you will want to optimize for the common case. If you have a very simple workload, you can calculate these values by hand. But for most workloads with a complex access pattern, we’ll need to estimate or wait for InnoDB to expose this information to us.

What happens in the case where I have two distinct access patterns: single row primary-key lookups and SELECT statements that examine 900 rows? If innodb_concurrency_tickets is set to 500, then all of the single row PK lookups will execute without ever being subject to an additional concurrency check (there is always one when a thread first enters InnoDB) while the 900-row SELECT statements will always be subject to one additional concurrency check (we actually care less about the concurrency check itself than the possibility that it may become queued). Your first instinct may be to increase innodb_concurrency_tickets to >=900 in this case, but that isn’t necessarily the best decision. As stated in the docs, the number of threads that can enter InnoDB is limited by innodb_thread_concurrency (which is why these two variables are most often tuned in concert). To continue the example, if innodb_thread_concurrency is set to 8 and eight 900-row-SELECT statements come in, they will effectively block the PK lookups until one of them is subject to a concurrency check or complete execution and exit InnoDB. If innodb_concurrency_tickets had been increased to >= 900, then ALL of the PK lookups would be blocked until the 900-row-SELECT statements complete execution.

With a maximum value of 4,294,967,295 this has the potential to block other queries for a significant amount of time. Setting innodb_concurrency_tickets too high can have startlingly negative performance implications. On the other hand, if we determine that 99% of the traffic are these single row PK lookups and only 1% are the 900-row SELECTs, we may be tempted to lower the setting to 1 to accommodate the “typical case”. The effects of this, though, would be to cause the 900-row SELECT statements to be subject to 899 concurrency checks. This means 899 potential opportunities to be queued! So, as with most other parameters, this is a balancing act.

It really comes down to the importance of the applicable queries. Imagine those 900-row SELECT statements were actually 10,000 row selects, this would become a more pressing issue. If they are reporting queries used only internally, then it is not so much of an issue and you can leave innodb_concurrency_tickets rather small. If, on the other hand, these are the queries that lead to revenue generation, you may want to give them a bit more dedicated CPU time so they execute that much faster (even at the expense of the PK lookups). In other words, if you’re optimizing for throughput in this scenario, you will tune innodb_concurrency_tickets to the 99th percentile of small PK lookups. If you’re optimizing for response time, you would set it larger to accommodate the larger (important) select statements.

A quick sysbench run gives us the following results (X-axis is innodb_concurrency_tickets, Y-axis is txn/sec. More is better). Since all sysbench queries are 10 rows or less, we don’t really expect to see much of a difference here:

Details:

Applicable my.cnf settings:

Entry posted by Ryan Lowe |
2 comments

Add to: | | | |

As you may know MySQL ® announced 5.0.91 and 5.1.47 with serious security fixes, so we provide binary releases of Percona Server 5.0 and Percona Server / XtraDB 5.1 with security patches.

Fixed bugs:

• Bug#53371, CVE-2010-1848
• Bug#53237, CVE-2010-1850
• Bug#50974, CVE-2010-1849

Release Percona Server 5.0.91-rel22 is available in our download area:
http://www.percona.com/downloads/Percona-Server-5.0/Percona-Server-5.0.91-22/

Release Percona Server/XtraDB 5.1.45-rel10.2 is available also ( we backported patches from 5.1.47 to 5.1.45):
http://www.percona.com/downloads/Percona-Server-5.1/Percona-Server-5.1.45-10.2/

Updated RPM and DEB are also available in our repositories.

We know that there still are many active MySQL 4.0 and MySQL 4.1 installations and we understand how hard it is upgrade 4.0/4.1 to 5.1. With published exploits these installation are now under serious risks. If you are interested in backporting patches to 4.0 / 4.1 or to older 5.0 and 5.1 releases, it is available under Percona Maintenance package or regular Consulting

Entry posted by Vadim |
4 comments

Add to: | | | |

One schema optimization we often do is extending index when there are queries which can use more key part. Typically this is safe operation, unless index length increases dramatically queries which can use index can also use prefix of the new index are they ? It turns there are special cases when this is not the case.

The obvious optimization is to extend index from column (a) to column (a,b) right which will make it faster and should not hurt any other queries a lot, right ?

Wow. The query runs 30 times faster – not only because it has to scan less rows but also because it is index covering query now – it does not need to access the data.

It turns out it is too early to celebrate. Application also had another query which previously ran so fast it hardly could be noticed. It however became a lot slower:

So why did this query become so much slower ? The reason is its plan benefits from Innodb specific feature – index entries being sorted by primary key for each complete key value. So when you have index (a) and id is a primary key the real index is (a,id) when we extend index to (a,b) it really becomes (a,b,id). So if there is a query which used both a and id key part from original index it will quite likely be unable to use new index to full extent.

What is solution ? You can have “redundant” indexes on (a) and (a,b) at the same time. This is something what suppose to work but it well often does not:

MySQL Optimizer considers using both (a) and (a,b) indexes and in the end decides to use neither rather doing full index scan until it finds a=100. This looks like an optimizer glitch in this case because it estimates it will scan 2247 rows in the selected plan, while using (a) index you can get result scanning only 1 row guaranteed.

To really get things going you will need to use FORCE INDEX(a) to force MySQL optimizer using right plan.

These results mean you should be very careful applying index changes from mk-duplicate-key-checker key checker when it comes to redundant indexes. If you have query plans depending on Innodb ordering of data by primary key inside indexes they can become significantly affected.

Optimizer behavior may be different in different MySQL versions. These tests were done with 5.1.45 though I’ve seen same behavior with MySQL 5.0 too.

Entry posted by peter |
9 comments

Add to: | | | |

I worked with application recently which has great memcached hit ratio – over 99% but yet still has average page response time over 500ms. Reason ? There are hundreds memcached gets and even though they have some 0.4ms response time they add up to add hundreds of ms to the total response time.

The great memcached hit ratio is great however even more than that you should target eliminating requests all together. Hit rate is very bad indicator to begin with. Imagine you have application which gets 90 memcache gets (hits) to retrieve some data plus there are 10 more requests which resulted in misses and caused MySQL queries. The hit ratio is 90%. Imagine now you have found a way to store the data which required 90 requests as the single object. You have 1 request (hit) now and 10 misses which drops your hit rate down to less than 10% but performance will likely be a lot better.

The answer in many cases is to use larger objects for the cache.
If you look at a web page you typically will see some “blocks” which will be static and possibly highly cachable while others may be very dynamic or badly cachable. For example the area which displays user name and current time may not be cachable however widget displaying last 10 blog entries cachable at least for several minutes.

There are many development implications, component dependencies etc on how you can really implement caching but if you’re looking from birds eye view you want to have as fewer blocks in the page as possible (to reduce number of get requests and CPU efford required to build the page) at the same time you have to split the page in blocks which can allow good caching:

Make non cacheable blocks as small as possible If you have something like current time on the page which is not cachable do not mix it with other content which would have to be also re-created each time on the cache miss.

Maximize amount of uses of the cache item It is the most efficient if the item in the cache can be used by multiple pages and multiple users this will maximize cache efficiency while reduce amount of cache space required. The importance of this advice depends on the use case for your applications – some applications which have only couple of page views per user per day would really need to have items shared by multiple users to get best hit rate. Others which have hundreds of page views per user per session may be good enough with caches which are effectively per user.

Control invalidation Item in cache expire or get invalidated. It is best if such invalidation only kicks of the data in cache which needs to be invalidated but this may result in too small blocks and hence too many cache requests required. You may look for balance by looking to cache data together which invalidates in about same time.

Multi-Get Multi Get is truly nuclear option for caching with memcached both because it can be so efficient and because it can be so complicated to add it to existing code which was not design with it in mind. Multi-Get effectively allows you to manage items (expire,invalidate) separately while fetching them in the single round trip. You can have have hundreds of different items fetched from cache for the page without paying for network latency.

Does Multi Get removes the need to think about using larger items for cache ? I do not think so. You still have to pay extra cost of processing both on memcached server and application side. However the optimal cache topology would probably be different with multi get than without it.

The good theoretical example would be having complicated front page which would require a lot of cache requests and hundreds of milliseconds of CPU processing and which can be cached almost int its entirety with exception of user name in the corner. Storing complete page HTML in the cache and doing template replace for user name can be very efficient.

This may sound complicated and indeed there is a lot of art and science in designing optimal cache solution. The good thing is most web sites do not have to get it absolutely perfect to get good enough performance, it is most important to avoid biggest design mistakes.

Entry posted by peter |
2 comments

Add to: | | | |

MySQL supports two different algorithms for views: the MERGE algorithm and the TEMPTABLE algorithm. These two algorithms differ greatly. A view which uses the MERGE algorithm can merge filter conditions into the view query itself. This has significant performance advantages over TEMPTABLE views. A view which uses the TEMPTABLE algorithm will have to compute the entire contents of the view before any filter is applied. Computing the entire contents requires a temporary table and many more rows may be accessed than otherwise would had the filter been merged into the view query.

As long as a view avoids aggregation, DISTINCT, GROUP BY, ORDER BY, etc, then it can use the MERGE algorithm. Unfortunately, this means that views of any significant complexity will almost always use the TEMPTABLE algorithm.

This blog post demonstrates a workaround that allows the convenience of complex views, including those with aggregation (that is views which use the TEMPTABLE algorithm) with some of the performance advantages of MERGE algorithm views.

As a demonstration, consider the following table which contains a combination of integer values. There are enough values in the table such that a scan of all rows takes a second or two.

If we define a view which uses no aggregation, then MySQL will let us use the MERGE algorithm. Notice that there are no warnings:

If we then write a SQL query to aggregate over the rows of the view and we add a filter condition on c1, the view is accessed quicky:

We can use EXPLAIN EXTENDED to see that MySQL rewrites the view query to include the restriction:

Notice the rewritten SQL query:

Now, lets try to use the MERGE algorithm with an aggregation query:

Notice that MySQL sets the ALGORITHM=UNDEFINED, and will chose to use TEMPTABLE when the view is actually used:

When you try to restrict the second view using a filter on c1, performance is not good. MySQL is scanning the entire table to produce the result:

Even a query with an impossible where clause takes a long time to process:

You can see that MySQL is accessing millions of rows:

Now to the actual workaround. MySQL won’t merge query filters into the view definition, but we can modify the physical definition of the view in a way which provides a different means of providing filter conditions to the query.

Lets start by creating a parameter table. This table might be view specific or you could construct it in such a way that it can be shared between views. This example is somewhere in between those two extremes:

Notice that MySQL still won’t let us use the MERGE algorithm (1 warning):

There are two important things to consider here. First, notice the use of connection_id() in the view definition. This makes sure that the only rows examined in the params table are those which belong to this connection. When the table is inserted too (see below) the connection_id() function will be used to generate the value for the connection_id column. Also notice the restriction that v1.c1 = p.param1_val. This restricts the query to only those rows where v1.c1 matches the value in the params table.

Place parameters into the params table using the REPLACE statement:

When MySQL executes the new view it will be able to restrict the rows examined based on the param1_val value in the params table:

Notice the vastly different plan over the old one. We no longer examine millions of rows:

In review, I like views for their convenient encapsulation of SQL logic that would usually have to be repeated in SQL statements throughout the application otherwise. The convenience sometimes comes with high cost though, particularly when the TEMPTABLE algorithm is used. The technique outlined in this post can be used to make a trade off between increased query complexity for improved view performance.

Entry posted by Justin Swanhart |
6 comments

Add to: | | | |

Customers have always asked me to make NDB Cluster starts automatically upon startup of the servers. For the ones who know NDB Cluster, it is tricky to make it starts automatically. I know at least 2 sets of scripts to manage NDB startup, ndb-initializer and from Johan configurator www.severalnines.com. If all the nodes come up at about the same time, it is not too bad but what if one the critical node takes much longer to start because of an fsck on a large ext3 partition. Then, a startup script becomes a nightmare. Finally, if the box on which the script is supposed to run didn’t start at all. That’s a lot of rules to handle.

Since all aspects of HA interest me, I was recently reading the Pacemaker documentation and I realized that Pacemaker has all the logic required to manage NDB Cluster startup. Okay it might seems weird to control a cluster by cluster but if you think about it, this is, I think, the best solution.

The Linux-HA project has split the old Heartbeat-2 project in 2 parts, the clustering and communication layer, Heartbeat and the resources manager, Pacemaker. A key new features that has been added to Pacemaker recently, a Clone resources set, that allows an optional startup if only one of 2 similar resources starts. I use this feature to start the data nodes. If after a major outage, only one of the physical host where the data nodes are located comes up, the cluster will start. The other features of Pacemaker that I need are resource location rsc_location and resource ordering rsc_order.

Let’s start by the beginning. My NDB cluster is made of the following 3 nodes:

• testvirtbox: ndb_mgmd (10.2.2.139)
• test1: ndbd
• test2: ndbd

For the sake of simplicity, I am not considering the SQL nodes but given the framework, extending to SQL nodes is trivial. Installing Pacemaker and Heartbeat is very easy on Lucid Lynx, just do the following:

On other distributions, you might have to build from sources, look here for help.

There 2 minimal configuration files:

And then, Heartbeat can be started on all nodes with /etc/init.d/heartbeat start.

Next, since Pacemaker is used to start resources and not to manage them, we don’t need to define Stonith devices so (run on only one node):

A last before defining resources, since the Heartbeat cluster is asymmetrical, meaning resources will not be able to run anywhere, we must create an “Opt-In” cluster with (run on only one node):

At this point, we have a running cluster controlling nothing. The trick with NDB Cluster is that Heartbeat is required to start the resources but not to stop them. In order to achieve this behavior, I created fake resource scripts that can be fully controlled by Heartbeat but allowing the one way behavior I wanted.

With Pacemaker it is not longer required to manipulate the cib in xml format but for this post, xml offers a compact way of presenting the configuration. The first things we need to define are the resources. A very handy resource type for us is the anything resource which allow an arbitrary script or binary to be run. The resources section will look like:

Please note the ndbd resource is defined through the use of a clone set. The clone set will allow the cluster to start even if only one to the ndb node group is available. If you have multiple ndb node groups, you’ll need one clone set per node group. The ndb_mgmd nodes or eventual SQL nodes could have been handled the same way although for SQL nodes, ndb_waiter is very handy. Once the resources are defined, we need to setup the constraints which cover mandatory locations and ordering.

And… that’s it. For my part, I configured Pacemaker by dumping the cib in xml format, editing and reloading. In term of commands, it means:

Once NDB is started, you can even stop heartbeat, it is no longer required.

P.S.:

As suggested by Florian, here is the configuration in CLI format:

Entry posted by Yves Trudeau |
8 comments

Add to: | | | |