MySQL Caching

To make queries faster to respond is to use caching, a cache stores frequently used data in a place that is local where is can be accessed faster. Cached data can be stored either on a computers local hard disk or in the computers internal memory. For example a web browser stores text, images and other objects of recently viewed web pages locally on your hard drive, thus the next time you visit the web and the information is still the same, the browser will retrieve the text, image, etc from the local hard disk instead of having to go out on the internet to get them which is slower. This means that the first time you retrieve data it will always be slower than when you retrieve it the second time as the data will be cached.

• getting the text, picture, etc from the internet is the slowest way to get that information and takes the longest for the web page to load
• getting the text, …

Backups and Recovery

This is the most important task of an database administrator, you must protect your data at all costs, this means regular backups and regular restores even to another system just to check the integrity of those backups. There is no point in putting yourself in a position where you are holding your breathe when a restore is happening only to find out that the backup is corrupt, try if possible to perform regular restores if not then at least you should be performing a disaster recovery test once per year. Not being able to restore could be a disaster for your company and your job.

To check your backups you can use one or more of the below which I have used in the past

• use a reporting database if the customers don't need real time data and you have the money and time, Production data could be restored every day to this system which is a very good test
• use a performance …

We all  know MySQL has different types of locking for different storage engines.It will vary depends upon the storage engines which you are using.Here we have explained how the locking mechanism will work.

There are three types of locking in MySQL,

1.Table level locking,

2.Row level locking and 

3.Page level locking.

 Table level locking:

Example1:Assume that there is a house with 5 rooms(1,2,3,4,5) and it is having 3 floors(x,y,z) and each houses are having only one  main door and each floor is having separate door to go inside the floor.And all the doors are automated when one person goes inside it will lock until the person is leaving from the house,floor and room.Here house is a table and rooms are rows and floors are pages.when a person is going inside the house name A and it will locked until the person open …

     It is important to back up your databases so that you can recover your data and be up and running again in case problems occur, such as system crashes, hardware failures, or users deleting data by mistake.

Types of Backup:

If your MySQL server performance is poor check the following details in your server.

1.Check the load average.

          ex: w 

Note:load average is not more than 1.


2.Check free memory.

          ex: free -m 

Check the available memory. 


3.Check the updating running process.

          ex: top 

Check CPU utilization and Memory of the server.


4.Show processlist.

This statement shows you which threads are running and to see “too many connections” error message and want to find out what is going on. …

What is binlog?

In MySQL the binary log record the events that, when ever the changes happens in the databases. (eg.create,delete,update,insert)

   What is binlog formats?

The format that has been used to record the changes of data in binlog.

What are the their types?

There are three types of binlog formats.

1) Statement

2) Row

3) Mixed

1) Statement 

It is the default binlog format for MySQL5.6. It records the events in SQL statement in binlog to read easily with mysqlbinlog. The binlog does not grow so fast than row format.
Faster to recover from a backup.  how it works?
· set the binlog format to statement if you use older version of MySQL 5.6 in my.cnf file.

 · restart MySQL   · create a database example india. …

MySQL STORAGE ENGINES

What is storage engine?

       In MySQL the datas are stored as files in any one of the types in storage engines. MySQL supports pluggable storage engines that we can use any types of engine belongs to your data.There are two types of storage engines in MySQL  Transactional(The data can be modified in engines) and non-transactional(It can only fetch the data from engines). The default storage engine for MySQL prior to version 5.5 was MyISAM. For MySQL 5.5 and later, the default storage engine is InnoDB.

Types of Storage Engine

1.MyISAM

2.InnoDB

3.Merge

4.Memory

5.Blackhole

6.Archive

7.CSV

8.Federated

1.MyISAM

       MyISAM is the oldest storage …

Prior to my talk at LaraconEU 2016 I was curious to know how much adoption for MySQL 5.7 was in within the community.

I tweeted this:

Pool, use twitter client to see it:

Which Version of MySQL do you use? SELECT @VERSION; RT for reach, please.

—
Gabriela D'Ávila (@gabidavila) August 23, 2016

Twitter polls only gives you up to 4 items to choose. What I wanted to know is if people were using MariaDB or other forks like Percona, but I didn’t had the proper space, and I  only put three options.

This January I managed to get a bit more syndication on my tweet and more people replied. I added a 4th option, “Other”. This option could …

In this blog post, I’ll look at the types of NVMe flash health information you can get from using the NVMe command line tools.

Checking SATA-based drive health is easy. Whether it’s an SSD or older spinning drive, you can use the

smartctl

 command to get a wealth of information about the device’s performance and health. As an example:

root@blinky:/var/lib/mysql# smartctl -A /dev/sda
smartctl 6.5 2016-01-24 r4214 [x86_64-linux-4.4.0-62-generic] (local build)
Copyright (C) 2002-16, Bruce Allen, Christian Franke, www.smartmontools.org
=== START OF READ SMART DATA SECTION ===
SMART Attributes Data Structure revision number: 16
Vendor Specific SMART Attributes with Thresholds:
ID# ATTRIBUTE_NAME          FLAG     VALUE WORST THRESH TYPE      UPDATED  WHEN_FAILED RAW_VALUE
 1 Raw_Read_Error_Rate     0x002f   100   100   000 …

I was working on a seemingly basic query the other day where the user needed to have an INT column listed in ascending order (i.e., 1, 2, 3, …).

However, the tricky part came in because the column allowed NULLs and the user needed the NULLs to be listed last, not first, which is the default behavior in both MariaDB and MySQL.

We first devised a somewhat convoluted solution where we used ISNULL() first in the ORDER BY, and then the column, but that wasn’t ideal since it added an additional check for each row in the ORDER BY, which we wanted to avoid in a query returning ~5M rows.

To illustrate, a normal query just sorting in ASC order returned:

MariaDB> SELECT * FROM t1 ORDER BY col1 ASC, col3 ASC;
+--------+--------+------+
| col1   | col2   | col3 |
+--------+--------+------+
| apple  | yellow | NULL |
| apple  | red    |    5 |
| apple  | green  |   10 |
| banana | brown  | NULL |
| banana | green  |    5 |
| …