Home MySQL Blogs
MySQL

My MySQL tipsvalid-rss-rogers




What I think about the Percona Live conference 2013. PDF Print E-mail
Written by Marco Tusa   
Sunday, 28 April 2013 22:54

 

mysql-2013-6934_Small

No need to say that as many others I have enjoy this conference a lot.

For me was also a personal success because I was finally able to have my company sponsoring the event and bring new members of my team to the conference as speakers, obviously all their merit, but I was happy as responsible of the MySQL cluster in Pythian knowing how much have cost to them to be there.

 

We also had a lot of people at the community dinner, last head count I did was over 120 people. In this regard I have to clarify some misunderstanding and confusion we had there.

Pedro’s requests to us to have the last head count by Tuesday morning, given that I have closed the count from the comments in the web-site and from the emails around noon.  The number I pass to Pedro’s base on that was of 70-80 people, but we had additional people registering after that time and also last moment show, given that we have to manage additional 40 people that has to be located in different areas.  Quite obvious that I did not give clear enough instructions, I personally apology for that, I (we) will do better next year. I hope you had good time and good food also if a little bit detach from the other tables.

 

What about the conference?

My feeling is that this event consolidates what is the “core” of the MySQL community.

We have seen many companies providing the same service, one close to the other sitting and talking with positive spirit and attitude.  I have been personally chatting all the time with people from SkySQL, Percona and others, all of us with open and friendly attitude.

I have seen Oracle people participate to the conference (hurray!!!), as IOUG committee member I know very well the number of time we have said to Oracle to be there, and they were! This was good, period.

 

MySQL where are you going?

In relation of what is happening to MySQL, and where is that leading us, I have confirm my idea that nowadays we are not talking anymore of LAMP or Full stack, when we talk about MySQL.

What customers, companies and users are expecting is a more complex and articulate environment. Interaction between different blocks is now not an optional but a fact.

When we approach an existing environment or when we have to build a new one, we now think in term of hundreds of application servers, terabytes of data to store or to process, many different client platform to support and impressive amount of data to analyze for reporting.

 

Feel free to fill the boxes with the name of the product that you like most, but for sure you will not limit yourself to MySQL or LAMP.

Already today I have customers using MySQL, Oracle Database, MongoDB, Hadoop and more, all in one single connected environment.

 

Thinking in term of MySQL only when we think to product, service, monitoring or design is too limitative.

 

For instance a tools that monitor MySQL but do not catch his interaction with other element like Hadoop, is going to provide only part of the picture. That partial picture referring only to MySQL metrics will be close to be useless because it will not be able to provide all the require information needed to perform a valid analysis and eventually projection.

In other terms will cover the basic of the single block and will not help us to get the big picture. That is it, still useful to keep the block in decent state but will let you blind for what is going on in the whole context.

This is for many aspects true also for the products, each block, MySQL included, must become more and more flexible to exchange data with the others. This can be achieve developing specific interfaces, or by defining a common protocol/format of communication that is shared between the different blocks.

 

In MySQL universe (or MariaDB), this also means to keep consistency and to remain as open as possible to facilitate the creation of additional plug-in/engines.

But what really worries me, given also is “my” field, is “service”. Those environments require support, design and so on. We know very well how complex a MySQL environment could be, what about it when we start to have many other actors involve? What really scares me is the level of knowledge is required to cover all of them or just a segment.

I am convince that we will have to work around it, because users/customers/companies will ask us to provide the support for all the element in their architecture, actually it is already happening, and the real risk is to have or become generalist instead of high profile experts.

 

If you don’t do it, if you do not differentiate, the risk is to be isolate by the market, and yes be very smart on that specific area, but not able to understand the big picture, ergo useless.

On the other hand, trying to do too much could drives you (as company) to disperse the resource and have an average level, that is good but not Excellent with capital “E”.

The possible solution is to have a huge monster with hundred and hundred of people, and division per technology … and … well I have seen already several of them starting and die. Starting good with very high service quality and then become big, heavy and so slow that customers moves out.

 

No that is not the solution, solution reside in being able to balance correctly what you can do with your resources, and reasonable growth, and what not.

I am working in a multi technology company, and I know very well of what I am talking about when I say that balance is the key.  The future will need to see two things: one is the companies improving their capacity to cover more then just MySQL, the other is open the space to collaboration, company covering different technologies must start to interact more, and offer better service and results to the users/customers in a cooperative way.

That will allow the single company to remain focus on few things and keep a high level of expertise on the chosen areas. Working in a cooperative way is the key.

All this needs to happen, and require coordination.

 

Flexibility and coordination are the keywords for the future. The MySQL community have shown already how much energy it has, how strong it could be in difficult moment and how much we really care about our customer/users.


What I see for the future is us working all together gathering all the actors involve, and give life to a new ecosystem which will help to facilitate the evolution of the next generation of data and applications.

 

What about the Speeches

In term of talks I have to say I was expecting a little bit more, not from the speakers only (me included), but also from the product companies.

As said I think is time to move to the next step and I was expecting more talks about interactions between technologies.

 

I am not saying that we should not cover the base; we must do it, but having more talks on how MySQL and MongoDB coexist, or how we could help Terabytes of data to be process between A and B; well that would have be nice.

Not only as what we have now, but also what we are planning for the future, including new features and ideas for the development.

 

In this regards the only relevant speeches I have seen were, the ones done by my colleague Danil Zburivsky on Hadoop/MySQL , and the other about Json by Anders Karlsson during the MariaDB/SkySQL event.

Thanks guys you see the future, and shame on Marco that was thinking about it and could have done it but did not … may be for the next conference.

 

Said that, the level of the speeches was good, I have being talking with the people attending, most of them satisfy, but let us wait and see what the evaluations will reveal.

What I can say is that I really enjoy the tutorial on Xtradb and Galera done by Jay Janssen, that helps me to feel less alone in the Galera implementation adventure; and I regret to have miss the “InnoDB: A journey to the core” by Davi Arnaut  and Jeremy Cole. But I was presenting at the same slot, and would have not be nice for me to say to the people there, ok let us move all to the next room.

 

What about the Expo

WoooW, first time as sponsor and first time with a boot. A lot of talk, a lot of possible new friends and a better understanding on what we need to do to be more effective next time. T-shirts first!!! Lesson learned we bring to few … we could have cover all the bay area with LOVE YOUR DATA, we miss the target this year, we will not do the same the next one.

 

I think that this year we had a well-balanced expo, with less show, but more focus, I must also mention the presence outside the expo area of  Julian Cash (http://jceventphoto.com/index.html), which takes a lot of cool shot of most of us.

I know Julian was there also during other conferences but I never met him before. I did this year and was a great experience, I hate to takes photos but with Julian I was having so much fun that at the end I love it.

 

What about the Lightening Talks?

Another well establish event at MySQL conference, and every year we have fun. This year I have enjoy the Shlomi one, and absolutely AWSOME was the performance from the Tokutek team.

 

About the Lightening talks and just to confirm what Dave Apgar was saying in his really good presentation, shit happens and you never know when. I had place my video camera and register the WHOLE event, and guess what… my new SD card just failed, and nothing I mean NOTHING was there after. Next time I will come with TWO video cameras and will setup redundancy!

 

Announcements

Finally during the conference we had two very significant announcements.

The first one was the expected merge of MariaDB and SkySQL, nothing new, but it is good to see that SkySQL is defining his identity with more determination, but not only this merge is very important because all MariaDB users now have a clear referring point, that will hep them and the community to better adopt and improve MariaDB. Way to go guys well done!

 

The second one is about Tokutek (http://www.tokutek.com/), finally open source. I have tested it the first time 3 years ago, and was a very interesting technology, but hard to have implemented because customers where reluctant to go for non-open source code.

Just a note, I wrote open source, not free. Open source doesn’t mean free, and here the concept was very clear, customers were willing to “eventually” pay, but not for close code.

Tokutek move is not only smart because will allow the company to have substantial help from the community in identify issues, improve utilization and identify new trends, but it is smart also because remove the last philosophical barrier in the software adoption.

 

From the technical point of view, the presentations have shown a significant improvement in respect to the previous years, and I was very impress from the presentation done by Gerry Narvaja during the SkySQL/MariaDB event.

One thing is sure, I have customers that could take huge benefit from Tokutek and I will give a try right away starting next week.

 

Winner and looser

No doubt from my side, I was not even mentioning before because for me is a given.

On the 12 of March 2011 I have written this article http://www.tusacentral.net/joomla/index.php/mysql-blogs/96-a-dream-on-mysql-parallel-replication, which was my dream about replication. At the time of writing I was not aware/testing ANY software able to do what I was asking.

On the 23 November 2011 I wrote another article http://www.tusacentral.net/joomla/index.php/mysql-blogs/119-galera-on-red-hat-is-123-part-1, and that was my first approach with Galera.

On the 29 September 2012 I have presented the first results of a POC done on customer environment http://www.slideshare.net/marcotusa/scaling-with-sync-replication-2012.

Next week, I must implement another MySQL Cluster, base on Galera replication.

 

The winner for me is the Galera solution (http://www.codership.com/), whatever version you may like; from my side I have found that the Percona version is the more stable, and using the Severalnines tool to manage the cluster (http://www.severalnines.com/clustercontrol) is also helpful.

 

 

Who is the looser then?

All the ones that have believe MySQL was over on the 2010 (Oracle take over).

We have MySQL from Oracle, we have MariaDB from Monty, we have companies developing their storage engines and tools, we have a more complex ecosystem that is growing day by day.

No MySQL is not over at all.

One note only, whoever leads the development from any side, reminds that you MUST allow the community to use and develop code on top of yours, modify interfaces without documenting, or not be fully explicit on what to do, how to do, and which direction, well it is not fair.

Percona Live, MySQL Conference in Santa Clara was a great conference, done by great people. We can do better all of us, always, but what makes me feel good is that I know we will do better next year.

 

Last note…

Did not you miss some one? Did you as I did, feel as that something was not right? Was not a face missed?

I mean … yes! He! Baron Schwartz!!  Hey man we miss you!!! Or at list I miss you and your block notes during the presentations, come back ASAP.

 

Happy MySQL to everyone.

 

References

http://www.codership.com/

http://www.severalnines.com/

http://jceventphoto.com/index.html

https://www.percona.com/live/mysql-conference-2013/

http://www.tokutek.com/

http://www.skysql.com/

http://www.pythian.com/

https://vividcortex.com

And more ...

 

{joscommentenable}

Last Updated on Sunday, 18 August 2013 17:04
 
Amazon EC2 - RDS quick comparison PDF Print E-mail
Written by Marco Tusa   
Tuesday, 12 March 2013 00:51

What this is about

The following is a review of the real status about Amazon RDS in comparison with EC2.

The purpose is to have a better understanding of possible limitation in the platform usage, and what is a possible fit and what not.

 

 

Why

I did a first review an year ago for internal purpose, but now we are receiving the same questions over and over from different customers.

Given that and given a lot of things could happen in one year, I have decide to repeat the review and perform the tests once again.

What needs to be underline is that, I am doing this in consideration of a usage in PRODUCION, not as QA or development.

So my considerations are obviously focus on more demanding scenarios.

 

 

About EC2 and RDS.

Machine configuration

There are different ways that we can choose to start our EC2 or RDS, both have different cost and “virtual” physical characteristics, the list for both is below:

EC2

T1 Micro (t1.micro)      Free tier eligibleUp to 2 ECUs1 Core613 MiB
M1 Small (m1.small)1 ECU1 Core1.7 GiB
M1 Medium (m1.medium)2 ECUs1 Core3.7 GiB
M1 Large (m1.large)4 ECUs2 Cores7.5 GiB
M1 Extra Large (m1.xlarge)8 ECUs4 Cores15 GiB
M3 Extra Large (m3.xlarge)13 ECUs4 Cores15 GiB
M3 Double Extra Large (m3.2xlarge)26 ECUs8 Cores30 GiB
M2 High-Memory Extra Large (m2.xlarge)6.5 ECUs2 Cores17.1 GiB
M2 High-Memory Double Extra Large (m2.2xlarge)13 ECUs4 Cores34.2 GiB
M2 High-Memory Quadruple Extra Large (m2.4xlarge)26 ECUs8 Cores68.4 GiB
C1 High-CPU Medium (c1.medium)5 ECUs2 Cores1.7 GiB
C1 High-CPU Extra Large (c1.xlarge)20 ECUs8 Cores7 GiB 
High Storage Eight Extra Large (hs1.8xlarge)35 ECUs16 Cores117 GiB

RDS
Micro DB Instance: 630 MB memory, Up to 2 ECU (for short periodic bursts), 64-bit platform, Low I/O Capacity
Small DB Instance: 1.7 GB memory, 1 ECU (1 virtual core with 1 ECU), 64-bit platform, Moderate I/O Capacity
Medium DB Instance: 3.75 GB memory, 2 ECU (1 virtual core with 2 ECU), 64-bit platform, Moderate I/O Capacity
Large DB Instance: 7.5 GB memory, 4 ECUs (2 virtual cores with 2 ECUs each), 64-bit platform, High I/O Capacity
Extra Large DB Instance: 15 GB of memory, 8 ECUs (4 virtual cores with 2 ECUs each), 64-bit platform, High I/O Capacity
High-Memory Extra Large DB Instance 17.1 GB memory, 6.5 ECU (2 virtual cores with 3.25 ECUs each), 64-bit platform, High I/O Capacity
High-Memory Double Extra Large DB Instance: 34 GB of memory, 13 ECUs (4 virtual cores with 3,25 ECUs each), 64-bit platform, 
High I/O CapacityHigh-Memory Quadruple Extra Large DB Instance: 68 GB of memory, 26 ECUs (8 virtual cores with 3.25 ECUs each), 64-bit platform, High I/O Capacity

Embedded features

EC2 imply that we do by ourselves the installation, setting up and maintenance of our system and Database software, but for RDS, Amazon provide few “features” that is important to keep in mind and have in mind for later discussions.

 

The most relevant are:

Pre-configured Parameters – Amazon RDS DB Instances are pre-configured with a sensible set of parameters and settings appropriate for the DB Instance class we select.

Monitoring and Metrics – Amazon RDS provides Amazon CloudWatch metrics for your DB Instance deployments at no additional charge. You can use the AWS Management Console to view key operational metrics for your DB Instance deployments, including compute/memory/storage capacity utilization, I/O activity, and DB Instance connections.

Automated Backups – Turned on by default, the automated backup feature of Amazon RDS enables point-in-time recovery for your DB Instance.

DB Snapshots – DB Snapshots are user-initiated backups of your DB Instance.

Multi-Availability Zone (Multi-AZ) Deployments – Amazon RDS Multi-AZ deployments provide enhanced availability and durability for Database (DB) Instances, making them a natural fit for production database workloads.

When we provision a Multi-AZ DB Instance, Amazon RDS automatically creates a primary DB instance and synchronously replicates the data to a standby instance in a different Availability Zone (AZ).

Each AZ runs on its own physically distinct, independent infrastructure, in case of an infrastructure failure (for example, instance crash, storage failure, or network disruption), Amazon RDS performs an automatic failover to the standby so that you can resume database operations as soon as the failover is complete.

Read Replicas – This replication feature makes it easy to elastically scale out beyond the capacity constraints of a single DB Instance for read-heavy database workloads. Amazon RDS uses MySQL’s native replication to propagate changes made to a source DB Instance to any associated Read Replicas.

 

Storage

As shown before in RDS we cannot do too much regarding the storage, we can just choose between different instances, and if we want to have provisioned IOPS.

On EC2 we obviously have both, but we also can choose how to define and use our storage solution.

 

MySQL configuration

Amazon presents to us the Pre-configured parameters as a cool “feature” but this is just one side of the coin. The other side is that we cannot really adjust some of the critical parameters for MySQL, or that their values are not as define in standard MySQL.

The parameters in discussion are:

binlog_format | STATEMENT expire_logs_days | 0 
(calc)| innodb_buffer_pool_size | 3921674240 
innodb_doublewrite | ON 
innodb_file_format_max | Antelope 
innodb_locks_unsafe_for_binlog | OFF 
innodb_log_file_size | 134217728 
innodb_log_files_in_group | 2 
innodb_log_group_home_dir | /rdsdbdata/log/innodb (Max 300)
innodb_open_files | 300 
max_binlog_size | 134217728 (max 4294967295)
max_join_size | 4294967295 
open_files_limit | 65535

Most concerning are, the binlog format, InnoDB Log related ones.

 

Multi-AZ implementation

From the architectural point of view, I do not have a clear way of HOW the Multi-AZ is implemented, and I am really interested in discovering how in Amazon they have achieved the declared Synchronous replication.

I am just guessing here but some base replication using DRBD Primary/Secondary seems the most probable. What could be concerning here is the protocol level use for such replication, and level of block transmission acknowledge, given a full protocol C will be probably to expensive, also if the ONLY really safe in the case of DRBD usage. But given I don’t have clear if the solution is really using it, let me just say it will be good to have better insight.

 

Standard replication

We cannot use standard replication in RDS, we need to rely on Read-Replicas, or not use replication at all. The only solution is to use external solution like Continuent Tungsten (http://scale-out-blog.blogspot.ca/2013/01/replicating-from-mysql-to-amazon-rds.html).

It is important to note that RDS replication between master and Read-replica is using the STATEMENT binlog format, and it cannot be change, as direct consequence we do have inefficient replication between master and replicas for all non-deterministic statements, and in case of mixed transactions between storage engines.

 

Tests done

The test performed where not too intensive, and I was mainly focus on identify what will be the safe limit of usage/load for RDS in comparison to an EC2 instance properly set.

As such I have choose to use the Large Instance set for both EC2 and RDS, with 2 virtual CPU 7.5GB virtual RAM, High I/O capacity for RDS.

For EC2 the only difference will reside in the fact I perform the tests using 1 EBS for the data directory in one case, and a raid5 of 4 EBS in the other.

Also in regards of the MySQL configuration I have “standardize” the configuration of the different instance using the same parameters.

Only differences was that I was not using SSL in the MySQL EC2 instance, while it cannot be turn off in RDS because Amazon security is relying on it.

The test was using a variable number of concurrent threads:

Writing on 5 main tables and on 5 child table.

Read on main table joining 4 tables to main, filtering the results by IN clause in on test, and by RANGE in another.

 

The structures of the tables are the following:

 

mysql> DESCRIBE tbtest1; 
 
 FIELD         | Type         | NULL | KEY | DEFAULT           | Extra 
 autoInc       | bigint(11)   | NO   | PRI | NULL              | AUTO_INCREMENT 
 a             | int(11)      | NO   | MUL | NULL              | 
 uuid          | char(36)     | NO   | MUL | NULL              |  
 b             | varchar(100) | NO   |     | NULL              | 
 c             | char(200)    | NO   |     | NULL              | 
 counter       | bigint(20)   | YES  |     | NULL              | 
 time          | timestamp    | NO   |     | CURRENT_TIMESTAMP | ON UPDATE 
 partitionid   | int(11)      | NO   |     | 0                 | 
 strrecordtype | char(3)      | YES  |     | NULL              | 
 

 

 

 

 

 
 mysql> DESCRIBE tbtest_child1; 
 
 FIELD        | Type         | NULL | KEY | DEFAULT           | Extra | 
 a            | int(11)      | NO   | PRI | NULL              | 
 bb           | int(11)      | NO   | PRI | NULL              | AUTO_INCREMENT 
 partitionid  | int(11)      | NO   |     | 0                 | 
 stroperation | varchar(254) | YES  |     | NULL              | 
 time         | timestamp    | NO   |     | CURRENT_TIMESTAMP | ON UPDATE

 

 

The filling factor for each table after the initial write was:

Table    tbtest1    total    1046    least    745    bytes per char: 3
Table    tbtest2    total    1046    least    745    bytes per char: 3
Table    tbtest3    total    1046    least    745    bytes per char: 3
Table    tbtest4    total    1046    least    745    bytes per char: 3
Table    tbtest5    total    1046    least    745    bytes per char: 3

Table    tbtest_child1    total    779    least    648    bytes per char: 3
Table    tbtest_child2    total    779    least    648    bytes per char: 3
Table    tbtest_child3    total    779    least    648    bytes per char: 3
Table    tbtest_child4    total    779    least    648    bytes per char: 3

 

Finally the total size of the data set was of 20Gb.

 

The inserts were using batch approach of 50 inserts per Insert command for all the platforms.

Below the summary of the tests to run

oltp 5 + 4 table write 4 -> 32

oltp 5 + 4 table read (IN) 4 -> 32

oltp 5 + 4 table read (RANGE) 4 -> 32

oltp 5 + 4 table write/read(IN) 4 -> 32


Results

Results for write using 4 to 32 concurrent threads

 

Write Execution time

(High value is bad)

executiontime_write

As the graph clearly shows, the behavior of the RDS and EC2 with one EBS is quite similar, while the EC2 running a RAID of EBS is maintaining good response time and scales in writes, the other two have a collapse point at 16 Threads, after which performance are becoming seriously affected.

 

Rows inserted

(High values is good)

rows_write

 

Consistently the number of Rows inserted in a defined period of time, see again the EC2 with RAID5 performing in the optimal way in relation to the other two.

During this test the performance loss starts at 8 threads, for EC2 solutions, while for the RDS solution it is with the increase of concurrency that we immediately see the performance degradation.

 

 

Select Execution time with IN

(High value is bad)

executiontime_read_in

 

Using the select with IN given the high efficiency of the IN approach, and the reduce number of reads that require to be executed on disk, all the instance maintain a good level of performance.

 

Rows reads with IN

(High value is good)

rows_read_in

 

In this case all the instances are consistently performing, but the EC2 with RAID solution can serve a larger amount of requests, almost 1/3 larger then of the RDS.

 

 

Select Execution time with RANGE

(High value is bad)

executiontime_read_range

 

In the case of range selects and heavy access on disks, the RDS and EC2 with 1 EBS, are absolutely not able to perform at the same level of the RAID solution. This quite obviously related to the amount of data needs to be read from disks, and the limitation existing in RDS and 1 EBS solutions.

 

 

Rows reads with RANGE

(High value is good)

rows_read_range


The volume test confirms and highlights the different behavior between EC2 RAID and the others, at 32 concurrent threads the RDS solution tends to collapse, while the EC2 RAID is serving successfully the traffic also if with less efficiency.


Select Execution time with mix of SELECT and INSERT

(High value is bad)

executiontime_read_write

 

Rows reads with mix of SELECT and INSERT

(High value is good)

rows_read_write

 

In a mix workload, I had unexpected results, with EC2 1 EBS behaving very badly when working with more then 16 threads, this given the I/O contention and possible RDS optimizations, implemented by Amazon to prevent single EBS problems.

Except that the RDS and the EC2 with RAID behave as I was expecting, with EC2 able to manage a larger volume of traffic, and the Inserts limiting the number of reads, as expected.

 

 

Conclusions

The comparison between RDS and EC2, cover several areas, from performance to High Availability.

 

My conviction is that RDS is not implementing a solid and trustable HA solution given the not clear way synchronous replication is implemented.

RDS is not applying correct best practices for replication given the use of STATEMENT format and the limitation existing in the replication management.

Finally RDS is not really efficient in managing large volume of traffic, or applications with a large number of highly concurrent threads.

 

Never the less it could be a temporary solution for very basic utilization in application that do not have demanding requirements.

RDS can probably further optimize, but I am sure it will never be enough to consider RDS production ready.

 

EC2 is more flexible and allow better tuning and control of the platform, multiple HA solutions and full control of replication and MySQL in general. All these define the significant difference with RDS, and draw the line for the right use of the tool.

Also there is not a difference in what kind of MySQL distribution we will implement, given that the source of the issue is on the platform.

 

My final advice is to use RDS for development or as temporary solution in a start-up, but it should not be use in the case of critical system or consolidated mature application, which require high available, scalable database support.

 

Reference

http://aws.amazon.com/rds/

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/RDSFAQ.MultiAZ.html

http://scale-out-blog.blogspot.ca/2013/01/replicating-from-mysql-to-amazon-rds.html

http://www.mysqlperformanceblog.com/2011/08/04/mysql-performance-on-ec2ebs-versus-rds/

 

 

{joscommentenable}

Last Updated on Sunday, 18 August 2013 17:09
 
MySQL and drop packet PDF Print E-mail
Written by Marco Tusa   
Friday, 08 February 2013 03:04

 

Overview

Last night a customer call us because was having issue on the server and loss of performance on the MySQL server.
When I join the bridge I ask to the customer a quick report of what was his experience and concern so far.

 

Luckily all the participants were technically skilled and team was compose by, SAs, DBAs, Team leader, so I was able to have a good overview in short time.
Mainly there were two fronts, one was the problem on the server in the network layer, the other in MySQL that was not able to manage efficiently the number of thread opening requests.

The machine has a single NIC, Storage attach by fibre channel, 8CPU hyper threading, 64GB RAM and finally heavy usage of NFS.

The same server was in the past using the volumes on the NFS also for MySQL, but now everything was moved to the attached storage.

 

As said the issue was that NIC was reporting drop packet wand MySQL was having issue to manage the number of threads, the last ones were between 200 - to 1000 connection requests.
As active threads the server was managing 200-300 threads, which was not enough.

 

I start reviewing the server and NIC issue, talking with the SAs they report that the NIC Receive buffer, was already set to maximum of 4096k.

 

So starting the investigation from there I review the back_log net.ipv4.tcp_max_syn_backlog, and all the other parameters related to TCP buffer:
1
2
3
4
5
6
7
8
9
CURRENT TCP buffer setting 
------------------------------
net.ipv4.tcp_mtu_probing = 0
net.core.rmem_max = 131071
net.core.wmem_max = 131071
net.ipv4.tcp_rmem = 4096    87380    4194304
net.ipv4.tcp_wmem = 4096    16384    4194304
------------------------------
 

 

The settings were misconfigured given that the tcp value cannot override the core values.


As such the settings for the tcp auto tuning were invalid for the max limit.

 

Given those values were not correct for a machine supporting high traffic I suggest:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Suggested TCP buffer settings
------------------------------
#TCP max buffer size
net.core.rmem_max = 16777216 
net.core.wmem_max = 16777216 
 
#Linux auto-tuning TCP buffer 
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
 
#length of the processor input queue
net.core.netdev_max_backlog = 30000
 
#default congestion control is htcp 
net.ipv4.tcp_congestion_control=htcp
 

 

About htcp see the reference to the document explaining in details the algorithm.
From the mysql point, I review few parameters that would have a direct relation with the Threads.
1
2
3
4
5
6
7
MySQL change
-----------------------------------------------
back_log                        = 1024
thread_cache_size               = 512
thread_stack                    = 256K
wait_timeout                    = 600
 

 

I decide to set value of backlog as the maximum queue we have seen, move the value of the thread_cache_size from 8 to 1/4 of the max number of connection,
then few correction given wait_timout was set as default and thread_stack was set as for 32bit machines.

 

When we apply the values, I was expecting to see the drop packet issue solve, instead MySQL was managing much better the incoming connection, but the drop packet were still there.
Also the bad network behaviour, was preventing to the data to flow as it was suppose to be.

 

We then focus on why this was happening reviewing all the changes applied.

 

After few investigations and researches the customer, realize that the value for the receive window on the NIC, was not really applied,
because also if it is declared as dynamic value by Cisco, the facto it require a reboot of the machines.

 

We reboot it, and the NIC was now working properly. Data was floating fine with no drop packet.
MySQL was managing the incoming thread efficiently, but I notice, after the server warm-up, that the performance were still not optimal.

 

So doing some other tuning, I set thread_cache_size to 1024 paring the back_log number,
at this point MySQL was managing the incoming new request very efficiently, we had a jump to of Threads_cached to 850 with obvious flotation between 8 threads up to the maximum,
Threads_created the same and then just a bit larger then the maximum number of created connections, and finally Threads_running jump from 200 - 300 to 600 - 700.

 

Conclusion

The drop packet is a sign of insufficient buffer, either from the NIC or the TCP, remember to review the parameters related.
"Surprising" also if the thread creation process was alway declare as "easy" and "light" a more aggressive thread settings makes MySQL act more properly.

Reference

{joscommentenable}

Last Updated on Sunday, 18 August 2013 17:14
 
Some thought on our work, from a different perspective PDF Print E-mail
Written by Marco Tusa   
Tuesday, 22 January 2013 06:39

Introduction

Recently I had some free spare time that I used to read, think, process and analyze few ideas and work on my own projects.

That was great for me because I had the chance to develop new tools and to review few concept related to work. So I had the chance to focus on the ideas behind the procedures or "how-to", including reviewing what I am doing at work, from different angles and prospective.

One of the different or better to say modified prospective, was the outcome of a mental process started with a reading.

Reading that I initially considered a waste of money, time and mind effort.

This because the topic discussed, and the way the topic was presented, is something that I had the chance to study when I was in school starting from secondary. In fact the topic of "Critical thinking" is, or I should say was, included in the school programs in our learning path associate to "Logic", "Grammar" and "Philosophy".

So when I read the books I commit the crime of assumptiveness, feeling also bore while reading, until the moment the book was covering the topic related to "Decision Making". In that chapter the writer was underlining how easy is for us to be caught in trap by our own knowledge and ideas.

I did stop to read, close the book went doing something else, and then I try to empty my mind. Only at that point I realize I was not reading at all, better my eyes were, some part of my brain was, but my mind and my attention were not, this because I had categorize the book from the initial chapters in the erroneous way.

So I take a glass of wine, had some time, good music and open the book again from the start. This time the book was presenting me a different scenario and prospective, it drives my thought through several mind paths and brings me to review some assumptions. At this point I was able to make some parallels with what is our/mine day-to-day activity in life and work. It was funny for me to discover how some personal best practices, fit perfectly in a well categorize universal model.

There was no magic, that is true, but what was good and interesting, was how "School Training" can forge your way of doing in an instinctive way, but also how the instinctive action path, can be transform and express in few, clear, universal and simple to read steps.

In particular I saw a good parallel with two critical areas of our work; the credibility of the source and the process of the decision-making.

The rest of the writing is a summary, a go through few points I have identify as relevant, and that I have see covering some critical grey areas.

I am aware this is just a small part of the picture, and as usual I am open to discussion and comments, and I will be more then happy if that will happen, actually this will mean that I have reach my target.

 

Credibility of the Source

In our work, as well as in many others, having a good credibility is not a plus but a must. Being credible as a company or as a single person, is not coming from free, and is a process that could takes years to build and days to get destroy.

The credibility is not only the result of "best practices" or "how to", but is also the result of a correct approach and process in what we do, how we do it and how we decide to do things (see after the decision-making section).

Whenever a customer will come to us, for an advice or help, he will ask to himself some questions, questions that we should answer or the customer will redirect his attention to someone else.

Those questions are:

  • Do they (us) have the relevant expertise (experience, knowledge and if needed formal qualification)?
  • Do they have the ability to observe accurately (eyesight, hearing, proximity to event, absence of distraction, appropriate instruments, skills in using instrument)?
  • Does their reputation suggest that they are reliable?
  • Do they have any interest or possible bias?
  • Are them claiming and providing evidences of knowledge about MY context?
  • Are they providing direct expertise?
  • Is they level of expertise base on direct experience?
  • Is what they say support by evidence and logic pattern?
  • Are other sources consistent?

Answer to all the above, as said, is not something you can achieve with limited or superficial effort, it instead require an extensive and constant shift in mentality, and require some well define ideas and behaviour. My interpretation is the following:

  • Always be "super partes", also avoid as much as possible to follow ephemeral trends, like the use and abuse of the "magic" term of the year, often used by others to show they capacity to be on the "trend". Unfortunately be there very often means doing without knowing. Be more conservative and analytic is the right things to do when responsible of other people.
  • Be under constant training and education, perform extensive tests, and provide public evidence of our conclusion and analysis. Publish few but focus blogs.
  • Avoid blog about everything, and avoid generalization, that will create more noise and confusion, yes you will be there, but as chatter not as an expert.
  • When claiming about something, provide evidence and a well-documented reasoning path to support your claim.
  • Always put the claim in a clear defined context, and if possible and available include the references to others reasoning and/or similar evidence and sources.
  • Whenever possible try to be or use a direct source, like provide the test you have done yourself, or review and repeat the tests done by others to validate them.
  • Never use other source material as yours, instead document them and contextualize them providing credit to the source. Again double check other source conclusion and provide evidence of your process.
  • Whatever evidence or conclusion you will provide, it needs to have an exact match with the discussed topic, avoid generalization. Assumption can be good only if supported by good and documented reasoning.
  • Do not rush, this is not a race, do not send out an answer or a comment without having the time to think on it. If possible, review it several times, and cover your reasoning also with others, this to be sure you have cover all the possible areas of uncertainly, and if you still see them, declare them.

I will be more then happy to have discussion on the above points, and if possible to extend them including more helpful suggestions.

 

Decision-making process

As mention previously the other point is related on how we take our own decisions, and how we evaluate other people conclusion/reasoning/motivation.

In our work we are constantly call to take decisions, some of them are very simple ones, and we can take action with very limited thinking, but others could be much more complex and could require significant effort from our side, more time and processing to efficiently evaluate what will be the right decision.

Unfortunately very often we are affected by at least one of the following bad behaviour:

  • We do not give us enough time to think.
  • We see a possible fit on a though and we remain there not giving us the space to evolve.
  • We do not process all the possible alternatives to/of the problem, and we do not develop more then one solution.
  • We do not evolve our solution/action into a clear path of possible consequences.
  • We often skip to take more information because the "time" issue (or others) and miss relevant part of the puzzle.
  • We sometime forget what is relevant for us and how much this can impact on our judgment.
  • We are emotionally involved and it affects the process and the decisions.
  • We just do what our Boss is saying to do.
  • Other recommendations influence us without applying analytical thinking.

Going through the above points, trying to clarify and to see what we can do to prevent them.

  • Time, time is relevant and often we have to take some decision fast, but thinking require time, time to take information, time to analyze them, time for the reasoning. The process should not be compromise by our rush, because results will be affected and our decisions can be imprecise (if lucky) or completely wrong, not only it could happen to take a wrong decision, but when this happen because rush, we do not have a good reasoning to support and justify our mistake, in short there will not be a learning lesson, only the mistake.
  • How often have we feel in love with our ideas, and not ready to divorce from them? Too often we must admit it, instead we should be able to go beyond and process all the possible options. We should keep our mind open and listen to other external suggestion but always applying analytical process.
  • When I was a kid I learn that "each action include/imply a reaction". Before performing any action, before apply what we think is correct in our decision, we should carefully think "What will happen next?". We should analyze the actions, and have a good level of understanding of what will be the path of events that our actions will generate, and be ready for possible unexpected bad behaviour.
  • In our job information about what is going on is everything. We should never stop to dig more, and get better understanding. Never consider the outcome of some tools/script enough for our analysis, taking their results as given without applying an analytical review. We should stop only when we are really confident that we cannot get more relevant information, and if possible we should ask to a trusted source to compare what we got, to see if we have miss anything.
  • Sometime we forget that we have personal commitments, those could affect our judgment. For example, if we are fully focus on open source, it could become almost automatic for us to skip the evaluation of a non-open source solution. Or if we are Linux fundamentalist just to have to approach windows server, could drive us to have a not objective approach to the problem. Again we must keep our mind open and process the problem by analytical steps, not considering the preconceptions in our thinking, but be able to filter them out and have an objective mind process.
  • How many times we have found that customer so annoying? His reiterate questions where lacking of any sense, and is not some time his behaviour to be so close to be offensive? On the other hand this other customer is really nice, he gives you a lot of credit, he has a good understanding of the effort you are doing to keep his environment in good shape. Can you honestly say that you have always gives to the two the same "time and attention"? This is a fact, it is in the human nature, to be more careful and nice comes easier with the ones that are nice with us. But this is not correct, we should always apply the same time/effort/reasoning independently to the customer behaviour. The reasoning is the point not our feeling. Understanding it and be able to mange it is a matter of be more or less professional.
  • Do not follow the boss or others advice, direction blindly. We must listen carefully to anyone, we should evaluate what they have to say and objectively extract whatever is good from their suggestion or recommendation. But never accept it without our own thinking/reasoning; also it will be appropriate to share with them our process step by step, before getting to the conclusion. This will help us in learning from each other work, and will provide advantage to everyone also reducing the chance of mistakes.

Summarizing we should ask ourselves the following before, during and after having done our reasoning for a decision:

  1. What make this decision necessary? What is the objective?
  2. What I am going to recommend, and on what basis?
  3. What other possible alternatives exists, which one is the more realistic feasible, which one the more innovative?
  4. What are the possible consequence of my decision, and how likely they are going to happen?
  5. If this consequence will happen what will be the relevance and how we can manage them?
  6. Comparing different solution, which one will be the best to mitigate negative effect?
  7. How I can transform my decision into an action reducing to minimum the risk of bad behaviour or mistakes?

Conclusion

In the above sections, I was just trying to report in a concise and easy way, what is part of a more complex topic. I am aware that most of us do the right thing, just doing it right, but I am also confident that reporting black on white those simple points could help us to avoid mistakes, and if possible to define process and checklists that other people less conscientious then us, can follow to make their work behaviour more trustable.

 

Reference

Ennis , R.H Critical thinking Prentice Hall 1996

Fisher A The logic of real arguments Cambridge University press 1988

Fisher A Critical Thinking – an Introduction Cambridge University press 2001

{joscommentenable}

Last Updated on Sunday, 18 August 2013 17:15
 
Xtrabackup for Dummy PDF Print E-mail
Written by Marco Tusa   
Tuesday, 22 January 2013 04:48

or summary  for lazy guys on how to use it...

 

I know that a lot has being written around Xtrabackup, and good documentation can be found on the Percona web site.

Anyhow I had to write a summary and clear procedure for my teams, so I choose to share those with all, given it could provide benefit to all community.

 

Each major topic is associate to a checklist, that need to be follow to prevent mistakes.

Overview

Xtrabackup is a hot backup tool, that allow you to perform backup on InnoDB with very limited impact on the running transactions/operations.
In order to do this xtrabackup, copy the IBD files AND it takes information out from the REDO log, from a starting point X.
This information needs to be then apply to the datafiles, before restarting the MySQL server on restore.
In short the Backup operation is compose by 2 main phases:
  1. Copy of the file
  2. Copy of the delta modified from REDO log.
Another phase is the "prepare" phase where the REDO log modifications, must apply.
This phase can be done as soon as the backup is complete, if the files are not STREAM (we will see it later), or must be done on Restore if STREAM was use.
Xtrabackup is compose by two main parts, the innobackupex script wrapper, and the xtrabackup.
the Xtrabackup binary has three different version:
- xtrabackup
- xtrabackup_51
- xtrabackup_55
Binary change in respect to the mysql binary version and are automatically selected from innobackupex as follow:
MySQL 5.0.* - xtrabackup_51
MySQL 5.1.* - xtrabackup_51
MySQL 5.1.* with InnoDB plugin - xtrabackup
Percona Server >= 11.0 - xtrabackup
MySQL 5.5.* - xtrabackup_55
It is important to note that while the backup of InnoDB tables is taken with minimal impact, the backup of MyISAM still require a full tables lock.
The full process can be describe as follow:
  1. check connection to MySQL
  2. start the xtrabackup as child process
  3. wait untill xtrabackup suspend the process
  4. connect to mysql
  5. if sever is a slave wait for replication to catch-up
  6. if server is a master it returns right away
  7. flush tables and acquire a read lock (unless explicitly ask in the settings to DO NOT get lock)
  8. write slave information
  9. perform physical write of the files
  10. resume xtrabackup process
  11. unlock tables
  12. close connection to mysql
  13. copy last LRU information
  14. write backup status report

User and Grants

Backup user SHOULD not be a common user or a DBA user but it should be one created for this operation as below:
1
2
3
4
5
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'bckuser123';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'backup'@'localhost';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
 

 

How to invoke the Xtrabackup in standard easy way.

This is the easier way to take a FULL backup using Xtrabackup.

/usr/bin/innobackupex-1.5.1 --defaults-file=<path> --slave-info --user=<username> --password=<secret>   /path/to/destination/backup/folder

ie

/usr/bin/innobackupex-1.5.1 --defaults-file=/home/mysql/instances/mtest1/my.cnf --slave-info --user=backup --password=bckuser123   /home/mysql/backup/

This will produce a full uncompress backup.
root@mysqlt3:/home/mysql/backup/2012_12_21_1300/2012-12-21_14-32-02# ll
total 200088
drwxr-xr-x 15 root root      4096 Dec 21 14:41 ./
drwxr-xr-x  3 root root      4096 Dec 21 14:46 ../
-rw-r--r--  1 root root       263 Dec 21 14:32 backup-my.cnf
-rw-r-----  1 root root 104857600 Dec 21 14:32 ibdata1
drwxr-xr-x  2 root root      4096 Dec 21 14:41 mysql/
drwxr-xr-x  2 root root      4096 Dec 21 14:41 performance_schema/
drwx------  2 root root      4096 Dec 21 14:41 security/
drwx------  2 root root      4096 Dec 21 14:41 test/
drwx------  2 root root      4096 Dec 21 14:41 test_audit/
drwx------  2 root root      4096 Dec 21 14:41 timstaging/
drwx------  2 root root      4096 Dec 21 14:41 timtags/
drwxr-xr-x  2 root root      4096 Dec 21 14:41 world/
-rw-r--r--  1 root root        13 Dec 21 14:41 xtrabackup_binary
-rw-r--r--  1 root root        26 Dec 21 14:41 xtrabackup_binlog_info
-rw-r-----  1 root root        85 Dec 21 14:41 xtrabackup_checkpoints
-rw-r-----  1 root root  99912192 Dec 21 14:41 xtrabackup_logfile
-rw-r--r--  1 root root        53 Dec 21 14:41 xtrabackup_slave_info
backup-my.cnf <--------------- very essential version of the my.cnf with innodb information
ibdata1<---------------------- Main tablespace
mysql/ <----------------------
world/<----------------------- DBs ... with files copy in
xtrabackup_binary <----------- contains the name of the xtrabackup binary used
xtrabackup_binlog_info <------ Binary log information (name/position)
xtrabackup_checkpoints <------ Information regarding the LSN position and range
xtrabackup_logfile <---------- File containing the delta of the modifications
xtrabackup_slave_info <------- Slave information (if slave)
 

 

In this case given it is NOT using streaming and it is not compress, you can prepare the file right away:

innobackupex --use-memory=1G --apply-log /home/mysql/backup/2012_12_21_1300/2012-12-21_14-32-02

 

After few operations you will see:
121221 15:57:04  InnoDB: Waiting for the background threads to start
121221 15:57:05 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 30312932364

 

[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 213145807, file name /home/mysql/instances/mtest1/binlog.000011
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
121221 15:57:05  InnoDB: Starting shutdown...
121221 15:57:09  InnoDB: Shutdown completed; log sequence number 30312932364
121221 15:57:09  innobackupex: completed OK!

 

When done the files needs to be put back in the right place:

innobackupex --defaults-file=/home/mysql/instances/mtest1/my.cnf --copy-back `pwd`

 

Note be sure that destination is empty both DATA and IB_LOGS.
If you can just rename the directories, and create new ones.
When copy is over:
innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/home/mysql/backup/2012_12_21_1300/2012-12-21_14-32-02'
innobackupex: back to original InnoDB data directory '/home/mysql/instances/mtest1/data'
innobackupex: Copying '/home/mysql/backup/2012_12_21_1300/2012-12-21_14-32-02/ibdata1' to '/home/mysql/instances/mtest1/data/ibdata1'
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/home/mysql/backup/2012_12_21_1300/2012-12-21_14-32-02'
innobackupex: back to original InnoDB log directory '/home/mysql/logs/mtest1/innodblog'
innobackupex: Finished copying back files.
121221 16:41:38  innobackupex: completed OK!

 

Modify the permission on the data directory:

chown -R mysql:mysql /home/mysql/instances/mtest1;

 

Then restart MySQL, you will see that mysql will recreate the iblogs as well, given we have removed them but this is ok because we have already apply all the changes.
121221 16:44:08 mysqld_safe Starting mysqld daemon with databases from /home/mysql/instances/mtest1/data
121221 16:44:09 [Note] Plugin 'FEDERATED' is disabled.
121221 16:44:09 InnoDB: The InnoDB memory heap is disabled
121221 16:44:09 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
121221 16:44:09 InnoDB: Compressed tables use zlib 1.2.3
121221 16:44:09 InnoDB: Using Linux native AIO
121221 16:44:09 InnoDB: Initializing buffer pool, size = 1.0G
121221 16:44:09 InnoDB: Completed initialization of buffer pool
121221 16:44:09  InnoDB: Log file /home/mysql/logs/mtest1/innodblog/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /home/mysql/logs/mtest1/innodblog/ib_logfile0 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
...
121221 16:44:15 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
121221 16:44:15  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 213145807, file name /home/mysql/instances/mtest1/binlog.000011
121221 16:44:17  InnoDB: Waiting for the background threads to start
21221 16:44:18 InnoDB: 1.1.8 started; log sequence number 30312933388
121221 16:44:18 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3310
121221 16:44:18 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
121221 16:44:18 [Note] Server socket created on IP: '0.0.0.0'.
121221 16:44:18 [Note] Event Scheduler: Loaded 0 events
121221 16:44:18 [Note] /home/mysql/templates/mysql-55p/bin/mysqld: ready for connections.
Version: '5.5.27-log'  socket: '/home/mysql/instances/mtest1/mysql.sock'  port: 3310  MySQL Community Server (GPL)

 

Checking the content we will have all data back:
1
2
3
4
5
6
7
8
9
+--------------+--------+--------+----------+----------+-----------+----------+
| TABLE_SCHEMA | ENGINE | TABLES | ROWS     | DATA (M) | INDEX (M) | TOTAL(M) |
+--------------+--------+--------+----------+----------+-----------+----------+
| test         | InnoDB |     51 |  9023205 |  5843.14 |   1314.62 |  7157.76 |
| test         | NULL   |     51 |  9023205 |  5843.14 |   1314.62 |  7157.76 |
| test_audit   | InnoDB |      9 |  1211381 |   658.54 |    230.54 |   889.09 |
| test_audit   | NULL   |      9 |  1211381 |   658.54 |    230.54 |   889.09 |
| NULL         | NULL   |     61 | 10234586 |  6501.68 |   1545.17 |  8046.86 |
+--------------+--------+--------+----------+----------+-----------+----------+

7 rows in set (6.92 sec)


 

How to BACKUP the Xtrabackup Using compression

One of the pain in backup compression is the compression process, that could take very long time and could be very inefficient.
We have choose to use pigz which is a tool for Parallel Implementation of gzip.
This combine with the --stream option of xtrabackup generate very compact backup files in shorter time.
The only thing to remember is that YOU CANNOT apply the logs on streaming, so you MUST do it after in the Restore phase.
So given our database as before now we have to be sure that pigz is in place:
#pigz --version
pigz 2.1.6

 

If this is returned (or another version) all ok.
Otherwise  you need to install it:

apt-get install pigz (debian)

yum install pigz (centos)

To execute the backup we will just change the last part of our command as follow:

./innobackupex-1.5.1 --defaults-file=/home/mysql/instances/mtest1/my.cnf --slave-info --user=backup --password=bckuser123  --stream=tar ./ | pigz -p4 - > /home/mysql/backup/2012_12_21_1300/full_mtest1.tar.gz

Once the copy is over you will have a file like this:
drwxr-xr-x  3 root root 4.0K Dec 21 17:08 ./
drwxr-xr-x  3 root root 4.0K Dec 21 14:16 ../
drwxr-xr-x 15 root root 4.0K Dec 21 16:31 2012-12-21_14-32-02/
-rw-r--r--  1 root root 737M Dec 21 17:18 full_mtest1.tar.gz <-------------

 

The whole process on a descktop machine takes:
121221 17:09:52  innobackupex-1.5.1: Starting mysql with options:  --defaults-file='/home/mysql/instances/mtest1/my.cnf' --password=xxxxxxxx --user='backup' --unbuffered --
121221 17:18:29  innobackupex-1.5.1: completed OK!

 

Less then 10 minutes for 8GB data, not excellent but it was running on a vere low level machine.
The file is then ready to be archive, or in our case to be copy over the slave for recovery.

How to RESTORE using Xtrabackup from stream

Once we have the file on the target machine we have to expand it.
Very important her is to use the  -i option, this because otherwise the blocks of zeros in archive  will be read as EOF (End Of File), and your set of files will be a mess.
So the string will be something like:

tar -i -xzf full_mtest1.tar.gz

Again after the operation we will have:
-rw-r--r-- 1 root root       269 Dec 21 17:10 backup-my.cnf
-rw-rw---- 1 root root 104857600 Dec 21 17:12 ibdata1
drwxr-xr-x 2 root root      4096 Dec 21 17:40 mysql
drwxr-xr-x 2 root root      4096 Dec 21 17:39 performance_schema
drwxr-xr-x 2 root root      4096 Dec 21 17:39 security
drwxr-xr-x 2 root root      4096 Dec 21 17:39 test
drwxr-xr-x 2 root root      4096 Dec 21 17:39 test_audit
drwxr-xr-x 2 root root      4096 Dec 21 17:39 timstaging
drwxr-xr-x 2 root root      4096 Dec 21 17:39 timtags
drwxr-xr-x 2 root root      4096 Dec 21 17:39 world
-rw-r--r-- 1 root root        13 Dec 21 17:18 xtrabackup_binary
-rw-r--r-- 1 root root        26 Dec 21 17:18 xtrabackup_binlog_info
-rw-rw---- 1 root root        85 Dec 21 17:18 xtrabackup_checkpoints
-rw-rw---- 1 root root 282056704 Dec 21 17:18 xtrabackup_logfile
-rw-r--r-- 1 root root        53 Dec 21 17:18 xtrabackup_slave_info

 

Note this time the information about the binary logs will be CRUCIAL.
Move or delete the old data directory and ib_log.
We have to apply the logs so assuming we have our file set in /home/mysql/recovery:

innobackupex --use-memory=1G --apply-log /home/mysql/recovery

 

Check CAREFULLY the ouput of the process if everithing is fine you will have something like this:
121221 17:52:17  InnoDB: Starting shutdown...
121221 17:52:21  InnoDB: Shutdown completed; log sequence number 30595333132
121221 17:52:21  innobackupex: completed OK!
 

 

Otherwise you must investigate, the most common issues are:
  • forgot -i in the expand
  • space on disk
When copy is over:
121221 18:04:48  innobackupex: completed OK!

 

Change the permissions

chown -R mysql:mysql /home/mysql/instances/mtestslave

 

Start the mysql server.
Again check the mysql error log:
121221 18:06:38 mysqld_safe Starting mysqld daemon with databases from /home/mysql/instances/mtestslave/data
121221 18:06:39 [Note] Plugin 'FEDERATED' is disabled.
121221 18:06:39 InnoDB: The InnoDB memory heap is disabled
121221 18:06:39 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
121221 18:06:39 InnoDB: Compressed tables use zlib 1.2.3
121221 18:06:39 InnoDB: Using Linux native AIO
121221 18:06:39 InnoDB: Initializing buffer pool, size = 1.0G
121221 18:06:39 InnoDB: Completed initialization of buffer pool
121221 18:06:39  InnoDB: Log file /home/mysql/logs/mtestslave/innodblog/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /home/mysql/logs/mtestslave/innodblog/ib_logfile0 size to 10 MB
InnoDB: Database physically writes the file full: wait...
121221 18:06:40 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
121221 18:06:40  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 150497896, file name /home/mysql/instances/mtest1/binlog.000001
121221 18:06:42  InnoDB: Waiting for the background threads to start
121221 18:06:43 InnoDB: 1.1.8 started; log sequence number 30595333644
121221 18:06:43 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3311
121221 18:06:43 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
121221 18:06:43 [Note] Server socket created on IP: '0.0.0.0'.
121221 18:06:43 [Note] Event Scheduler: Loaded 0 events
121221 18:06:43 [Note] /home/mysql/templates/mysql-55p/bin/mysqld: ready for connections.
Version: '5.5.27-log'  socket: '/home/mysql/instances/mtestslave/mysql.sock'  port: 3311  MySQL Community Server (GPL)
 

 

And now is time to log in check the data set AND fix replication.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
root@localhost [(none)]> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| security           |
| test               |
| test_audit         |
| world              |
+--------------------+
13 rows in set (0.04 sec)
root@localhost [(none)]> SELECT TABLE_SCHEMA, ENGINE, COUNT(1) as 'TABLES', sum(TABLE_ROWS) as 'ROWS',
TRUNCATE(sum(DATA_LENGTH)/pow(1024,2),2) as 'DATA (M)',
TRUNCATE(sum(INDEX_LENGTH)/pow(1024,2),2) as 'INDEX (M)',
TRUNCATE((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/pow(1024,2),2) AS 'TOTAL(M)'
FROM information_schema.tables       WHERE TABLE_SCHEMA <> 'information_schema'
AND TABLE_SCHEMA <> 'mysql'     AND TABLE_SCHEMA not like 'avail%'
AND TABLE_SCHEMA <> 'maatkit'            AND TABLE_TYPE = 'BASE TABLE'
GROUP BY TABLE_SCHEMA, ENGINE      WITH ROLLUP;
+--------------------+--------------------+--------+----------+----------+-----------+----------+
| TABLE_SCHEMA       | ENGINE             | TABLES | ROWS     | DATA (M) | INDEX (M) | TOTAL(M) |
+--------------------+--------------------+--------+----------+----------+-----------+----------+
| performance_schema | PERFORMANCE_SCHEMA |     17 |    23014 |     0.00 |      0.00 |     0.00 |
| performance_schema | NULL               |     17 |    23014 |     0.00 |      0.00 |     0.00 |
| security           | InnoDB             |      1 |  1454967 |   170.73 |     60.75 |   231.48 |
| security           | NULL               |      1 |  1454967 |   170.73 |     60.75 |   231.48 |
| test               | InnoDB             |     51 |  9298913 |  6058.39 |   1347.78 |  7406.17 |
| test               | NULL               |     51 |  9298913 |  6058.39 |   1347.78 |  7406.17 |
| test_audit         | InnoDB             |      9 |  1189343 |   685.56 |    236.56 |   922.12 |
| test_audit         | NULL               |      9 |  1189343 |   685.56 |    236.56 |   922.12 |
| world              | MyISAM             |      3 |     5302 |     0.35 |      0.06 |     0.42 |
| world              | NULL               |      3 |     5302 |     0.35 |      0.06 |     0.42 |
| NULL               | NULL               |    227 | 11971539 |  6916.70 |   1645.74 |  8562.44 |
+--------------------+--------------------+--------+----------+----------+-----------+----------+
 

 

So far so good.
Now is time to modify the slave.
First take the current status:
1
2
3
root@localhost [(none)]> SHOW slave STATUS\G
Empty SET (0.00 sec)
root@localhost [(none)]>

 

Ok nothing, good.
Assign the master AND the log file and position from xtrabackup_binlog_info.
1
2
cat xtrabackup_binlog_info
binlog.000001    150497896

 

Prepare the command as:

change master to master_host='192.168.0.3', master_port=3310,master_user='replica',master_password='xxxx', master_log_file='binlog.000001',master_log_pos=150497896;

Check again:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
root@localhost [(none)]> SHOW slave STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.3
Master_User: replica
Master_Port: 3310
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 150497896
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 150497896
Relay_Log_Space: 107
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row IN SET (0.00 sec)
root@localhost [(none)]>


Perfect start the slave:

slave start;

AND CHECK again:


root@localhost [(none)]> SHOW slave STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting FOR master TO send event
Master_Host: 192.168.0.3
Master_User: replica
Master_Port: 3310
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 206843593
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 22872
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 150520518
Relay_Log_Space: 56346103
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 30
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3310
1 row IN SET (0.00 sec)

 

Ok we have some delay as expected by all is running as it should.
Our server is up and running.

How to do INCREMENTAL BACKUP with Xtrabackup 

 

Incremental backup works in a different way.
To understand it correctly we need to remember that InnoDB pages have a sequence number LSN (Log Sequence Number), given that, each incremental backup starts from the previous stored LSN.

 

Incremental backup must have a first FULL Backup as base, then each following incremental, will be stored in a different directory (by timestamp).

To restore the incremental backup the full set of incremental, from the BASE to the last point in time, need to be apply.
So if we have the Full Backup done on Monday, and incremental are taken every day, if we need to restore the full set on Friday, we must apply the logs on the BASE (Monday) following the chronological order, Monday (base), then Tuesday, Wednesday, Thursday, Friday.

Only at that point we will have the full set of data, that can replace the one we were having on the server.

To remember that this works only for InnoDB, other storage engines like MyISAM are copy in full every time.

Let this work without compression

/opt/percona-xtrabackup-2.0.4/bin/innobackupex-1.5.1 --defaults-file=/home/mysql/instances/mtest1/my.cnf --slave-info --user=backup --password=bckuser123   /home/mysql/backup/

The new directory 2013-01-10_13-07-24 is the BASE.

Checking the files inside we can check the LSN position:
root@tusacentral03:/home/mysql/backup/2013-01-10_13-07-24# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 32473279827
last_lsn = 32473279827
Last LSN is 32473279827

 

As exercise let us do TWO incremental backup starting from this base, but first add some data...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
root@localhost [test]> SHOW processlist;
+-----+--------+---------------------------+------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| Id  | User   | Host                      | db   | Command | Time | State  | Info                                                                                                 |
+-----+--------+---------------------------+------+---------+------+--------+------------------------------------------------------------------------------------------------------+
|  87 | root   | localhost                 | test | Query   |    0 | NULL   | SHOW processlist                                                                                     |
|  92 | stress | tusacentral01.LOCAL:37293 | test | Sleep   |    0 |        | NULL                                                                                                 |
|  94 | stress | tusacentral01.LOCAL:37296 | test | Query   |    0 | UPDATE | INSERT INTO tbtest30 (uuid,a,b,c,counter,partitionid,strrecordtype) VALUES(UUID(),731188002,"hd rsg  |
|  95 | root   | localhost:37295           | test | Query   |    0 | update | INSERT INTO test_audit.tbtest4 values(NEW.autoInc,NEW.a,NEW.uuid,NEW.b,NEW.c,NEW.counter,NEW.time,NE |
|  96 | stress | tusacentral01.local:37298 | test | Query   |    0 | NULL   | COMMIT                                                                                               |
|  97 | root   | localhost:37299           | test | Query   |    0 | update | INSERT INTO test_audit.tbtest4 values(NEW.autoInc,NEW.a,NEW.uuid,NEW.b,NEW.c,NEW.counter,NEW.time,NE |
|  98 | stress | tusacentral01.local:37300 | test | Query   |    0 | update | insert INTO tbtest15 (uuid,a,b,c,counter,partitionid,strrecordtype) VALUES(UUID(),598854171,"usfcrgl |
|  99 | root   | localhost:37301           | test | Query   |    0 | UPDATE | INSERT INTO test_audit.tbtest4 VALUES(NEW.autoInc,NEW.a,NEW.uuid,NEW.b,NEW.c,NEW.counter,NEW.time,NE |
| 100 | stress | tusacentral01.LOCAL:37302 | test | Query   |    0 | UPDATE | INSERT INTO tbtest15 (uuid,a,b,c,counter,partitionid,strrecordtype) VALUES(UUID(),22723485,"vno ehhr |
| 101 | stress | tusacentral01.local:37303 | test | Query   |    0 | update | insert INTO tbtest1 (uuid,a,b,c,counter,partitionid,strrecordtype) VALUES(UUID(),991063177,"nqdcogeu |
| 102 | stress | tusacentral01.LOCAL:37304 | test | Query   |    0 | UPDATE | INSERT INTO tbtest1 (uuid,a,b,c,counter,partitionid,strrecordtype) VALUES(UUID(),86481207,"sdfabnogn |
| 103 | stress | tusacentral01.local:37305 | test | Query   |    0 | NULL   | COMMIT                                                                                               |
+-----+--------+---------------------------+------+---------+------+--------+------------------------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)

 

Now let us create the first incremental backup:

/opt/percona-xtrabackup-2.0.4/bin/innobackupex-1.5.1 --incremental --incremental-basedir=/home/mysql/backup/2013-01-10_13-07-24 --defaults-file=/home/mysql/instances/mtest1/my.cnf --slave-info --user=backup --password=bckuser123   /home/mysql/backup/

 

After all the process is complete, we will have TWO directories:
total 20
drwxr-xr-x  5 root  root  4096 Jan 10 13:30 ./
drwxr-xr-x 18 mysql mysql 4096 Dec 28 12:16 ../
drwxr-xr-x 15 root  root  4096 Jan 10 13:17 2013-01-10_13-07-24/
drwxr-xr-x 15 root  root  4096 Jan 10 13:34 2013-01-10_13-30-43/ <-------- the last one is the Incremental
 

 

I was inserting data mainly on the TEST schema, and as you can see test is the one that HAS more data in, which represent the DELTA:
root@tusacentral03:/home/mysql/backup/2013-01-10_13-30-43# du -sh *
4.0K    backup-my.cnf
4.5M    ibdata1.delta
4.0K    ibdata1.meta
1.5M    mysql
212K    performance_schema
18M        security <---------------------------------
1.2G    test <---------------------------------
173M    test_audit <---------------------------
488K    world
4.0K    xtrabackup_binary
4.0K    xtrabackup_binlog_info
4.0K    xtrabackup_checkpoints
4.0K    xtrabackup_logfile
4.0K    xtrabackup_slave_info
 

 

On top of the usual files, in the schema directory and per table I will find some additional inormations inside the tablexyz.ibd.meta file
root@tusacentral03:/home/mysql/backup/2013-01-10_13-30-43/test# cat tbtest1.ibd.meta
page_size = 16384
zip_size = 0
space_id = 1983

 

Checking the file xtrabackup_checkpoints you will see the delta related to LSN
root@tusacentral03:/home/mysql/backup/2013-01-10_13-30-43# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 32473279827 <------------ starting point
to_lsn = 33215076229   <------------ End point
last_lsn = 33215076229

 

Let us add other data and take another incremeental.
root@tusacentral03:/opt/percona-xtrabackup-2.0.4/bin# /opt/percona-xtrabackup-2.0.4/bin/innobackupex-1.5.1 --incremental \
--incremental-basedir=/home/mysql/backup/2013-01-10_13-30-43/ \
--defaults-file=/home/mysql/instances/mtest1/my.cnf  \
--slave-info --user=backup --password=bckuser123   /home/mysql/backup/

 

There is a HUGE difference from the previous command, the BASEDIR change, and must be the las incremental.
Given this is not always possible it is good practices when working with scripts to store the LAST LSN in the xtrabackup_checkpoints and pass it as parameter with:

--incremental-lsn=xyz

This is the more elegant and flexible way.

Ok NOW we have 3 Directory:
drwxr-xr-x 15 root  root  4096 Jan 10 13:17 2013-01-10_13-07-24/
drwxr-xr-x 15 root  root  4096 Jan 10 13:34 2013-01-10_13-30-43/ <--------- First incremental
drwxr-xr-x 15 root  root  4096 Jan 10 14:02 2013-01-10_13-57-04/ <--------- Second incremental

 

To have a full backup we have now to rebuild the set from the BASE then First incremental then Second Incremental, to do so we need to apply the changes but NOT the rollback operation.
If we forgot and perform ALSO the rollback, we will not be able to continue applying the incremental backups.
To do so there are two ways, explicit and implicit:
  • Explicit --apply-log --redo-only
  • Implicit --apply-log-only
I like the Explicit because you know exactly what you pass also if this can be more verbose, so my commands will be:

/opt/percona-xtrabackup-2.0.4/bin/innobackupex-1.5.1 --use-memory=1G --apply-log --redo-only /home/mysql/restore/2013-01-10_13-07-24

/opt/percona-xtrabackup-2.0.4/bin/innobackupex-1.5.1 --use-memory=1G --apply-log --redo-only /home/mysql/restore/2013-01-10_13-07-24 --incremental-dir=/home/mysql/restore/2013-01-10_13-30-43

/opt/percona-xtrabackup-2.0.4/bin/innobackupex-1.5.1 --use-memory=1G --apply-log --redo-only /home/mysql/restore/2013-01-10_13-07-24 --incremental-dir=/home/mysql/restore/2013-01-10_13-57-04

Once done the BASE directory will contains the up to date information including the binary log position:
root@tusacentral03:/home/mysql/restore/2013-01-10_13-07-24# cat xtrabackup_binlog_info
binlog.000005    275195253     <------------ Original from Base
root@tusacentral03:/home/mysql/restore/2013-01-10_13-07-24# cat ../../backup/2013-01-10_13-07-24/xtrabackup_binlog_info
binlog.000003    322056528     <------------ Up to date from incremental
 

 

It is now time to have all finalize it:

/opt/percona-xtrabackup-2.0.4/bin/innobackupex-1.5.1 --use-memory=1G --apply-log /home/mysql/restore/2013-01-10_13-07-24

 

At this point we just need to copy back on the slave the content of BASE directory /home/mysql/restore/2013-01-10_13-07-24, and change the permissions.
[root@tusacentral07 data]# scp -R 
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 .0.3:/home/mysql/restore/2013-01-10_13-07-24/*  .
[root@tusacentral07 data]# sudo chown -R mysql:mysql .
 

 

At this point if this is a slave we just to setup the replication from the last binlog and positon as usual, otherwise all done and we can restart the server.

 

Incremental with compression

To perform the incremental + compression the process is the same but instead tar we need to use xbstream, for documentation I have add the --incremental-lsn with the value from the latest backup,
at this point add some data, and take the backup again.
Given I don't have the previous set of FULL + Incremental1 + Incremental2 UNPREPARED, I will take again 1 full and to compress incremental.

/opt/percona-xtrabackup-2.0.4/bin/innobackupex-1.5.1 --incremental\

--incremental-lsn=34020868857 \

--defaults-file=/home/mysql/instances/mtest1/my.cnf \

 --slave-info --user=backup --password=bckuser123 \

 --extra-lsndir=/home/mysql/backup/ \

--stream=xbstream --parallel=4 ./ |pigz -p4 - > /home/mysql/backup/incremental_2013_01_10_19_05.gz

To note is the parameter  --extra-lsndir which allow you to specify an additional location for the LSN file position,
this is very important because it needs to be "grep" for the next incremental backup.
Like:
grep last_lsn xtrabackup_checkpoints|awk -F' = ' '{print $2}'
34925032837

 

and the parameter --parallel=4 to implement multi thread streaming
So next will be:
/opt/percona-xtrabackup-2.0.4/bin/innobackupex-1.5.1 --incremental \
--incremental-lsn=34925032837 \
--defaults-file=/home/mysql/instances/mtest1/my.cnf \
--slave-info --user=backup --password=bckuser123 \
--extra-lsndir=/home/mysql/backup/ \
--stream=xbstream 

--parallel=4 ./ |pigz -p4 - > /home/mysql/backup/incremental_2013_01_11_11_35.gz

 

Once done taking again the LSN value it will be 35209627102
At this point we have a compress incremental backup using xbstream and pigz.
Point is can we restore it correctly?
copy all the files to the resore area/server
root@tusacentral03:/home/mysql/backup# ll
total 631952
drwxr-xr-x  3 root  root       4096 Jan 11 11:36 ./
drwxr-xr-x 19 mysql mysql      4096 Jan 10 15:01 ../
drwxr-xr-x 15 root  root       4096 Jan 10 17:27 full_2013_01_10_18_54.gz
-rw-r--r--  1 root  root  360874358 Jan 11 11:25 incremental_2013_01_10_19_05.gz
-rw-r--r--  1 root  root  286216063 Jan 11 11:41 incremental_2013_01_11_11_35.gz
-rw-r--r--  1 root  root         93 Jan 11 11:41 xtrabackup_checkpoints
 

 

then to expand it:

pigz -d -c full_2013_01_10_18_54.gz | xbstream -x -v

create 2 directory:

mkdir 2013_01_10_19_05

mkdir 2013_01_11_11_35

Then

pigz -d -c incremental_2013_01_10_19_05.gz | xbstream -x -v

pigz -d -c incremental_2013_01_11_11_35.gz | xbstream -x -v

 

After that the procedure will be the same.

/opt/percona-xtrabackup-2.0.4/bin/innobackupex-1.5.1 --use-memory=1G --apply-log --redo-only /home/mysql/restore/2013-01-10_17-15-27

/opt/percona-xtrabackup-2.0.4/bin/innobackupex-1.5.1 --use-memory=1G --apply-log --redo-only /home/mysql/restore/2013-01-10_17-15-27 --incremental-dir=/home/mysql/restore/2013_01_10_19_05

/opt/percona-xtrabackup-2.0.4/bin/innobackupex-1.5.1 --use-memory=1G --apply-log --redo-only /home/mysql/restore/2013-01-10_17-15-27 --incremental-dir=/home/mysql/restore/2013_01_11_11_35

 

Finalize the process

/opt/percona-xtrabackup-2.0.4/bin/innobackupex-1.5.1 --use-memory=1G --apply-log /home/mysql/restore/2013-01-10_17-15-27

 

Copy in the production location:
To remove possible not needed files :

find . -name "*.TR*" -exec  \rm -v '{}' \;

 

Assign correct grants to mysql user

chown -R mysql:mysql data

restart and if slave set the right binlog and position as before
Done!

 

Incremental with compression and NetCat

There are two possible ways to perform the copy with NetCat:
  • one is "on the fly" means that the stream instead being direct to a local file it is directly push on the "Recovery" server.
  • the other is to write the file then push it to the "Recovery" server.
Using the the "on the fly" is in my opinion conceptually dangerous.
This because a backup operation should be as more solid as possible.
Having the stream directed to the final server is opening to possible issue at any network glitch.
Any network flotation could affect the whole backup, and there could be also possible scenario where a full transmitted backup will result corrupted.
This because IF a network issue happen during the transfer the process on the source or destination server, the one DOING the backup or the one receiving can crash or hung.
All the above impose a sanity check on the process and on the final result, to be sure that in case of failure the backup will be take again, or at least there will be awareness about the issue.

 

Needs to be say that the process is not so fragile when dealing with small amount of data, but it could become much more concerning when dealing with Gigs because resource allocation limit on the source machine.

The NetCat solution see two elements in our case:

  • server (sender)
  • client (receiver)
This is valid in our case but needs mention that the server can also get input from the client, but this is not a topic here.

The on the fly

The backup process is suppose to be launched on the server with the following statement:
/opt/percona-xtrabackup-2.0.4/bin/innobackupex-1.5.1 --incremental  --incremental-lsn=35209627102 --defaults-file=/home/mysql/instances/mtest1/my.cnf --slave-info --user=backup --password=bckuser123 --extra-lsndir=/home/mysql/backup/ --stream=xbstream --parallel=4 ./ |pigz -p4 - | nc -l 6666
while on client :
nc 192.168.0.3 6666|pv -trb > /home/mysql/recovery/incremental_2013_01_14_12_05.gz
So the only difference is the add of the NetCat commands and obviously the need to have it done on the client.
Once the process is over, the expand can be done as usual:
pigz -d -c incremental_2013_01_14_12_05.gz | xbstream -x -v

Two steps process

Is exactly the same of the one "Incremental with compression", but instead doing a file copy issue the commands:
on the server:
cat /home/mysql/backup/incremental_2013_01_14_12_05.gz | nc -l 6666| pv -rtb
On the client:
nc 192.168.0.3 6666|pv -trb > /home/mysql/recovery/incremental_2013_01_14_12_05.gz

Conclusion

I think it could make sense to use NetCat ONLY in very specific cases, and only developing solid scripts around it, including in them:
  • status checks of the backup operation
  • list of the transmitted files
  • LSN position validation
  • network status/monitor during the operations
In short a possible nightmare.

Check lists

Simple backup

[] Check binary version
[] Check binaries are accessible in the PATH and accessible
[] Assign correct user/password in MySQL for backup user
[] Create or check backup data destination folder
[] Check my.cnf for datadir and be sure is pointing in the right place
[] Execute backup
[] Apply logs

Simple restore

[] be sure mysql server is down
[] remove / move data from original directory
[] remove / move ib_logs from original directory
[] run innobackupex --copy-back
[] check file permissions for mysql
[] start mysql
[] check the mysql log for error
[] log in and check for data.

Backup with Stream and compression

[] Check binary version
[] Check binaries are accessible in the PATH and accessible
[] Assign correct user/password in MySQL for backup user
[] Create or check backup data destination folder
[] Check my.cnf for datadir and be sure is pointing in the right place
[] Check for Pigz presence and version
[] Execute backup

Restore from Stream on a different machines (slave)

[] be sure mysql server is down
[] remove / move data from original directory
[] remove / move ib_logs from original directory
[] copy over the compress file
[] expand the backup in a safe directory
[] run innobackupex --copy-back
[] check file permissions for mysql
[] check that server WILL NOT restart the slave process on start
[] start mysql
[] check the mysql log for error
[] log in and check for data.
[] take the master log position
[] check for slave process information
[] apply new binary log position
[] restart slave
[] check slave status

Incremental Backup with Stream and compression

[] Check binary version
[] Check binaries are accessible in the PATH and accessible
[] Assign correct user/password in MySQL for backup user
[] Create or check backup data destination folder
[] Check my.cnf for datadir and be sure is pointing in the right place
[] Check for Pigz presence and version
[] Check for LSN file postion in xtrabackup_checkpoints
[] Assign the LSN to the "incremental-lsn" parameter
[] Be sure that the --extra-lsndir parametr is present and pointing to an existing directory
[] Execute backup

Incremental Restore from Stream on a different machines or slave

[] be sure mysql server is down
[] remove / move data from original directory
[] remove / move ib_logs from original directory
[] copy over the compress files
[] validate the chronological order from the BASE to the last increment
{loop for each file set}
[] expand the backup in a safe directory one a time
[] be sure that you apply log with "--apply-log --redo-only" parameters every time
[] be sure you always have the correct destination directory set (BASE set)
[] remove the incremental once apply
{loop end}
[] run innobackupex --apply-log on the BASE set
[] remove IB_log files
[] copy files to destination directory
[] check file permissions for mysql
[] check that server WILL NOT restart the slave process on start
[] start mysql
[] check the mysql log for error
[] log in and check for data.
[] take the master log position
[] check for slave process information
[] apply new binary log position
[] restart slave
[] check slave status


{joscommentenable}




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

Page 3 of 10
 

Who's Online

We have 12 guests online