Why this article?

First of all because I was having fun in digging in the code.

Then I was reading a lot about the improvements we will have in MySQL 5.6, and of some already present in 5.5.

Most of them are well cover by people for sure more expert then me, so I read and read, but after a while I start to be also curious, and I start to read the code, and do tests.

I start to do comparison between versions, like 5.1 - 5.5. - 5.6

One of the things I was looking to was how the new Purge thread mechanism works and his implications.

I have to say that it seems working better then the previous versions, and the Dimitry blog (see reference) seems confirm that.

So again why the article? Because I think there are some traps here and there and I feel the need to write about them.

The worse behaviour is when using MySQL 5.5, and this is because in 5.5 we have an intermediate situation, where the purge is not fully rewrite as in 5.6, but also not bound to the main thread.

What is the history list?

MySQL uses (from Innodb 1.1 MySQL 5.5) 1 to 128 Rollback segments, each able to support 1023 transactions.

Each transaction is assigned to one of the rollback segments, and remains tied to that rollback segment for the duration.

This enhancement improves both scalability (higher number of concurrent transactions) and performance (less contention when different transactions access the rollback segments).

History list is tightly bound to undo log representing the number of Header Pages related to undo log segments, segments that are related to finalize transactions,commit or roll back.

That's it, History list represent the number of not yet flush segments in the undo log.

Old and New

Previously, the purge thread was directly controlled by the main thread in InnoDB causing serious side effects for description of which read ( http://dimitrik.free.fr/blog/archives/2010/04/mysql-performance-why-purge-thread-in-innodb.html).

The main change was to move out the purge thread and allow it to run it isolated, such that it will not impact other process.

The move was done in Innodb 1.1 (present in 5.1 and 5.5)

But that is not all, the new version of purge has a mechanism that allow it to be more or less aggressive, in relation to the increase or decrease of the History List length.

This option is enable in the 5.6 release and is taking the innodb_purge_threads as the "up to value" it can use to increase the number of threads for purging.

Different behaviour

What is quite obvious is that the behaviour of the new Purge mechanism, is quite different from the previous one, ie 5.0 (innoDB 1.0) or 5.1.

In the previous versions of InnoDB, we were educated to consider the History List something that should always be close to 0, not always but as soon as possible.

Frankly speaking that was always a little bit confuse, but as said the MANUAL, was suggesting in several place to keep it between reduced numbers:

I.e. (http://dev.mysql.com/doc/refman/5.5/en/innodb-multi-versioning.html)

"If you insert and delete rows in smallish batches at about the same rate in the table, the purge thread can start to lag behind and the table can grow bigger and bigger because of all the “dead” rows, making everything disk-bound and very slow In such a case, throttle new row operations, and allocate more resources to the purge thread by tuning the innodb_max_purge_lag system variable."

A classic scenario for such issue, is an application taking track of the activity on the network, that require to write huge number of small insert into the database.

From my tests I have seen an incredible number of entry in the history list in 5.5, that were not present in the previous InnoDB version, and that are not present again in 5.6.

The point is it could happen to have so many transactions, doing INSERT,UPDATE or DELETE that the History grows too much, and the un-flushed undo log as well. To prevent issues, we should tune the value of the innodb_max_purge_lag in order to allow InnoDB to complete the PURGE operations.

Innodb_max_purge_lag is the maximum number in history list we want to have, above which Innodb will start to apply an indiscriminate delay in pushing the operations.

the formula is quiet simple:

1

((purge_lag/innodb_max_purge_lag)×10)–5 milliseconds.

Or following the code we have:

float ratio = (float) trx_sys->rseg_history_len/ srv_max_purge_lag;
((ratio - .5) * 10000);
 

If we have a History list of 1200

and we have set innodb_max_purge_lag to 1000

result should be:

1
2

((1200/1000)X10)-5= 7 ms delay for operation. following the manual
((1200/100) -5) * 10000 = 7000 <-- Microsecond following the code

All match and delay will be 7 ms.

Also the max limit in previous version for the delay was of 4295 seconds!!

While in 5.5/5.6 we see a different way of managing the max number of seconds:

1
2
3
4

if (delay > srv_max_purge_lag_delay) {
    delay = srv_max_purge_lag_delay;
}

Where srv_max_purge_lag_delay max value is 10 seconds.

So the max delay, the worse case will be 10 seconds.

In the past as said we were use to see the History list going up and down (relatively) fast, so the force delay was playing his role efficiently.

At the same time, we knew that all operations in the Main threads where slowed down, so the forced delay, was something we had to leave with, or worse things could happen, like the need to perform an emergency page flush from the buffer pool, to allow REDO to recover space.

But something has changed...

... in better obviously... but we must be careful.

Better because now the purge thread works independently, and that it could scale, pending undo flushes do not slow down the other operations.

Also in 5.6, MySQL could be more or less aggressive in relation to the History list to purge.

Those operation remain something we should  monitor and tune, for two main reasons:

- space taken by undo log segments is till an issue, and now that the number is increase, it could be even worse.

- Setting a wrong value for innodb_max_purge_lag could kill our performance.

Let us start digging a bit.

First of all History list and purge log thread are still very bad documented in the InnoDB Status Report.

In 5.5 we can count on the History list information, number of transaction purge has being up to,  then the number of purged record up to, finally in 5.6 we have the generic state.

Not too much here, better info like number or running threads, real segments used (1-128), number of Header Pages in the segments, and dimension (at least pages as Percona does) would be nice to have.

Undo log is currently stored inside the system tablespace, this means that IT WILL NOT BE POSSIBLE to shrink the size of the table space, once undo log have taken huge amount of space.

That's it, the 80% or more of the size of a system table space is because the undo log, when using innodb_file_per_table. and this was already true when InnoDB was using a single set of segments (1023), now that it can scale up to 130944, and that it supports better more transactions, the size on disk can explode.

Some numbers to have better understanding,

History list 359585
insert/s 35649.67
pages to flush in the undo log 429126
Means in the undo log a total size of ~ 6.7GB

Considering that normal block size is 4K in file system each page is 4 operation, we will have 1,716,504 operation, assuming that each page will be sequential, this means 3 ms for Seek+half rotation then, 1 ms transfer for the first operation then 2ms for the remaining will be 12ms for each page delete on disk.

Meaning 5149.512 seconds (85 minutes)  at 6.7 Mb/s given the partial random write, to flush the whole.

Obviously this number changes in respect of the available cache and available spindles.

Also more threads more capacity in write, less time, so the option innodb_purge_threads is more then welcome.

Setting the right value for innodb_max_purge_lag

In setting this value we must keep into consideration the following:

- the value is the number of head pages representing an undo log relation to a running/executed transaction.

- Purge can apply only to complete transaction

- delay apply to all write operation inside InnoDB

Nowadays is not uncommon to have high number in history list in 5.5, in this case "just" 359,585 head pages, is then very important to correctly balance the delay point of start with the real load, like transactions/sec and the kind of operations are ongoing on the server.

To clarify, the relevance also in case of reads, not only of writes, let me cite:

<snip>

Running Very Long Transaction If you’re running very long transaction, be it even SELECT, Innodb will be unable to purge records for changes which are done after this transaction has started, in default REPEATABLE-READ isolation mode. This means very long transactions are very bad causing a lot of garbage to be accommodated in the database. It is not limited to undo slots. When we’re speaking about Long Transactions the time is a bad measure. Having transaction in read only database open for weeks does no harm, however if database has very high update rate, say 10K+ rows are modified every second even 5 minute transaction may be considered long as it will be enough to accumulate about 3 million of row changes.

<snip>

(Peter Z)

But what can really harm your the system is the dealy define by the purge lag to improve the flushing.

Assume we define it to innodb_max_purge_lag=200,000, and we do have the number of pending flush as for the above 359585.

Doing calculation as for the previous formula

((359585/200000)X10)-5= 12.97925 ms delay for operation

Hey that's not too bad, I will delay only 12.97925 ms to operation/Insert to help the purge.

But what is not clear is what an operation is for the delay, or more correctly where do the delay really apply?

Ready? Are you sit comfortable?

2) row0mysql.c

1
2
3
4
5
6
7
8
9
10
11
12

/*******************************************************************//**
Delays an INSERT, DELETE or UPDATE operation if the purge is lagging. */
static
void
row_mysql_delay_if_needed(void)
/*===========================*/
{
if (srv_dml_needed_delay) {
   os_thread_sleep(srv_dml_needed_delay);
 }
}
 

3)os0thread.c

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

/*****************************************************************//**
The thread sleeps at least the time given in microseconds. */
UNIV_INTERN
void
os_thread_sleep(
/*============*/
ulint tm) /*!< in: time in microseconds */
{
#ifdef __WIN__
Sleep((DWORD) tm / 1000);
#else
struct timeval t;
t.tv_sec = tm / 1000000;
t.tv_usec = tm % 1000000;
select(0, NULL, NULL, NULL, &t);
#endif
}

Do you get it?

delay is per ROW.

So assume you have a system checking connections status and traffic, collecting statistics every minute for your 100000 connected users, each usert generate at least 8 insert, plus a consolidation operation to get average 10 minutes each insert. Each insert per user taking, 0.002 second. All traffic manage by 300 threads.

100000 x 8 = 800000/300 = 2666 insert for transactions x 0.002ms each = 5.3sec to run all the operations.

Now what if we apply the delay given we have History list quite above as say before?

I have to sum the 12ms to the 0.002 which will give us 0.122 sec, which means 325 seconds (5.4 minutes) for each thread!!!!

Do not forget the consolidation process, who needs to run each 10 minutes.

So it has to process (1 reads for user per minute x 100000 users) x 10 minutes, split in 10 threads doing it by user id set, assuming each read per user will take 0.001 ms (given already in memory) and 0.002 ms for write.

Without delay we will have = 1,000,000 / 10 = 100,000 x 0.003 = 300sec (5 minutes) for thread.

With delay it will be for each operation 0.122 = 12200 seconds (203 minutes).

Last but not least the consolidation will collide with the inserts, causing possible increase of the delay because the REPEATBLE_READ, and another possible issue is... the consolidation will cause pages to remain in a dirty state for too long, possibly causing serious issue in the REDO log in case of need to free space.

I did push some number a little bit but not too much and only to make the scenario more clear.

Now just to remove some possible doubt:

Are you sure that it really push it by ROW?

let us create a working test.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE TABLE `City_test2` (
`ID` int(11) NOT NULL DEFAULT '0',
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
`satisfaction_grade` char(50) DEFAULT NULL,
`previous_satgrade` varchar(50) DEFAULT NULL,
KEY `satisfaction_grade` (`satisfaction_grade`,`previous_satgrade`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
insert into City_test2 select * from city;
mysql> truncate  table City_test2;insert into City_test2 select * from city limit 1000;
Query OK, 0 rows affected (0.24 sec)
Query OK, 1000 rows affected (0.10 sec)
Records: 1000  Duplicates: 0  Warnings: 0

So running the query we see the History going up and down but never to 0 as before.

but changing the innodb_max_purge_lag and setting it to a lower value then the history say history is 137, set the lag to 100

in theory there should be the following delay as for instructions:

((purge_lag/innodb_max_purge_lag)×10)–5 milliseconds.

result should be

((137/100)X10)-5=8.7 ms

1
2
3
4
5
6
7
8

mysql> set global innodb_max_purge_lag=100;
Query OK, 0 rows affected (0.00 sec)
 
mysql> truncate  table City_test2;insert into City_test2 select * from city limit 1000;
Query OK, 0 rows affected (0.10 sec)
Query OK, 1000 rows affected (7.40 sec)
Records: 1000  Duplicates: 0  Warnings: 0
 

Close enough from my point of view, and it increase at the increasing of the number of rows.

Changing :

innodb_purge_batch_size

innodb_rollback_segments

innodb_purge_threads

Will change how the purge will work but not the delay, innodb_max_purge_lag is the only possible parameter to use, and is not enough.

Conclusions

The new mechanism for the purge is much more flexible, and capable to scale. The fact that is now separate from the main thread reduce a lot the negative effects.

Never the less the capacity that the undo log purge have now, has also possible risks, huge pending flushes means possible huge space on disk and/or huge delay.

The delay behaviour is not new and it was already present MySQL 5.0, but the new capacity bring it to a higher level of risk, specially in 5.5.

In 5.6 the purging thread is much more efficient and I was having really hard time to do get huge history list, but when I get it I had same behaviour.

Whenever we need to tune the purge lag settings, the value needs to be set not in relation to the history list, but in relation to the maximum acceptable delay in the inserts as cumulative value.

References

http://www.mysqlperformanceblog.com/2011/01/12/innodb-undo-segment-siz-and-transaction-isolation/

http://www.mysqlperformanceblog.com/2010/06/10/reasons-for-run-away-main-innodb-tablespace/

http://dimitrik.free.fr/blog/archives/2010/04/mysql-performance-why-purge-thread-in-innodb.html

http://www.mysqlperformanceblog.com/2011/06/09/aligning-io-on-a-hard-disk-raid-the-benchmarks/

http://blogs.innodb.com/wp/2011/04/mysql-5-6-multi-threaded-purge/

http://mysqldump.azundris.com/feeds/categories/1-Strangeness.rss

{joscommentenable}

 MySQL Cluster Performance Tuning

-------------------------------------------

In this session we will look at different tuning aspects of MySQL Cluster.

As well as going through performance tuning basics in MySQL Cluster, we will look closely at the new parameters and status variables of MySQL Cluster 7.2 to determine issues with e.g disk data performance and query (join) performance.

This was the last session I attend, and for me is alway a great pleasure to be at Johan presentations, for many reasons:

- he is probably the best cluster expert (in service delivery)

- he knows a lot of distinctiveness and insight that no one else knows

--------------------------------------------------------------------------

Speech taken by Johan "my Viking" Andersson

MySQL/Oracle has released a new version of MySQL cluster recently, and I had the opportunity to test a little bit but not really in depth as I would like.

But one of the aspect that I was looking for what the way how the "condition pushdown" was modified.

Johan confirm by tests (empiric is always more trustable then commercial announcements), the way it is working now.  NDBcluster returns the full result set, no sub set to MySQL and additional requests, so less roundtrip, and the relations by ID are resolved at local data node level.

Interesting the way it is also possible to partition data by Key associating that on the Data node, that would increase the locality of reference also if not implementing a real partition pruning.

Only negative note is that we still have the annoying problem with the MySQL SQL node/connection pool, it is still taking 1 slot out of the 253 available for each connection in the pool. This means that if I have 4 MySQL SQL nodes each allocating 10 connection in the pool, I will take out 40 connections from the 253, instead 4.

This cannot be a problem, but given I have done several MySQL cluster implementations, I can say that when MySQL is really used it needs to heave a huge number of Data nodes and MySQL nodes, resulting to a very high number of connections slot used.

References

http://www.severalnines.com/

Boost Your Replication Throughput with Parallel Apply, Prefetch, and Batching

------------------------------------------------------

Slave lag is the bane master/slave replication. This talk will explain why slave lag occurs and show you three important ways that Tungsten Replicator can banish it for MySQL slaves. Parallel apply uses multiple threads to execute slave transactions. Prefetch uses parallel threads to read ahead of the slave position and fetch pages that will be needed by the slave. Batching uses CSV files to load row updates in extremely large transactions that bypass SQL completely. We will explain each technique, show you how to use it, and provide performance numbers that illustrate the gain you can expect. We will round the talk out with a discussion of non-Tungsten tools that offer similar benefits. With these techniques in hand, you'll be well-prepared to attack any replication performance problem.

The talk taken by Robert Hodges with Stephane Giron, was as expected very interesting, and give to the audience a good insight abut how to implement Replicator efficiently.

I also think that at the current moment Continuent Replicator, is the only production ready solution that can be use for:

- Parallel replication by schema (or combination of them)

- Multi master one slave solution

- on process filtering and data processing

- Oracle to MySQL

- MySQL to oracle.

I enjoy it, and I have immediate plan to use the solution for solving current customer need.

I am in particular interested in the FILTER option, and to see how it can really become helpful when talking of data processing.

Finally a small note some times ago I publish a blog describing a dream about replication, my dream, and Rob was the only one of the mega-expert in the fields, who honors me with an answer and a good explanation why it could not work, at the current state of arts.

My blog

http://www.tusacentral.net/joomla/index.php/mysql-blogs/96-a-dream-on-mysql-parallel-replication

Rob clear and really appreciated explanation

http://scale-out-blog.blogspot.ca/2011/03/parallel-replication-using-shards-is.html

Using and benchmarking Galera in different architectures

=============================================

What I was interested most during the second day was again, synchronous replication and Replication solutions provide from Continuent.

The first I attend in the day was the Galera one, done Henrik and Alexey.

The presentation was going to talk about:

"We will present results from benchmarking a MySQL Galera cluster under various workloads and also compare them to how other MySQL high-availability approaches perform. We will also go through the different ways you can setup Galera, some of its architectures are unique among MySQL clustering solutions.

* MySQL Galera

** Synchronous multi-master clustering, what does it mean?

** Load balancing and other options

** WAN replication

** How split brain is handled

** How split brain is handled in WAN replication

* How does it perform?

** In memory workload

** Scale-out for writes - how is it possible?

** Disk bound workload

** WAN replication

** Parallel slave threads

** Allowing slave to replicate (commit) out-of-order

"

I know how passionate is Henrik when talking about Galera and I partially shares the feeling. I said partially because I am still not fully convinced about the numbers, but I am working on that.

Anyhow, a side the part related to bench marching, I have found interesting the combination of blocks and element for the HA solution.

Including redundant load balancer and use MySQL JDBC with Galera is a simple but efficient way to provide HA.

Also important we can finally drop the DRBD solution that has being for too long the only syncronous solution for MySQL. DRBD was forcing to have one PRIMARY (RW) node, and one SECONDARY completely useless.

I have also appreciated the honesty from Alexey about scaling.

Galera will not scale to infinite as some foul state, but it could have a decent number of nodes.

Now the limit of it is obviously to discover and calibrate against the real load pushed against the nodes, it cannot be define as an absolute abstract limit.

Interesting also how the Galera team is managing by "quorum" the server synchronization. In short having 3 nodes if one will not be able to access the other two but still get writes (split brain), at the moment of re-union, the other two will take over by "quorum".

Obvious and immediate problem is in case of having 3 datacenter one with 6 nodes and the others with 2 nodes each. If the DC with 6 nodes gets disconnected, the valid data will be in the 2 remaining data centre, but at the moment of reunion, the DC with 6 nodes will take the majority, and all data set will become invalid.

Alexey is working on a way to calculate the "weight" by proximity to fix this issue.

Honestly, I am not sure that Galera is production ready, but is for sure the most interesting and easy solution for simple write scale.

Reference for Galera at http://codership.com/