• Call: +1 (858) 429-9131

Posts Tagged ‘ODB’

Migrate Mysql database to Mongodb

In recent years, we have seen a growing interest in database management systems that differ from the traditional relational model. At the heart of this is the concept of NoSQL, a term used collectively to denote database software that does not use the Structured Query Language (SQL) to interact with the database. One of the more notable NoSQL projects out there is MongoDB, an open source document-oriented database that stores data in collections of JSON-like documents. What sets MongoDB apart from other NoSQL databases is its powerful document-based query language, which makes the transition from a relational database to MongoDB easy because the queries translate quite easily.

This new class of databases seems to solve many of the bottlenecks in MySql and other relational databases. It will give you shear performance, self replication and scalability at not cost because it open source. MongoDB has plenty of drivers for other scripting and high-level languages I use PHP so I download the PHP driver. You can see the supported list here: http://www.mongodb.org/display/DOCS/Drivers. In this blog I convert  a MySQL database using PHP to MongoDB.

First you install MongoDB, you can do it by checking the previous blog.  Check this link

Then we run the script to convert a Mysql DB to Mongodb.

create a new file called MySqltoMongodb.php , In that file please copy paste the below contants (please give your Mysql DB details as well as your Mongodb details)

  1. <?php
  2. // mysql settings
  3. $mydb = “database”;
  4. $myconn = mysql_connect(‘localhost’,’user’,’password’);
  5. $setmydb = mysql_select_db( $mydb );
  6. $mytables = getMyTables( $mydb );
  7.  //mongo db settings
  8. $modb = “database”;
  9. $moConnect=”mongodb://user:password@localhost”;
  10.  function getMyTables( $dbname ) {
  11. $tables = array();
  12. $sql = mysql_query(“SHOW TABLES FROM $dbname “) or die(“Error getting tables from $dbname”);
  13.  if( mysql_num_rows( $sql ) > 0 ) {
  14. while( $table = mysql_fetch_array( $sql ) ) {
  15. $explain = explainMyTable( $table[0] );
  16. $tables[$table[0]] = $explain;
  17. }
  18. }
  19. return $tables;
  20. }
  21.  function explainMyTable( $tbname ) {
  22. $explain = array();
  23. $sql = mysql_query(“EXPLAIN $tbname”) or die(“Error getting table structure”);
  24. $i = 0;
  25.  while( $get = mysql_fetch_array( $sql ) ) {
  26. array_push( $explain, $get[0] );
  27. $i++;
  28. }
  29. return $explain;
  30. }
  31.  function checkEncode($string) {
  32. if( !mb_check_encoding($string,’UTF-8′)) {
  33. return mb_convert_encoding($string,’UTF-8′,’ISO-8859-1′);
  34. } else {
  35. return $string;
  36. }
  37.  }
  38. try {
  39. $moconn = new Mongo($moConnect);
  40. $modb = $moconn->selectDB( $modb );
  41. } catch(MongoConnectionException $e) {
  42. die($e.”Problem during mongodb initialization. Please start mongodb server.”);
  43. }
  44.  foreach( $mytables as $table => $struct ) {
  45. $sql = mysql_query(“SELECT * FROM $table LIMIT 0 , 500000″) or die( mysql_error() );
  46. $count = mysql_num_rows( $sql );
  47.  // Starts new collection on mongodb
  48. $collection = $modb->$table;
  49.  // If it has content insert all content
  50. if( $count > 0 ) {
  51. while( $info = mysql_fetch_array( $sql, MYSQL_NUM )) {
  52. $infosize = count( $info );
  53. $mosql = array();
  54.  for( $i=0; $i < $infosize; $i++ ) {
  55. if(!empty($struct[$i]))
  56. $mosql[$struct[$i]] = checkEncode($info[$i]);
  57. }
  58.  $collection->insert($mosql);
  59. }
  60. // Only create a new entry empty
  61. } else {
  62.  for( $i=0; $i < $infosize; $i++ ) {
  63. if(!empty($struct[$i]))
  64. $mosql[$struct[$i]] = ”;
  65.  }
  66. $collection->insert($mosql);
  67. }
  68. }
  69. echo “Done! Please, check your MongoDB collection!”;
  70. ?>

Now fire up your browser and launch the page. If all goes well you should see
“Done! Please, check your MongoDB collection!”

After running this script check your Mongo db collection, in that you can see your Mysql Table.  However we haven’t done it on a large system, we are planning to do the same on a huge Postgres Sql system soon.

MySQL Optimization

Database optimization is the process of configuring database to use system resource efficiently and perform tasks quickly. To optimize mysql you should know the work flow of entire system, your hardware, operating system, disk I/O performance etc.
Why to Optimize
You can do more with less. The default mysql setup is optimized for a minimal system because it should work well on a minimal hardware. But when you use a dedicated mysql server with high traffic and complex queries you have to optimize mysql.
MySQL Server tuning Considerations
Here you will find some common optimization parameters.

  • MySQL variables
  • Hardware
  • Disk
  • Application

MySQL Optimization
MySQL global variables don’t have any predefined optimum values. It is a trial and monitor process. It depends on all the above parameters. Here you will see some of the common parameters.
It is size of the buffer used to index blocks for MyISAM tables. On a dedicated mysql server with MyISAM storage engine 25-30% of systems total memory you can allocate for key_buffer_size. To fine tune key_buffer_size you can compare the variables key_reads and the key_read_requests.
This ratio should be at least 1:100.

SHOW STATUS LIKE ‘%key_read%’;
| Variable_name | Value |
| Key_read_requests | 10726813161 |
| Key_reads | 92790146 |
Here the ratio is 1:115 which is acceptable.
But suppose you get a ratio 1: 10 then you need to add more key buffer and upgrade hardware accordingly.
Query Cache
“My website is too slow while loading dynamic pages”. If it is a mysql database related issue, following MySQL variables will be your solution.
Set the query cache type. There are 3 values 0 ,1 or 2

0 Do not cache any query result
1 Cache query results.
2 Cache results ondemand. Cacheable queries that begin with SELECT SQL_CACHE.

The amount of memory used to cache query result. Default is 0 which disable query cache.
The optimum value is depend on your application.
Do not cache results that are larger than this number of bytes. The default value is 1MB.
Status checking
SHOW STATUS LIKE ‘%qcache%’;
| Variable_name | Value |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 8371272 |
| Qcache_hits | 23547551 |
| Qcache_inserts | 46909131 |
| Qcache_lowmem_prunes | 5110536 |
| Qcache_not_cached | 2760196 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
There were 46909131 queries and out which 23547551 queries cached and remaining not cached. Here the issue will either the result is greater than query_cache_limit or greater than query_cache_size itself. You have to trial and monitor 🙂
When a query is removed from the query cache, this value will be incremented. If it increases quickly, and you still have memory to spare, you can set query_cache_size high, If it never increases, you can reduce the cache size.

The sort_buffer is a useful for speed up myisamchk operations. It can also be useful when performing large numbers of sorts.


This variable determines the maximum size for a temporary table in memory. The maximum in memory size is minimum of tmp_table_size and max_heap_table_size. You can compare
Created_tmp_disk_tables and Created_tmp_tables to optimize tmp_table_size.


This variable is target for innodb table and it is similar to key_buffer_size in MyISAM table.
On a dedicated mysql server using innodb you can set this upto 80% of RAM.
Hardware for mysql
If you have large tables(>3GB), you should consider 64 bit hardware as mysql uses a lots of 64bit integers internally.

You need more memory(RAM) if you want mysql to handle large number of connections simultaneously. More RAM will speed up key updates by keeping most of the pages in RAM

Another consideration is Ethernet device, You can use a 1G Ethernet for a dedicated mysql server for fast remote connections.

Disk performance is also an important parameter.
Disk Optimization
Striping disk (RAID 0) will increase both read and write throughput.

Don’t use RAID 1 or mirroring on disk for temporary files.

On Linux, mount the disks with async (default) and noatime.
Optimizing your application
Cache process in your application

Specify the column name in queries(eg dont use SELECT * FROM……)

Use persistent connections

USE EXPLAIN to explain!!.You will see detail below.

Queries and Indexes

Let us start with a simple query SELECT firstname FROM student WHERE id=’145870′;
MySQL start searching from the beginning row to find the student with id 145870. It does not even know it exist or not. An index is a sorted file which have an entry for each row.MySQL can find the corresponding record very quickly by referring this index.
EXPLAIN is a nice tool to understand your queries

EXPLAIN SELECT firstname,lastname FROM student WHERE id=’145870′;

| table | type | possible_keys | key | key_len | ref | rows | Extra |
| student | ALL | NULL | NULL | NULL | NULL |10000 | where used |
The possible_keys is null. In this case mysql will check all the 10000 rows. We can say this query(or table) is not optimized.

Now suppose we have use index for above table and run EXPLAIN again then we will get
| table | type | possible_keys | key | key_len | ref | rows | Extra |
| employee | const | PRIMARY | PRIMARY | 10 | const | 1 | |
The type is “const”, which means that the table has only one matching row. The primary key is being used to find this particular record.

There are many more optimization variables and indxing methods. It is difficult include everything in a single article. But you can start mysql fine tuning while you database is underperfoming.