MySQL

High Availability MySQL

Basic Guideline for High Availability MySQL Replication DRBD HA We are discussing DRBD HA here. So first things to go is DRBD HA Following Steps are required: Preparation of OS : DRBD Preparation OS : We need to reserve a huge physical volume which would be later used as a DRBD volume. Don’t specify any file system type. fdisk /dev/sda…

Continue Reading

MySQL

The BLACKHOLE Storage Engine

The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result: mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE; Query OK, 0 rows affected (0.03 sec)   mysql> INSERT INTO test VALUES(1,’record one’),(2,’record two’); Query OK, 2 rows affected (0.00 sec) Records: 2  Duplicates:…

Continue Reading

MySQL

MySQL Slow Query Log

Start MySQL Server: /etc/init.d/mysqld start Syntax for /etc/my.cnf in Red Hat Family Machine : [mysqld] set-variable=long_query_time=1 log-slow-queries=/var/log/mysql/log-slow-queries.log log-queries-not-using-indexes The 2nd line under [mysqld] turns on slow query log and logs all slow queries to log-slow-queries.log in the MySQL data directory. The 1st line indicates that any queries that took more than 1 seconds to execute need to be logged. The…

Continue Reading

MySQL

Common Replication Administration Tasks

Common Replication Administration tasks : 1. Checking the Replication Status. The most common task when managing a replication process is to ensure that replication is taking place and that there has been no errors between the slave and the master. On Slave Server (192.168.36.2) mysql>     Show slave statusG The key fields from the status report to examine are: Slave_io_state: indicates…

Continue Reading

MySQL

MySQL Replication

Implementing Master-Slave Replication: Master-Slave replication is mainly for scale-out solutions- spreading the load among the multiple slaves to improve the performance. And for Analytics – while the analysis of the information can take place on the slave without affecting the performance of the master. The target uses for Replication in MySQL include: Scale-out solutions: spreading the load among multiple slaves…

Continue Reading

Linux, MySQL

MySQL Performance

MySQL Performance: Replication, Partition and Memcached 3 main step to increase mysql performance: Split Read and Write Horizontally partition Implement Memchache Setup Replication You can spread your reads with replication, and that helps a lot, but you can’t spread writes (they have to process on all machines) and they’ll eventually consume all your resources. You’ll find yourself adding replicated slaves…

Continue Reading

Linux, MySQL

MySQL Step-by-Step Installations

If you are on Fedora or Red Hat Box $ setenforce 0 #disbles selinux temporarily $ yum install mysql If you have a rpm then $ rpm -i /mysqlSRC_RPM_Folder/MySQL* You can download rpm’s from http://www.mysql.org $ restorecon -R /var/lib/mysql/ #restore selinux labels $ setenforce 1 #enable selinux again Now, I successfully installed MySQL. Now I wanted to change the location…

Continue Reading

MySQL

MySQL’s FEDERATED storage engine

One of the most exciting features introduced in MySQL 5 is the federated engine. The ability to access data from a remote server without the constraints of replication tickles every programmer’s fancy. The Federated engine allows a DBA to create logical pointers to tables that exist on other MySQL servers and thereby link together separate data islands to form one…

Continue Reading

MySQL, PHP

FullText Search Solutions

Types of FullText Search Solutions Special Database Features MySQL Full Text Search, Sienna Solutions exists for PostgreSQL, Oracle and many others Home baked database based solutions Using stored procedures or set of queries and keyword tables for search External Full Text Search Solutions Lucene, Sphinx, Mnogosearch etc. My Thoughts on Performance Lucene and Sphinx both can handle large data sizes…

Continue Reading