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.

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.

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 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)

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)

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)

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)

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)

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)

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)

Rows reads with mix of SELECT and INSERT

(High value is good)

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.

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:

What make this decision necessary? What is the objective?

What I am going to recommend, and on what basis?

What other possible alternatives exists, which one is the more realistic feasible, which one the more innovative?

What are the possible consequence of my decision, and how likely they are going to happen?

If this consequence will happen what will be the relevance and how we can manage them?

Comparing different solution, which one will be the best to mitigate negative effect?

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.

How to do INCREMENTAL BACKUP with Xtrabackup

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.

Incremental with compression and 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:

Overview

In the last few months, we have seen more and more discussion on the use of Galera solution for MySQL Clustering.

I have being one of those that had heavily test and implement Galera solution, actually with quite good results and I have also presented SOME of them at Oracle Connect.

On the other side I have be work with MySQL NDB for years (at least from 2007) at many customers site, from simple to complex setups.

So also if I cannot consider myself as mega expert, I think to have some good experience and insight on both platform.

The point here is that I was not happy in reading some articles comparing the two, not because the kind of tests, or results.

Not because I prefer this or that, but simply because, from my point of view, it does not make any sense to compare the two.

We can spend pages and pages in discussing the point here, but I want try to give a simple generalize idea of WHY it makes no sense in few lines.

NDB brief list

NDB is not a simple storage engine and can work independently, MySQL is “just “ a client.

NDB is mainly an in memory database and also if it support table on disk the cost of them not always make sense.

NDB is fully synchronous, no action can be returned to client until transactions on all nodes are really accepted.

NDB use horizontal partition to equally distribute data cross node, but none of them has the whole dataset (unless you use one node group only, which happens ONLY when you don’t know how to use it).

NDB Replicate data for a specific factor, which is the number of replica, and that replication factor will not change with the increase of the nodes number.

Clients retrieve data from NDB as whole, but internally data is retrieve by node, often use parallel execution. (Not going in the details here on the difference between select methods like match by key, range, IN option and so on).

NDB scale by node group that means it really scale in the Dataset dimension it can manage and operation it could execute, and it really scale!

Galera brief list

Galera is an additional layer working inside the MySQL context.

Galera require InnoDB to work.

Galera offer “virtually synchronous” replication.

Galera replicate the full dataset across ALL nodes.

Galera data replication overhead, increase with the number of nodes present in the cluster.

Galera replicate data from one node to cluster on the commit, but apply them on each node by a FIFO queue (multi thread).

Galera do not offers any parallelism between the nodes when retrieving the data; clients rely on the single node they access.

So why they cannot be compare?

It should be quite clear that the two, are very different from starting from the main conceptualization, given NDB is a cluster of many node groups with distribute dataset, while Galera is a very efficient (highly efficient) replication layer.

Galera is not involved at all in the data fetch and clients need to connect to one node or more by themselves, means application require managing parallel request in case of need.

In NDB the more node groups you add the more you get in possible operation per second and data archived/retrieved.

In Galera the more nodes you add, the more overhead you generate in the replication, so more data will require to be “locally” commit by the replication layer, until when the number of nodes and operations executed on them will compromise the performance for each node.

Conclusion

NDB Cluster is a real cluster solution, design to internally scale and to perform internally all the required operation to guarantee high availability and synchronous data distribution.

Galera is a very efficient solution to bypass the current inefficient mechanism MySQL has for the replication.

Galera allow to create a cluster of MySQL nodes, in virtually synchronous replication. This with almost zero complexity added on the standard MySQL management.

Never the less the obtaining platform is composed by separate nodes, which for the good or the bad is not a system of distributed data.

Given that, the scenario where we can use Galera or NDB are dramatically different, trying to compare them is like comparing a surfboard with a snowboard.

I love them both, and honestly I am expecting to see Galera deployment to dramatically increase in 2013, but I am still respecting my motto “use the right tool for the job”.