What are literals in MySQL?

If we look in the dictionary: literals are meanings. Description of something in the form of text. In MySQL, Oracle, SQL Server, or any other database, a literal is the content of a variable, or simply a value offered for comparison.

SET var_Name = ‘ALEXANDRE’

In the example above we’ve assigned the literal ALEXANDRE to the var_Name variable. We can say that the content (or meaning) of var_Name is ALEXANDRE.

Ah! Got it!!! Everything between quotes is a literal. Not so fast. The analogy is good but imperfect.

SET var_I_like_move_move_it  = FALSE;

SET @var_Price = 10;

SET @Qty = 3;

SET @Total = @var_Price * @Qty;

FALSE (0) is a literal that has been assigned to var_I_like_move_move_it.  Although my children love Madagascar, and, I being sympathetic to King Julien, I don’t like to dance. Therefore, FALSE (0 in MySQL) describes the contents of the variable to which it was assigned.

10 is the literal assigned to the integer variable @var_Price. Like, 3 tells me how much the @Qty variable is worth. The computation (calculation) of the literals contained in the environment variables @Var_Price and @Qty describe the environment variable @Total, or the value within the variable. Continue reading What are literals in MySQL?

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:

[shell]# 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.