Wednesday, 12 February 2014

MySQL: Slave Status - SECONDS_BEHIND_MASTER



We often execute the below command to check the replication status:

mysql> SHOW SLAVE STATUS \G

This will show all the details regarding replication. Out of which, the field SECONDS_BEHIND_MASTER is the one is the curious one.

It is the time in Seconds, which indicates actual amount of lag between Master and Slave.

In actual words, it's the calculation in seconds between the relay log's latest query event time stamp(which is already executed on master and queued on slave) and the current time stamp of the slave.

So, if the time set on Master's host and Slave's host are different, then, you'll see a scary value for this field.

Tuesday, 11 February 2014

techGeek: Create Password less PDF !

Now-a-days, almost all PDF docs are password protected! Uff..sometimes I get irritated of it and try to make it password-proof!

And here's how you can make it:

My PDF with password:





No worries! Just close it and do as below:

* Open the PDF with Google Chrome:






*It will prompt for the password and give it:



* Once the PDF is open, click on Print button at right side bottom (or press Ctrl + P) and select Save as PDF. Click on Save :



Its done! 
Now the PDF will be saved without password!!






Monday, 10 February 2014

Linux: Crontab - Brief

I always get confuse whenever I want to set a new cron job. The confuse is with regard to the options too be set!
For those who new to 'cron', its nothing but, an event scheduler in Linux. That means, you can schedule any script to run at any time you wanted to. Its just the system/server should be up and running!

cron job is specific to every user in Linux/Unix. So, one can't see other's cron unless the necessary privileges or sudo root access given.

Whatever, here is the options in cron:


To check cron jobs:

[root@localhost kiran]# crontab -l
no crontab for root


To set cron jobs:

[root@localhost kiran]# crontab -e


After adding, here is how it looks:

[root@localhost kiran]# crontab -l
##Script to test
00 */2 1-31 * 0,2,3   sh /home/kiran/test.sh >> /dev/null


Every Cron job should be given with 5 options:


  • minute -> 0-59
  • hour -> 0-23
  • day of month -> 1-31
  • month -> 1-12 
  • day of week -> 0-7 (0 is Sunday )

In the above example:

00 -- 0th Minute
*/2 -- Every 2 hours
1-31 -- Every day (1 to 31)
* -- Every Month
0,2,3 -- Sunday,Tuesday,Wednesday




Friday, 7 February 2014

MySQL: Clustered Index


Clustered Index is the InnoDB term for a primary key index. 

InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. 

If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. 


The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted.
Thus, the rows ordered by the row ID are physically in insertion order. 


All indexes other than the clustered index are known as secondary indexes.
In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. 


InnoDB uses this primary key value to search for the row in the clustered index.


Physical Strcuture:
All InnoDB indexes are B-trees where the index records are stored in the leaf pages of the tree. 
The default size of an index page is 16KB. 
When new records are inserted, InnoDB tries to leave 1/16 of the page free for future insertions and updates of the index records.

The page size can be specified for all InnoDB tablespaces in a MySQL instance by setting the innodb_page_size configuration option before creating the instance.
 

Once the page size for a MySQL instance is set, you cannot change it.
Supported sizes are 16KB, 8KB, and 4KB, corresponding to the option values 16k, 8k, and 4k.

A MySQL instance using a particular InnoDB page size cannot use data files or log files from an instance that uses a different page size.

MySQL: B-Tree Index

 When any CREATE TABLE is executed, InnoDB internally creates a clustered B-tree index  representing the table. 
If no primary key is explicitly given, internally a primary key is added. 

Each node of B-tree is represented by one page (which is by default is of size 16KB). 


Below is an useful set of info, copied from other blog
(guptavikas.wordpress.com/2012/12/17/b-tree-index-in-mysql/):


B-Tree is the default index for most storage engines in MySql. 

The general idea of a B-Tree is that all the values are stored in order, and each leaf page is the same distance from the root.

A B-Tree index speeds up data access because the storage engine doesn’t have to scan the whole table to find the desired data. 
Instead, it starts at the root node. The slots in the root node hold pointers to child nodes, and the storage engine follows these pointers.

It finds the right pointer by looking at the values in the node pages, which define the upper and lower bounds of the values in the child nodes. 

Eventually, the storage engine either determines that the desired value doesn’t exist or successfully reaches a leaf page. Leaf pages are special, because they have pointers to the indexed data instead of pointers to other pages.

u00320020206brl05_01

MySQL: InnoDB doublewrite buffer

That's an useful piece of information, so did a copy-paste from MySQL site:

InnoDB uses a novel file flush technique called doublewrite. 

Before writing pages to the data files, InnoDB first writes them to a contiguous area called the doublewrite buffer. Only after the write and the flush to the doublewrite buffer have completed, does InnoDB write the pages to their proper positions in the data file. 

If the operating system crashes in the middle of a page write, InnoDB can later find a good copy of the page from the doublewrite buffer during crash recovery.

Although data is always written twice, the doublewrite buffer does not require twice as much I/O overhead or twice as many I/O operations. Data is written to the buffer itself as a large sequential chunk, with a single fsync() call to the operating system.

To turn off the doublewrite buffer, specify the option innodb_doublewrite=0.



Wednesday, 29 January 2014

MySQL: SUBSTRING_INDEX - Select Patterns

Consider, a MySQL table having values in a column like below:

SELECT location  FROM geo LIMIT 3;

"location"
"India.Karnataka.Shimoga.Gopala"
"India.Karnataka.Bengaluru.BTM"
"India.Karnataka.Chikmaglore.Koppa"

My requirement is to take only 4th value from each of the rows(such as, Gopala,BTM,Koppa). 
I don't want to display remaining values. 
Its same as what 'cut' command will do in Linux.

For this, we can use SUBSTRING_INDEX function.

SELECT SUBSTRING_INDEX(location,'.',-1)  from geo LIMIT 3;
"location"
"Gopala"
"BTM"
"Koppa"

Syntax: SUBSTRING_INDEX(string,delimiter,count)
Here count means column number based on delimiter. 
Negative value indicates that the column numbers calculated from right side.

So, if I give '-2' instead of  '-1':

SELECT SUBSTRING_INDEX(location,'.',-2)  from geo LIMIT 3;
"location"
"Shimoga.Gopala"
"Bengaluru.BTM"
"Chikmaglore.Koppa"