MongoDB for a MySQL user

Over the past few years NO SQL has become very popular, specially with a capability to boast about an illustrious reference list like twitter, facebook and foursquare.

Being a MySQL user I was curious to explore into this and see what it has and what it offers. Certainly there are plenty of NO SQL implementations, first wanted to check casandra but the first few lines of its documentation sounded like a no go zone, hence decided to try Mongo DB. Going through it I realized that there were alot of similarities. So listed them here for quick reference.

Criteria	MySQL	MongoDB
Type of database	Relational	Document
Installation	On Linux you need to go through a certain amount of steps like user creation, permission setting, creating the data directory	Pretty stright forward create the the data directory at the default location /data/db. It was pretty simpler than MySQL
Starting Up	starting up using safe_mysqld (techincally it invokes mysqld)	pretty straight forward run mongod for a basic start up
Queries	Using SQL	No SQL
	Need to execute create statements ex. When you wanted to create a table with columns x, y you need to do the following. 1. Create database mine; 2. create table a(x int, name varchar(50))engine = MyISAM; 3. Then seperately do an insert insert into a values(10, "hiii");	No create statements for DBs, collections, etc Updated and created as and when needed(lazy loading), so to do the same we need to, db.mine.insert({x: 10, y: "hiii"}) similarly you have functions for update, save, remove, find
	if you want to select data; we can select * from a;	On MongoDB its, db.mine.find()
Storage Engines	A lot of Storage Engines to choose from	Upto now a single storage engine implementation
Operations/ Transactions	When you fire a query to the MySQL server the client needs to wait till the MySQL server returns the result.	In contrast Mongo DB uses 'Fire and forget'. So technically speaking client is not aware whether the query executed successfully or not. It doesn’t return OK. For system where you need safe operations immediately following the query getLastError command is issued and the exception handled. For Requirements like analytics , status message updates, pics comments this fire and forget is perfect. Anyhow for Financial type of system we need to use the work around.
	storage engine like INNODB supprts transactions and has ACID compliance	Not typically made for transactional systems
Indexing	Supports mutiple types of indexes. For optimzation EXPLAIN is used with the select statements	Very similar to MySQL indexing. For optimzation similar to MySQL this has the EXPLAIN statement and another statement called as hint Main difference is that you can define the order of indexing for composite indexes. Which gives more control to the query definition Eg. db.mine.ensureIndex({"x" : 1}) 1 indicates the direction
Geo Spatial Indexing	MyISAM storage engine supports geo spatial indexing.	Is created by passing "2d" instead of passing 1 or -1 to the ensureIndex  function. Pretty handy stuff with  straight forward support for querying nerarest locations, find entries within a shape and find distances. Compound geo spatial indexing also supported, this will be handy for finding nearest ATM etc. I think this is a must check for people developing location based systems
Back Up	MySQL back up is more complex, specific to the storage engines. Snapshot based back ups on LVM2 volumes is similar to the fsync command in mongo	supports fsync command which is a warm back up with point of time support. Supports mongodump - which does a hot back up but without point of time support
Replication
Mode of Replication	asynchorouns	asynchronous
	Replication is based on Binary log - which has all the writes	Replication is based on OpLog, similar to the binary log but has idompotent operations. Which basically means if the same statement is executed mutiple times it will not make the date inconsistant . Anyhow the statements shoule be executed in order
	set up is straight forward after having a master and slave in place	quiet similar - at start up itself the server can be specified as slave in the start up line, ./mongod --dbpath ~/data/slave --port 10001 --slave --source localhost:10000
	supports complex replication set ups, like two way replication, circular replication, etc	does not support replication from the slave
Replica Set	MySQL cluster supports Replica sets- anyhow MySQL cluster replica sets has synchrous replication	Similarly for HA support MondoDB supports replica sets - which is a master slave cluster with automatic failover. Similar to MySQL cluster in the replica sets. There are arbiter nodes which decide on the primary and secondary node election in case of failures
Sharding	MySQL doesn’t have inbuilt sharding support. Sharding is usually handled at application or ORM level for MySQL	sharding support is given - mongos is a process that will interface multiple mongods which will have the data distributed. Very good feature for scalability

For those who want to try out mongodb check this book, http://oreilly.com/catalog/0636920001096

Next up I want to try Couch DB, which is another No SQL database and implemented on Erlang!

MySQL Development Best Practices

I strongly believe that MySQL programming best practices need to be known and followed by each developer, since practicing query optimization from day one make everyone's life much more easier. So I present here a very simple MySQL Development check list.


Check List for General Best Practices

1. Checking for indexing

1.1 All columns in the 'WHERE' clauses indexed?

• [Eg. select * from student where StudentId = '7986'; then column StudentId need to be indexed]
  

1.2 All columns in the 'ORDER BY' indexed?

• [Eg. select * from student ORDER BY dob; then column dob need to be indexed]

1.3 All columns in the 'GROUP BY' indexed?

• [Eg. select * from student GROUP BY Class; then column Class need to be indexed]
  

1.4 All columns used for joins indexed?

• [Eg. select country.Name, city.Name where country.Id = city.CountryId; both country.Id and City.CountryId are better to be indexed]
  

2. Checking for over indexing

2.1 No redundant indexes?

• when having composite indexes remember that the left most indexes can serve as indexing and does not require separate indexes.

• [Eg. select * from student where house = 'YELLOWS' and class = '3E';

• if student table has the following indexes a. index(house,class) b. index(house)The index(house) is a redundant index hence not required and better be removed.
  

2.2 No indexes that has been created and never used within the 'WHERE'or ORDER BY or GROUP BY or for joins?

2.3 No simple indexes created on column which does not have unique values?

• [Eg. A simple index on a 'sex' column with a data set with a distribution of 45% Males and 55% Females will not be usually used for result pruning]

3. Check for data types

3.1 Has the appropriate shortest data type been chosen for each column?

• [Eg. If a column can be defined as TINYINT do not have it as BIGINT]
• [Eg. If a column can be defined as varchar(10) do not define it as varchar(255)]
• [Eg. If a column can be defined as enum do not define it either as varchar, char or int]

3.2 Has the integer columns not taking minus values defined as 'unsigned'?

3.3 Has the columns which could never have NULL values explicitly defined as NOT NULL?

4. INNODB Specific Checks

4.1 No char columns in INNODB tables?

• [ Do not use char columns in INNODB always have VARCHAR]

4.2 Does All the INNODB tables have primary key?

4.3 Are the columns in the Primary key of the minimal possible length?

4.4 Has all the statements like, select count(*) from innodbTable; statements eliminated?

• [Specially do not use on critical tables with a huge dataset. As an alternative to the select count(*) use summary tables]
  

* NOTE: I had avoided complicated practices and included only the most simple practices.

Using Perl Script for memory calculation of NDB Cluster in Fedora 9

Step 1: Check that you have perl

get the perl DBI module installed by using,

yum install perl-DBD-MySQL

step 2: cd into $MYSQL_HOME(or mysql base directory usually /usr/local/mysql and try

./bin/ndb_size.pl --database=myDb --user=myUser --socket=/tmp/mysql.sock --password=password

If this fails with the error like Can't use string ("9/16") as a HASH ref while "strict refs" ..... then you need to get fix the bug by replacing the following peice of code,

current code, in release ..it starts from line number 920


foreach my $i(@show_indexes)
{
$indexes{${%$i}{Key_name}}= {
type=>${%$i}{Index_type},
unique=>!${%$i}{Non_unique},
comment=>${%$i}{Comment},
} if !defined($indexes{${%$i}{Key_name}});
$indexes{${%$i}{Key_name}}{columns}[${%$i}{Seq_in_index}-1]=
${%$i}{Column_name};
}


need to be replaced as follows,

foreach my $i(@show_indexes)
{
$indexes{$i->{Key_name}}= {
type=>$i->{Index_type},
unique=>$i->{Non_unique},
comment=>$i->{Comment},
} if !defined($indexes{$i->{Key_name}});

$indexes{$i->{Key_name}}{columns}[$i->{Seq_in_index}-1]=
$i->{Column_name};
}


then executing,

./bin/ndb_size.pl --database=myDb --user=myUser --socket=/tmp/mysql.sock --password=password

should get the output for you.

Looking to optimize "Group by" in MySQL?

Few months back I was asked for the reason behind the recommendation by the consultants from MySQL. When the system was performing poorly the consultants had walked in and simply requested them to add the "order by NULL" clause to the queries. To the joy of the client and amazement of the tecnical team at the client location, this simple fix had done the trick and suddenly there was a marked improvement in performance! This had left the technical team confused, as they started to wonder why and how this happened. WhenI heard this I was confused ( as always ;) ), as I could not think of any possible logical explanation for this behaviour.

Last week while I was reading the planet MySQL I bumbed into an article talking about order by NULL and I think I had found the possible reason. Any how it is not a statement, which would optimize all queries , instead it would optimize all the statements with a group by clause.

To make things simple lets take small example,

EXPLAIN SELECT CountryCode, COUNT(*) FROM City GROUP BY CountryCode \G

This gives the following output,

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using temporary; Using filesort

The extra column indicates that both filesort and temporary is being used, which is an indication of poor performance.

Alternatively when its applied with a Order by NULL as shown below,

EXPLAIN SELECT CountryCode, COUNT(*) FROM City GROUP BY CountryCode order by NULL \G

gives an ouput,

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using temporary

Which indicates a bit of an improvement as it does not any longer have Using filesort.

This addition of a order by NULL could give a performance increament by many folds at times.

The next most important question is how does this happen?

MySQL by default when having a group by statement would also order by the same column, which would require an additional sorting to take place. Any how when the order by NULL is included, it does not do a sort and thereby give a performance improvement.

Playing around with memory tables

The other day I was discussing about using the alter table statement in MySQL to convert from MyISAM to Memory engine with some of my colleagues. We were just wondering what will really happen if we try issue an alter table statement, which will subsequently try to create a memory table which will have a table size larger that the max_heap_table_size parameter. We were wondering if data truncation will occur after that limit.

So I thought of testing it and I did the following,

initially I set the following in my.cnf
[mysqld]
max_heap_table_size = 8K

then I tried the following,
alter table City2 engine=memory;
ERROR 1114 (HY000): The table '#sql-2bc1_1' is full

and as soon as I checked
show create table City2;
City2 | CREATE TABLE `City2` (
`ID` int(11) NOT NULL auto_increment,
`Name` char(35) NOT NULL default '',
`CountryCode` char(3) NOT NULL default '',
`District` char(20) NOT NULL default '',
`Population` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

It was clear that no changes has taken place and the alter table has completely failed and the table continue to be MyISAM,

After that just to confirm it I did the following,

set global max_heap_table_size=2*1024*1024;

alter table City2 engine=memory;

City2 | CREATE TABLE `City2` (
`ID` int(11) NOT NULL auto_increment,
`Name` char(35) NOT NULL default '',
`CountryCode` char(3) NOT NULL default '',
`District` char(20) NOT NULL default '',
`Population` int(11) NOT NULL default '0',
PRIMARY KEY (`ID`)
) ENGINE=MEMORY AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

So finally I know for sure that there will be no data truncation when you issue an alter table statement to convert to Memory. Hopefully this will be useful to the guys who are playing around with memory tables these days :)

PS: I tested this on mysql 5.0.27-max-log

Copying tables in MySQL

The other day I was asked by friend on how a table could be copied in mysql, this query made me think that I should list down some interesting yet simple statements which might be important to people.

QUERY 1: If you want to create a new table out of an existing one

create table City2 like City;

This would create a new table with the same structure as City, but the rows in City will not be copied to City2.

QUERY 2: If you want to create a new table along from an existing table along with all the existing data.

create table City3 select * from City;

QUERY 3: If you want to select only a few columns and create a new table from an existing one

create table City4 select Name,CountryCode from City;

QUERY 4: If you want to copy all data from one table to the other

insert into City2 select * from City;

QUERY 5: If you want to copy certain data from one table to the other

insert into City6(Name, CountryCode) select Name, CountryCode from City;

** NOTE in this query we do not have a values part in the insert query.