Home MySQL Blogs
MySQL

My MySQL tipsvalid-rss-rogers




Portable Tablespace in InnoDB I test it! PDF Print E-mail
Written by Marco Tusa   
Friday, 27 April 2012 20:58

 Overview

I have recently blog on the company site about portable Tablespaces

 

What I was saying is that is one of the things that could make us, people who work with MySQL/InnoDB happy.
This because it is a useful feature for administration and not just a "cool" thing to have.
My words were "This is a huge improvement that only people working daily with MySQL/InnoDB can understand,
so far it is still in the lab version but we all really hope to have it deliver with the new MySQL 5.6 GA"
From there I decide to try it right away, and to also try to extend the test.
So what I have done is to take the MySQL version from lab and start to play with tables and tablespaces.
Below my results and final considerations.
After having downloaded and install the Lab version
(root@localhost) [(none)]>status;
--------------
/home/mysql/templates/mysql-56p/bin/mysql  Ver 14.14 Distrib 5.6.6-labs-april-2012, for linux2.6 (i686) using  EditLine wrapper
 
Connection id:        1
Current database:    
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.6.6-labs-april-2012-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /home/mysql/instances/my56testm/mysql.sock
Uptime:            21 sec
 
Threads: 1  Questions: 5  Slow queries: 0  Opens: 16  Flush tables: 1  Open tables: 9  Queries per second avg: 0.238

 

Real work now and create a schema, tables and feed them.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
(root@localhost) [(none)]>use test_tablespace1
Database changed
(root@localhost) [test_tablespace1]>show tables;
Empty set (0.00 sec)
(root@localhost) [test_tablespace1]>create table tbtest(a int auto_increment PRIMARY KEY, 
b char(3) DEFAULT 'AAA', dat TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.24 sec) (root@localhost) [test_tablespace1]> (root@localhost) [test_tablespace1]>insert into tbtest (b) values ('aaa'),('bbb'),('ccc'),('dddd'); Query OK, 4 rows affected, 1 warning (0.05 sec) Records: 4 Duplicates: 0 Warnings: 1 (root@localhost) [test_tablespace1]>insert into tbtest (b) select b from tbtest; Query OK, 4 rows affected (0.05 sec) Records: 4 Duplicates: 0 Warnings: 0 .... (root@localhost) [test_tablespace1]>insert into tbtest (b) select b from tbtest; Query OK, 4096 rows affected (0.44 sec) Records: 4096 Duplicates: 0 Warnings: 0  

 

Now what we do have on fs ?
1
2
3
4
5
6
rwx------ 2 mysql mysql   4096 2012-04-27 14:42 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 14:42 tbtest.frm
-rw-rw---- 1 mysql mysql 360448 2012-04-27 14:45 tbtest.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1#

 

Time to RAISE THE COMMAND
1
2
(root@localhost) [test_tablespace1]>FLUSH TABLES tbtest WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

 

Check on the file system to see the new cfg file
1
2
3
4
5
6
7
drwx------ 2 mysql mysql   4096 2012-04-27 14:51 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql    440 2012-04-27 14:51 tbtest.cfg <--------------IS THERE!!!
-rw-rw---- 1 mysql mysql   8606 2012-04-27 14:42 tbtest.frm
-rw-rw---- 1 mysql mysql 360448 2012-04-27 14:45 tbtest.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1#

 

And given I am curious, I read insight:
we have there:
1
2
3
4
5
6
7
8
Name of the machine:     000033
Schema/Table name:       test_tablespace1/tbtest
Table definition:        a, b, dat
last ROW_ID:             DB_ROW_ID
Transaction ID:          DB_TRX_ID
Rollback pointer:        DB_ROLL_PTR
Primary kye and value :  PRIMARY a
Last transaction valuea: DB_TRX_ID DB_ROLL_PTR b dat

 

Now let us copy then remove it and see what happens.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# mkdir -p /home/mysql/backup
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp tbtest.* /home/mysql/backup/
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll
total 380
drwx------ 2 mysql mysql   4096 2012-04-27 14:59 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql    440 2012-04-27 14:51 tbtest.cfg
-rw-rw---- 1 mysql mysql   8606 2012-04-27 14:42 tbtest.frm
-rw-rw---- 1 mysql mysql 360448 2012-04-27 14:45 tbtest.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll /home/mysql/backup
total 376
drwxr-xr-x  2 root  root    4096 2012-04-27 14:59 ./
drwxrwxr-- 30 mysql mysql   4096 2012-04-27 14:59 ../
-rw-r-----  1 root  root     440 2012-04-27 14:59 tbtest.cfg
-rw-r-----  1 root  root    8606 2012-04-27 14:59 tbtest.frm
-rw-r-----  1 root  root  360448 2012-04-27 14:59 tbtest.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace
 

 

I have the files in the backup dir
Now is time to unlock the tables:
1
UNLOCK TABLES;

 

And check what happened:
1
2
3
4
5
6
drwx------ 2 mysql mysql   4096 2012-04-27 15:01 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 14:42 tbtest.frm
-rw-rw---- 1 mysql mysql 360448 2012-04-27 14:45 tbtest.ibd
 

 

No more cfg file.
Copy the table to something else and Drop the table:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
(root@localhost) [test_tablespace1]>create table tbtest2 select * from tbtest;
Query OK, 8192 rows affected (1.02 sec)
Records: 8192  Duplicates: 0  Warnings: 0
(root@localhost) [test_tablespace1]>
Drop tbtest:
(root@localhost) [test_tablespace1]>drop table tbtest;
Query OK, 0 rows affected (0.08 sec)
(root@localhost) [test_tablespace1]>show tables;
+----------------------------+
| Tables_in_test_tablespace1 |
+----------------------------+
| tbtest2                    |
+----------------------------+
1 row in set (0.00 sec)
 

 

Create a fake table tbtest
1
2
3
4
5
6
7
8
9
10
11
12
13
(root@localhost) [test_tablespace1]>create table tbtest(a char(1));
Query OK, 0 rows affected (0.23 sec)
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest DISCARD TABLESPACE;
Query OK, 0 rows affected (0.04 sec)
(root@localhost) [test_tablespace1]>
drwx------ 2 mysql mysql   4096 2012-04-27 15:05 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:02 tbtest2.frm
-rw-rw---- 1 mysql mysql 425984 2012-04-27 15:02 tbtest2.ibd
-rw-rw---- 1 mysql mysql   8554 2012-04-27 15:04 tbtest.frm
-rw-rw---- 1 mysql mysql  98304 2012-04-27 15:04 tbtest.ibt <----------- DETACHED tablespace
 

 

COPY back the files:
1
2
3
4
5
6
7
8
9
10
11
12
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp /home/mysql/backup/* .
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll
total 904
drwx------ 2 mysql mysql   4096 2012-04-27 15:06 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:02 tbtest2.frm
-rw-rw---- 1 mysql mysql 425984 2012-04-27 15:02 tbtest2.ibd
-rw-r----- 1 root  root     440 2012-04-27 15:06 tbtest.cfg
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:06 tbtest.frm
-rw-r----- 1 root  root  360448 2012-04-27 15:06 tbtest.ibd
-rw-rw---- 1 mysql mysql  98304 2012-04-27 15:04 tbtest.ibt

 

Fix permissions
1
2
3
4
5
6
7
8
9
10
11
12
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# chown mysql:mysql tbtest.*
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll
total 904
drwx------ 2 mysql mysql   4096 2012-04-27 15:06 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:02 tbtest2.frm
-rw-rw---- 1 mysql mysql 425984 2012-04-27 15:02 tbtest2.ibd
-rw-r----- 1 mysql mysql    440 2012-04-27 15:06 tbtest.cfg
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:06 tbtest.frm
-rw-r----- 1 mysql mysql 360448 2012-04-27 15:06 tbtest.ibd
-rw-rw---- 1 mysql mysql  98304 2012-04-27 15:04 tbtest.ibt

 

Remove fake table space and import back the old one
1
2
3
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# rm -f tbtest.ibt
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest IMPORT TABLESPACE;
ERROR 1801 (HY000): InnoDB: Number of columns don't match, table has 4 columns but the tablespace meta-data file has 6 columns

 

Error, well that was easy to understand, my fault I was suppose to create a fake table with the same structure not different.
Let me repeat the process of the fake table.
I did:
  • attach the ibt table space (I had it saved in backup)
  • drop it
  • recreate table as for initialal structure
  • detach it again.
  • copy back the old idb file and cfg
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest IMPORT TABLESPACE;
Query OK, 0 rows affected (2.93 sec)
(root@localhost) [test_tablespace1]>check table tbtest;
+-------------------------+-------+----------+----------+
| Table                   | Op    | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| test_tablespace1.tbtest | check | status   | OK       |
+-------------------------+-------+----------+----------+
1 row in set (0.02 sec)
(root@localhost) [test_tablespace1]>select count(*) from tbtest;
+----------+
| count(*) |
+----------+
|     8192 |
+----------+
1 row in set (0.00 sec)

 

WOW it works that could make me happy but given I am never happy (enough)
Let me try a crazy thing.
  • Create a fake table tbtest3
  • change the info in the cfg file and the table space filename
  • try to import it.
Let's go ...
First copy and modify the cfg file:
1
2
3
root@000033:/home/mysql/backup# cp tbtest.ibd tbtest3.ibd
root@000033:/home/mysql/backup# cp tbtest.cfg tbtest3.cfg
root@000033:/home/mysql/backup# vi tbtest3.cfg

 

Try to attach it:
1
2
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest3 IMPORT TABLESPACE;
ERROR 1712 (HY000): InnoDB: While reading table name: 'I/O error'.

 

No luck, it would have being to cool and easy.

Ok so Conclusions

====================
1) we can now do export - import of table spaces a little bit more easily
2) we cannot move tablespace cross schemas
3) we cannot attach a tablespace to another table
I understand that would be too cool and we must accept what we have, and it would be also nice to take a look at the code.

The full procedure in a thumb

========================================
Assuming you already have the table in place
  1. take the table creation to replicate the structure
    SHOW CREATE TABLE tbtest\G
  2. Lock the table to copy it
    FLUSH TABLE tbtest WITH READ LOCK;
  3. Copy somewhere the files DON'T forget the .cnf
  4. UNLOCK TABLES;
  5. Drop the table
  6. Create a fake table using the create statement stored before
  7. Detach the table
  8. ALTER TABLE tbtest DISCARD TABLESPACE;
  9. MOVE !!! the *.ibt file in a safe place
  10. Copy over the previous files from the backup directory
  11. CHECK PERMISSION!!
  12. Import back the table space
  13. ALTER TABLE tbtest IMPORT TABLESPACE;
  14. check table;

 

{joscommentenable}



Last Updated on Sunday, 18 August 2013 17:28
 
Some fun around history list PDF Print E-mail
Written by Marco Tusa   
Friday, 27 April 2012 18:06

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}

Last Updated on Sunday, 18 August 2013 17:29
 
MySQL 2012 Percona conference day 2 part 3 PDF Print E-mail
Written by Marco Tusa   
Monday, 16 April 2012 21:36

 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/

Подобного рода сделка между двумя белыми "Песни скачать свеча"считалась бы крупным мошенничеством, преступлением.

При этом он то и дело повторял, что они совершенно не схожи характерами, хотя, по словам Римо, один зануда придерживается на этот счет иного мнения.

Он-то как раз нормальным себя числил, а посему хотел смыться от голубоволоски и как минимум доесть остывающий эскалоп.

Эти мелочи мы "Игра говорящий попугай"оставим императору Смиту.

Воздух, который я вдыхал, напоминал пар, вылетающий из клапана паровой машины.

Приблизив ткань к лицу, он внимательно разглядывал ее на свет.

Все ответы на твои вопросы содержатся именно в них.

Он-то понимал могущество информации, помноженной на силу.

Они побежали за кислородом, и вскоре появились парамедики в оранжевых куртках с переносным баллоном.

И влепил две пули в мертвое лицо Ильи.

Потом он тоже женится на кореянке, и вскоре все забудут, что на славном знамени Дома Синанджу было когда-то позорное белое пятно.

Вот он-то "Футбол. Подробный путеводитель"мне и "Модный декупаж: пошаговые мастер-классы для начинающих"нужен, переулок, называемый "Руслан и Людмила"обычно Закоулком Смерти.

Сперва "Мифы Первой мировой"мы подверглись нападению.

Тянемся через "Книга для бизнес-тренера: технологии и искусство"внезапно похолодевшую "Крестоносцы"Карту.

Но теперь, когда родился "X-Polyglossum English. Полный аудиокурс английского: лексика + грамматика (3CDmp3)"наследник.

Я пришел бросить "Собачья жизнь и другие рассказы"тебе вызов, "Мальдивы: Путеводитель"Транто.

В свете факелов "Летят перелетные птицы"он действительно напоминал Снежного Старика.

Так "скачать песню самый лучший"как я теперь видел возможность возникновения именно такой ситуации.

Вот если бы "скачать книги по оригами"еще нашлась пиццерия где-нибудь поблизости.

Затем он, казалось, погрузился "скачать книгу буря мечей"в глубокий сон без сновидений, чтобы наконец проснуться в скромно обставленной "аська для телефона нокиа скачать"комнате, на диване, обитом зеленым атласом.

Многие люди чувствуют "симс история робинзонов скачать"себя бессильными и обесчеловеченными, будто они колесики машины.

Признаться, "скачать антивирусы для смартфона"я очень рассчитывал на "скачать фильм в качестве"то, что королева отправится вовремя.

Затем дядя медленно развел "зайти в аську не скачивая"руки в стороны, и линии извивающейся черноты "руки вверх ай яй яй скачать"последовали за ним, растекаясь в обоих направлениях из того участка Хаоса, что был "скачать песню кавказскую"валуном, прокладывая длинную темную траншею, в коей я узрел парадокс.

Генерал Блейд "Убить Ланселота" приехала, чтобы познакомиться с нашей веселой "Бианки Рассказы и сказки" шайкой.

Исцеляющие бактерии размножались в нижней части "Необыкновенное варенье Маша и Медведь" здания, в старом римском бассейне, "Окно в Европу" при реконструкции которого нашли множество старинных монет "Голубой ангел" и других предметов римской эпохи.

Кстати, "Грибы 15 заготовок на зиму" я хотел бы услышать ваше мнение по поводу "Загадки про героев любимых сказок и мультиков с развивающимися заданиями" происходящего.

Я не хочу, чтобы "Экономика переходного периода" Джей оставил какие-нибудь следы.

С самого начала Адам и Ева были "Париж и Северная Франция" единственными людьми "Финансовый инжиринг" на Земле.

Амнет дважды отвергал эту честь.

Last Updated on Tuesday, 02 July 2013 19:33
 
MySQL 2012 Percona conference day 2 part 2 PDF Print E-mail
Written by Marco Tusa   
Monday, 16 April 2012 21:34

 

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

Last Updated on Sunday, 18 August 2013 17:29
 
MySQL 2012 Percona conference day 2 part 1 PDF Print E-mail
Written by Marco Tusa   
Monday, 16 April 2012 21:05

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/

Last Updated on Sunday, 18 August 2013 17:30
 
«StartPrev12345678910NextEnd»

Page 5 of 10
 

Who's Online

We have 15 guests online