Category Archives: Sem categoria

MySQL on Amazon AWS: RDS or EC2?

Guys, it’s been two days since the Star Wars movie premiere. Height anxiety level ūüôā

But, you didn’t come here to talk about Star Wars. So let’s get down to business, and leave the dark side of the force to illuminate your mind.

Alphabet Soup: AWS is Amazon Web Services. By the way, I think the web is no more, as Amazon services have reached much higher levels. RDS is an acronym for Relational Database Services, and EC2 Elastic Compute Cloud.

If you reproduce all this, it’s a Kaminoano blender, so it will be a cloud service, ideal for hosting database servers.

One question that always asks me is: Should I host my MySQL database on RDS or EC2?

My standard answer has always prevailed, “It depends, little Padawan.”

Let’s understand in general who is who:

RDS: An RDS instance is an abstraction of a database server. Besides not having a hardware figure, not having an operating system or installing a database. It can be roughly understood as a virtualized database. It is a database provided as a service (DBaaS). Currently, the following banks can be contracted as RDS: MySQL, MariaDB, Aurora, Oracle, SQL Server and PostgreSQL.

EC2: EC2 is the same, it’s a good old virtual machine (VM) that virtualizes the hardware, installs and configures your preferred operating system, file system, and database. Everything can be tailor made and taste. Any database can be installed in the EC2 environment.

There are a lot of good articles on RDS and EC2, and I already say that there are fervent advocates on both sides. The best person to tell you is the best way forward: RDS or EC2, it’s yourself. So here is a basic guide of questions you should ask to make the best decision.

Ease of implementation and management of the environment

RDS will save you a good time implementing a new environment, remember, you don’t need to create the VM, install the OS, configure the file system, install the database. One click, and Shazam! Ready to use. However, you are carrying a bundle of joy with some customizations, and that’s it.

EC2 is grosser. You will need to create the VM, install and configure your preferred OS, configure the file system that best suits your application, install and configure the database.

RDS is click and be happy. Let AWS worry about updates and patches.

EC2 is Dark Side of the Force. It is control. How good Sith I prefer.

Performance

I really don’t have a magic formula for you. I have clients that RDS performs better than EC2. Fact. From the tests I did, 78% of well-configured EC2 cases run better and faster than RDS. But in 22% of cases, in fact, RDS ran better.

Unexplained Locks

What bothers me about RDS environments are locks that happen on time without any logic. “Ah, it’s your configuration or application problem.” Hi? Doesn’t AWS say it takes this part? In all our instances, from different clients, from different niches, configurations, and instances of different sizes, this behavior is observed to a greater or lesser extent.

Cost

Some geniuses have posted that RDS is cheaper. What I have observed is that RDS is 20% to 38% more expensive than EC2. However, we have to consider here the hours we will stop using with installation, update, patching, etc. Another cost factor that does not reach MySQL, but should be considered in the case of Oracle and SQL Server is the Bring Your Own License (BYOL) model, in which you take your license to RDS, which may positively or negatively influence the cost. .

Management

You will not have a MySQL SUPER user. So, forget some commands. But AWS has provided a number of procedures that attempt to mitigate this absence of the SUPER user. This is particularly bad as it takes away some of DBA’s ability to act in emergency situations.

Data Encryption

RDS does not allow data encryption. Then, depending on the criticality and sensitivity of your data, go straight to EC2.

MySQL Plugins

If for you plugins for authentication, auditing, semi-synchronous replication, among others, is a necessity, RDS is not for you. Strictly speaking, RDS does not allow any kind of plugin. Oh, no GTID (Global Transaction ID). To use these plugins, only on EC2.

And now?

Use the best of both worlds. Ask yourself the questions, and see which application should go to RDS, and which should go to EC2. Use the portfolio balancing technique of investors and economists to mitigate risk.

As appropriate for RDS, use RDS to decrease your hours with change management and Operating System and Database updates.

Whatever is important, sensitive, critical put in EC2, where, you have the most control of the environment. And, okay, invest a little more time.

INSTALLING INNOTOP TO MONITOR INNODB

O innotop √© uma script perl criado para extrair informa√ß√Ķes importantes acerca do funcionamento interno do storage engine innoDB. A partir da vers√£o 5.4 passamos a contar com informa√ß√Ķes sobre transa√ß√Ķes ativas, travamentos (locks), situa√ß√£o do innoDB Buffer Pool, etc, atrav√©s de tabelas acrescidas ao cat√°logo do MySQL, tamb√©m conhecido e representado pelo banco de dados information_schema.

Ler estas tabelas pode ser uma tarefa ingrata e in√≥cua para aqueles que n√£o tem tanta experi√™ncia com MySQL. Pior ainda, seria tentar usar o show engine innoDB status. Confesso que nos primeiros anos de MySQL o resultado deste comando, para mim, parecia muito com o sinal alien escondido nas freq√ľ√™ncias dos sat√©lites durante a invas√£o terrestre do filme “Independence Day”. Algumas coisas me intrigaram neste filme: 1o) Por que nunca traduziram o nome do filme; b) Como o “homem mosca” (Jeff GoldBlum) conectou-se ao sistema dos invasores: bluetooh ou wi-fi?; c) Caras, pelo amor do J.Cristo, os invasores n√£o tinham firewall e ainda queriam dominar a Terra? Aqui n√£o jacar√© ūüôā

Já enrolei bastante voces, voltemos ao que interessa: instalação do innotop!

Vou assumir que iremos utilizar um Linux, por questão de paixão: CentOS. Quais as dependências, os pré-requisitos para instalação do innotop?

* PERL

– Execute o comando abaixo para saber se o Perl est√° instalado:

# perl -v
This is perl, v5.8.8 built for x86_64-linux-thread-multi
Copyright 1987-2006, Larry Wall
Perl may be copied only under the terms of either the Artistic License or theGNU General Public License, which may be found in the Perl 5 source kit.
Complete documentation for Perl, including FAQ lists, should be found onthis system using “man perl” or “perldoc perl”. ¬†If you have access to theInternet, point your browser at http://www.perl.org/, the Perl Home Page.

No meu caso, tenho o Perl v5.8.8 instalado. N√£o importa a vers√£o, caso receba mensagem parecida com a que eu recebi, voce est√° pronto para continuar.

– Execute o comando abaixo para instalar Perl, caso n√£o tenha ele instalado:

yum install perlLoaded plugins: fastestmirrorLoading mirror speeds from cached hostfileSetting up Install ProcessPackage 4:perl-5.8.8-32.el5_5.2.x86_64 already installed and latest versionResolving Dependencies–> Running transaction check—> Package perl.i386 4:5.8.8-32.el5_5.2 set to be updated–> Finished Dependency Resolution
Dependencies Resolved
===========================================================
Package     Arch   Version              Repository   Size
===========================================================
Installing: perl        i38    4:5.8.8-32.el5_5.2   extras       12 M
Transaction Summary
===========================================================
Install       1 Package(s)Upgrade       0 Package(s)
Total download size: 12 MIs this ok [y/N]: y

Just confirm with yes and get down the wood in the installation. It’s never too much to remember that yum is an installation command inherent in Red Hat-based distributions, as in my case, CentOS. Other distributions may use: apt-get, aptitude, urpmi, pkg-get, etc.