Wednesday, 29 May 2013

Master Slave Configuration in Mysql

CONTENT TAKEN BY GIVEN URL:-

http://erlycoder.com/43/mysql-master-slave-and-master-master-replication-step-by-step-configuration-instructions-

http://studioshorts.com/blog/2010/03/mysql-master-slave-replication-on-centos-rhel/

MySQL master-slave and master-master replication. Step by step configuration instructions.

If you are looking for the options to csale your MySQL installation you may be also interested in MySQL partitioning and subpartitioning. It may improve each node speed and capacity parameters.

One may say that there are a lot of MySQL replication manuals, but latest versions of MySQL server have changed the way how configuration should be applied. Most of the manuals do not reflect these changes. I want to describe some other aspects of configurations also. As far as there are a lot of good manuals about replication, I think there is no need to dove into details what is the replication. Just want to mention that this technique is usually used for load balancing on database servers. If you have a lot of read requests (most common for web applications) master-slave replication should suit your needs well. In this case you will do write transactions on master host and read requests on slave hosts, because data is populated from master to slave much faster than from slaves to master and to other slaves.

But sometimes you might have more write requests or may have other (application related) reasons to start another type of replication. You can see it on the next fugure and that is so called master-master replication.

In this article I will describe simple master-slave architecture with 2 hosts and simple master-master replication with the same 2 hosts. Our final goal is to configure master-master replication, what includes several sub-steps, so lets start. Sure you should configure network services on both systems. For example:

Master 1/Slave 2 ip: 192.168.16.4
Master 2/Slave 1 ip : 192.168.16.5

Iptables rules for MySQL replication

It will be good practice to allow connections only from other nodes envolved into the replication and deny from other. By the way this will work good for some other services that are allowed to communicate nly with the known hosts. You can define port range like 1025:3306 (I am going to write more about iptables soon, so follow my blog on twitter).


iptables -A INPUT -p tcp -s 192.168.16.4 --sport 3306 -d 192.168.16.5 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 192.168.16.5 --sport 3306 -d 192.168.16.4 --dport 3306 -m state --state ESTABLISHED -j ACCEPT

MySQL master-slave replication

Basically master-master replication consists of two master-slave replications. Now we will configure master-slave replication from the first server to the second one.

Create relication user on Master 1:


mysql> grant replication slave on *.* to 'replication'@192.168.16.5 identified by 'slave';

And start master:


mysql> start master;

Master 1 changes to /etc/my.cnf:


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1


log-bin
binlog-do-db=<database name>  # input the database which should be replicated
binlog-ignore-db=mysql            # input the database that should be ignored for replication
binlog-ignore-db=test


server-id=1


[mysql.server]
user=mysql
basedir=/var/lib




[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Slave 1 changes to /etc/my.cnf:


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1


server-id=2


[mysql.server]
user=mysql
basedir=/var/lib


[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Important! Pay attention that you should not configure master-host, master-user, master-password, master-port via my.cnf on slave server now.

On Master 1:


mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
|mysqld-bin.000012       |      106 | adam         |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

On Slave 1:


mysql> CHANGE MASTER TO MASTER_HOST='192.168.16.4', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_PORT=3306,MASTER_LOG_FILE='mysqld-bin.000012', MASTER_LOG_POS=106, MASTER_CONNECT_RETRY=10;

Attention! This will configure slave and server will remember settings, so this replaces my.cnf settings in latest versions of MySQL server.

Start slave on Slave 1:


mysql> start slave;
mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.16.5
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysqld-bin.000012
          Read_Master_Log_Pos: 1368129
               Relay_Log_File: mysqld-relay-bin.000005
                Relay_Log_Pos: 605530
        Relay_Master_Log_File: mysqld-bin.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1368129
              Relay_Log_Space: 1367083
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.02 sec)

Above highlighted rows must be indicate related log files and Slave_IO_Running and Slave_SQL_Running: must be to YES.

MySQL master-master replication

Master-master replication is actually two master-slave replications. This allows to make read and write transactions on both servers, as data propagation from master to slave goes very fast oposit to data propagation from slave to master which requires much more time. So, to create master-master replication we should now configure Master 2 - Slave 2 replication.

Create a replication slave account on Master 2 for Master 1/Slave 2:


mysql> grant replication slave on *.* to 'replication'@192.168.16.4 identified by 'slave';

And start master:


mysql> start master;

Master 2 changes to /etc/my.cnf:


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1


log-bin
binlog-do-db=<database name>  # input the database which should be replicated
binlog-ignore-db=mysql            # input the database that should be ignored for replication
binlog-ignore-db=test


server-id=2


[mysql.server]
user=mysql
basedir=/var/lib




[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Slave 2 / Master 1 changes to /etc/my.cnf:


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1


log-bin
binlog-do-db=<database name>  # input the database which should be replicated
binlog-ignore-db=mysql            # input the database that should be ignored for replication
binlog-ignore-db=test


server-id=1


[mysql.server]
user=mysql
basedir=/var/lib


[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Important! And again you should not configure master-host, master-user, master-password, master-port via my.cnf on slave server now.

On Master 2:


mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
|mysqld-bin.000012       |      106 | adam         |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

On Slave 2:


mysql> CHANGE MASTER TO MASTER_HOST='192.168.16.5', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_PORT=3306,MASTER_LOG_FILE='mysqld-bin.000012', MASTER_LOG_POS=106, MASTER_CONNECT_RETRY=10;

Attention! As I have already mentioned in the previous section this will configure slave and server will remember settings, so this replaces my.cnf settings in latest versions of MySQL server.

MySQL master-master replication and autoincrement indexes

If you are using master-slave replication, than most likely you will design your application the way to write to master and read from slave or several slaves. But when you are using master-master replication you are going to read and write to any of master servers. So, in this case the problem with autoincremental indexes will raise. When both servers will have to add a record (different one each server simultaneously) to the same table. Each one will assign them the same index and will try to replicate to the salve, this will create a collision. Simple trick will allow to avoid such collisions on MySQL server.

On the Master 1/Slave 2 add to /etc/my.cnf:


auto_increment_increment= 2
auto_increment_offset   = 1

On the Master 2/Slave 1 add to /etc/my.cnf:


auto_increment_increment= 2
auto_increment_offset   = 2





THIS CONTENT tAKEN bY gIVEN uRL:-




http://studioshorts.com/blog/2010/03/mysql-master-slave-replication-on-centos-rhel/






Today I set up some MySQL replication for a server that I set up a few weeks ago. This was my first time doing replication and it was quite a learning experience. This was using MySQL 5.0 and CentOS 5.3, but this should work for most semi-recent versions of both.
In this setup, transactions are mirrored to the slave server as they happen on the master.

In this tutorial I’ll use the following setup:
Master Server: 10.1.100.1
Slave Server: 10.2.200.2
MySQL Data path: /var/lib/mysql
MySQL slave user named slave_user


[Master]

First, edit the master server MySQL config file. Add/Replace the following lines



1

vim /etc/my.cnf



# [mysqld] section
# Start Modification
# First line is probably already there
datadir = /var/lib/mysql
server-id = 1
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/var/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin
# Stop Modification


Restart MySQL to load the changes



1

service mysqld restart




[Slave]

Now we’ll do about the same thing on the slave server



1

vim /etc/my.cnf



# [mysqld] section
# Start Modification
# First line is probably already there
datadir = /var/lib/mysql
server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/var/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
# Stop Modification


Restart MySQL to load the changes



1

service mysqld restart




[Master]

Now we need to tell MySQL where we are replicating to and what user we will do it with.



1
2
3
4

mysql -u root -p
mysql> STOP SLAVE;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';
mysql> FLUSH PRIVILEGES;




Now we will test that this side of the replication is working and get the location that we will start the replication from.



1
2
3
4
5
6
7
8
9

mysql> USE Any_database_name;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | POSITION | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |   451228 |              |                  |
+------------------+----------+--------------+------------------+
1 ROW IN SET (0.00 sec)




Write down the File, Position number, as this is where we will start the replication from.
Here it is a good idea to do a dump of your master database(s) and pipe it into your slave server.



1

mysqldump -u root --all-databases --single-transaction --master-data=1 > /home/MasterSnapshot.sql




[Slave]

First grab your SQL dump file from the master server. You can use whatever method you would like to transfer the file. SCP example:



1

scp root@10.1.100.1:/path/to/MasterSnapshot.sql root@10.2.200.2:/home/MasterSnapshot.sql




Import the SQL file into MySQL



1

mysql -u root -p < /home/MasterSnapshot.sql




Now we’ll set the slave to read from the master server, starting at the record position we wrote down earlier. Make sure you use the MASTER_LOG_FILE and MASTER_LOG_POS from a few steps back.



1
2
3

mysql> CHANGE MASTER TO MASTER_HOST='10.1.100.1', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=451228;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;




Make sure that from the resulting output you have the following:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes





----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



Master1

grant replication slave on *.* to 'root'@'172.31.22.57' identified by 'admin123';

GRANT REPLICATION SLAVE ON *.* TO 'root'@'172.31.22.57' IDENTIFIED BY 'admin123';




Slave1/master2

CHANGE MASTER TO MASTER_HOST='172.31.22.52', MASTER_USER='root', MASTER_PASSWORD='admin123', MASTER_PORT=3306,MASTER_LOG_FILE='mysqld-bin.000006', MASTER_LOG_POS=66302851, MASTER_CONNECT_RETRY=10;







CHANGE MASTER TO MASTER_HOST='172.31.22.56', MASTER_USER='root', MASTER_PASSWORD='admin123', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=310;







master2/slave1

grant replication slave on *.* to 'root'@'172.31.22.56' identified by 'admin123';




GRANT REPLICATION SLAVE ON *.* TO 'root'@'172.31.22.56' IDENTIFIED BY 'admin123';




slave2/master1:




CHANGE MASTER TO MASTER_HOST='172.31.22.53', MASTER_USER='root', MASTER_PASSWORD='admin123', MASTER_PORT=3306,MASTER_LOG_FILE='mysqld-bin.000020', MASTER_LOG_POS=89253722, MASTER_CONNECT_RETRY=10;

























iptables -A INPUT -p tcp -s 172.31.22.56 --sport 3306 -d 172.31.22.57 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT




iptables -A INPUT -p tcp -s 172.31.22.57 --sport 3306 -d 172.31.22.56 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT

Thursday, 23 May 2013

Mysql Questions

//Delete Duplicate record from Mysql ... If you want to keep the row with the lowest id value: DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name If you want to keep the row with the highest id value: DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name I used this method in MySQL 5.1 //mysql query SELECT w.`FIRST_NAME`,w.`LAST_NAME`,w.`SALARY`,t.WORKER_TITLE from worker as w,title as t where w.`WORKER_ID`=t.WORKER_REF_ID and w.`SALARY`in( select max(SALARY) as SALARY from worker where SALARY < (select max(SALARY) from worker where SALARY)) SELECT SALARY FROM worker e where 2= (SELECT COUNT(DISTINCT SALARY) from worker p where e.SALARY<=p.SALARY) SELECT SALARY FROM worker e WHERE 2= (SELECT COUNT(DISTINCT SALARY) FROM worker p WHERE e.SALARY<=p.SALARY)

mysql authentication

GRANT REPLICATION SLAVE ON *.* TO 'root'@'172.31.22.56' IDENTIFIED BY 'admin123';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.31.22.54' WITH GRANT OPTION;

GRANT REPLICATION SLAVE ON *.* TO 'root'@'172.31.22.54' IDENTIFIED BY 'admin123';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.31.22.54' IDENTIFIED BY 'admin123';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.31.22.54' IDENTIFIED BY 'admin123';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'admin123';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'admin123';

DROP USER 'root'@'%';

DROP USER 'root'@'172.31.22.58';

drop USER 'root'@'localhost' where Host='localhost'

CREATE USER 'root'@'%' IDENTIFIED BY 'admin123';

drop USER 'root'@'%' where Password='admin123'

CREATE USER 'root'@'%' IDENTIFIED BY 'Cyberlinks';

CHANGE MASTER TO MASTER_HOST='172.31.22.53', MASTER_USER='root', MASTER_PASSWORD='admin123', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=66883888;

CHANGE MASTER TO MASTER_HOST='172.31.22.53', MASTER_USER='root', MASTER_PASSWORD='admin123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=23713;

Mysql Questions

This article introduces MYsql Database queries:

Suppose we have to tables
1.Employee
2.Employee Salary

CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`photo` varchar(100) DEFAULT NULL,
`first_name` varchar(100) DEFAULT NULL,
`last_name` varchar(100) DEFAULT NULL,
`gender` varchar(11) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`mobile` varchar(100) DEFAULT NULL,
`password` varchar(100) DEFAULT NULL,
`active` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

employees

id name
1 Admin
2 Employee1
3 Employee2
4 Employee3
5 Employee4

CREATE TABLE `employee_salary` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`employee_id` int(10) DEFAULT NULL,
`salary` varchar(50) DEFAULT NULL,
`created_on` datetime DEFAULT NULL,
`created_by` int(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=latin1

id employee_id salary
1 1 10000
2 2 20000
3 3 10000
4 4 40000
5 5 30000

1. Find Second highest paid employees withou using LIMIT function.

Query:

SELECT MAX(salary) FROM employee_salary WHERE salary <(SELECT MAX(salary) FROM employee_salary)

2.Skip repeating values

Query:
SELECT salary FROM employee_salary GROUP BY salary

3.Reset Auto-increment next value

Query:
ALTER TABLE tablel SET AUTO_INCREMENT=val;

4.Find the Size of database

Query:
SELECT
s.schema_name AS 'database',
SUM(t.data_length) AS DATA,
SUM( t.index_length ) AS INDEXES,
SUM(t.data_length) + SUM(t.index_length) AS 'Mb Used',
IF(SUM(t.data_free)=0,'',SUM(t.data_free)) AS 'Mb Free',
COUNT(table_name) AS TABLES
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON s.schema_name = t.table_schema
GROUP BY s.schema_name

5.First and Last Date of Month

Last day of next month:

SELECT LAST_DAY (DATE_ADD(CURDATE(), INTERVAL 30 DAY))

Last day of Previous month:

SELECT LAST_DAY (DATE_SUB(CURDATE(), INTERVAL 30 DAY))

First day of next month

SELECT CONCAT(LEFT(CURDATE() + INTERVAL 1 MONTH, 8), '01');

6.Display every Nth row

Query:
SELECT id
FROM employees
GROUP BY id
HAVING MOD(id, N) = 0;

7.Age in years

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS('1991-07-01')), '%Y') + 0;

8.Difference between two dates

SELECT DATEDIFF('2002-07-15','2001-07-15')

9. Update Table using Join

UPDATE employee e LEFT JOIN employee_salary s ON e.id=s.employee_id
SET s.salary='50000'
where e.first_name='Admin'

10. DELETE Table using Join

Delete employee_salary FROM employee_salary LEFT JOIN employee ON employee.id=employee_salary.employee_id
WHERE employee.first_name='Admin'

11- How insert male and female table data in other table .

t1 table t2 table

id name id name

1 manoj 1 neha

2 ravi 2 rubi

3 satntosh 3 isha

Merge above tble in t3 like

Id Name Type

1 manoj M

2 ravi M

3 satntosh M

4 neha F
5 rubi F

Ans:-

INSERT INTO t3( name,TYPE ) (SELECT name, 'M' AS TYPE FROM t1) UNION (SELECT name, 'F' AS TYPE FROM female )

INSERT INTO t3( name, salery,TYPE ) (SELECT name, salery, 'm' AS TYPE FROM male)
UNION
(SELECT name, salery, 'f' AS TYPE FROM female )

Q:- select emp coming Birthday sql query
Ans: select replace(dob,Year(dob),Year(curdate())) as dob from emplist having dob <= date_add(curdate(), INTERVAL 15 DAY) and

dob >= curdate();

Question 2nd highest value in array?

ans : 1st create bubble sort ascending or descending then print 2 no of array
according to order :--

<?php
/*
function __autoload($class_name) {
include "test/".$class_name . '.php';
}

$obj = new MyClass1();
//$obj2 = new MyClass2();
*/

class bubchek {
function swap($items,$firstIndex,$secondIndex){
$temp =$items[$firstIndex];

$items[$firstIndex]=$items[$secondIndex];

$items[$secondIndex]= $temp;

return $items[$secondIndex];

}
function buble_sort($items){
//echo $items;
$len= count($items);
for($i=0;$i<$len;$i++){

for($j=0;$j<$len-$i-1;$j++){
//echo $items[$j];
//echo $items[$j+1];
if($items[$j] > $items[$j+1]){
//echo $j;
//echo $items[$j];
//$this->swap($items,$j,$j+1);

$temp =$items[$j];

$items[$j]=$items[$j+1];

$items[$j+1]= $temp;

}

}

}
return $items;

}

}
$obj = new bubchek();
//$vaarray=array(3,2,4,5,1);

$chkval =$obj->buble_sort(array(3,2,4,5,1));

print_r($chkval);

?>

==============================================================

<?php

//how to find highest and second highest number in an array without using max function
/*
$array = array('200', '15','69','122','50','201');
$max_1 = $max_2 = 0;
//$arraval= array();

for($i=0; $i<count($array); $i++)
{
if($array[$i] > $max_1)
{
$max_2 = $max_1;
$max_1 = $array[$i];
}
else if($array[$i] > $max_2)
{
$max_2 = $array[$i];
}
}
echo "Max=".$max_1;
echo "<br />";
echo "Smax 2=".$max_2;

*/

//Find string palindrome or not in PHP without using strrev()

$mystring="LEVEL";
$newstrarray= array();
$newstrarray=str_split($mystring);
$len=sizeof($newstrarray);
$newstring = "";
for($i=$len;$i >= 0;$i--){
$newstring.=$newstrarray[$i];

}
echo $newstring ;
echo "<br>";
if($mystring == $newstring){

echo "parldorme".$mystring;
}else{

echo "Not===parldorme".$mystring;

}

//Sort an array of integers without using PHP built-in sort functions
/*

$srtArray=array(2,8,9,5,6,3);
for ($i=0; $i<count($srtArray); $i++) {
for ($j=0; $j<count($srtArray); $j++) {
// Compare two elements of array
if ($srtArray[$j] > $srtArray[$i]){
$tmp = $srtArray[$i];
$srtArray[$i] = $srtArray[$j];
$srtArray[$j] = $tmp;
}
}
}
//Print an array after sorting
for($i=0;$i<count($srtArray);$i++){
echo $srtArray[$i]."<br>\n";
}

*/

?>

Que: reverse name without using any function:

<?
//reverse string with out using any function.

$num=3456;
$rev=0;
$num=0;
while($num>0){

$rev = $rev*10;
$rev=$rev+$num%10;
$num = (int)($num/10);
}
echo $rev;

echo "==========================================================";
$a='Manoj';

for($i=0;$i<=5000;$i++)
if($a[$i])
$count++;
else

break;

for($j=$count;$j>=0;$j--){
echo $a[$j];
}

?>

finish

=============

Question site

https://www.toptal.com/sql/interview-questions

Apache performance as well as MySQL Performance

For Apache :-

1- Prefork and worker are two type of MPM apache provides. Both have their merits and demerits.

By default mpm is prefork which is thread safe.

Prefork MPM uses multiple child processes with one thread each and each process handles one connection at a time.

Worker MPM uses multiple child processes with many threads each. Each thread handles one connection at a time.

2- Apache is the most common and famous webserver. Everyone knows about apache and most of us also have hands on experience with apache. But few of us know that apcahe2 comes with 2 multi processing modules(MPMs):

1. Prefork
2. Worker

3. What is the difference between this two?

Prefork MPM uses multiple child processes with one thread each and each process handles one connection at a time.

Worker MPM uses multiple child processes with many threads each. Each thread handles one connection at a time.

On most of the systems, speed of both the MPMs is comparable but prefork uses more memory than worker.

Which one to use?

On high traffic websites worker is preferable because of low memory usage as comparison to worker MPM but worker is more safe if you are using libraries which are not thread safe.

For example you cannot use mod_php(not thread safe) with worker MPM but can use with prefork.

So if you are using all thread safe libraries then go with worker and if you are not sure then use default prefork MPM, you may have to increase your RAM in case of high traffic.

If you are on linux then run following command to check which MPM is on your machine

1	/usr/sbin/apache2 -V \| grep MPM

for More Please review Given URL:-

http://codebucket.co.in/apache-prefork-mpm-configuration/

http://codebucket.co.in/apache-prefork-or-worker/

Apache Prefork MPM Configuration

Published by Arvind Saini on November 9, 2012 | Leave a response

1
inShare

If you have installed a new apache web server(prefork MPM), planning to launch an online application and exptecting good amount of traffic then before going live, check apache settings for prefork mpm otherwise your server will not able to serve even 20-30 users. Why, becouse apache default setting are not optimized for high traffic, you have to adjust prefork settings based on your application and hardware(RAM,Swap etc).

Before I will start explaining prefork configuration, if you are not aware of prefork mpm or how to check apache memory details then read following blogs before proceeding further:

Prefork Vs Worker
Apache Memory Details

Open your apache config file or run following command

gedit /etc/apache2/apache2.conf

Search prefork setting block, which look like this

StartServers 5

MinSpareServers 5

MaxSpareServers 10

MaxClients 150

MaxRequestsPerChild 0

</IfModule>

Above is the default settings of apache prefork mpm.

StartServers: The StartServers directive sets the number of child server processes created on startup. As the number of processes is dynamically controlled depending on the load, there is usually little reason to adjust this parameter.

MaxSpareServers: The MaxSpareServers directive sets the desired maximum number of idle child server processes. An idle process is one which is not handling a request. If there are more than MaxSpareServers idle, then the parent process will kill off the excess processes.
Tuning of this parameter should only be necessary on very busy sites. Setting this parameter to a large number is almost always a bad idea. If you are trying to set the value lower than MinSpareServer.

MinSpareServers: The MinSpareServers directive sets the desired minimum number of idle child server processes. An idle process is one which is not handling a request. If there are fewer than MinSpareServers idle, then the parent process creates new children at a maximum rate of 1 per second.
Tuning of this parameter should only be necessary on very busy sites. Setting this parameter to a large number is almost always a bad idea.

MaxClients: The MaxClients directive sets the limit on the number of simultaneous requests that will be served. Any connection attempts over the MaxClients limit will normally be queued. Once a child process is freed at the end of a different request, the connection will then be serviced.
Generally number of MaxClients=(Total RAM memory – RAM memory used for other process except Apache process) / (Memory used by Single Apache process)

MaxRequestsPerChild: The MaxRequestsPerChild directive sets the limit on the number of requests that an individual child server process will handle. After MaxRequestsPerChildrequests, the child process will die. If MaxRequestsPerChild is 0, then the process will never expire.
You can keep it high (20000-25000) but never set it to 0.

Above mentioned setting are for start purpose only. To get best optimized setting you have to analyze sever performance on different load(for that you can use jmeter) and accordingly change the settings.

Using a MySQL Performance Tuning Analyzer Script

When you are working on increasing the speed of your website, a very important piece is making sure you get every last drop of performance out of your database server. Unfortunately, for most of us that aren’t normally database administrators this can be a difficult proposition.

There’s a number of performance tuning scripts that will analyze your server settings and current status and give you information on recommended changes that you should make. You shouldn’t necessarily follow all of the suggestions, but it’s worthwhile to take a look at anyway.

The script I’ve been using gives you recommendations for the following:

· Slow Query Log

· Max Connections

· Worker Threads

· Key Buffer

· Query Cache

· Sort Buffer

· Joins

· Temp Tables

· Table (Open & Definition) Cache

· Table Locking

· Table Scans (read_buffer)

· Innodb Status

Once you download the script, you’ll need to make it executable with the following command:

chmod u+x tuning-primer.sh

If you run this script as a regular user, it will prompt you for your password, so you’ll have to make sure to set access accordingly. If you run it as root it’ll pick up the mysql password from Plesk if you have that installed.

I’ve cut out a lot of the output, which had a lot more recommendations, but was just too long to fit on the page.

./tuning-primer.sh

— MYSQL PERFORMANCE TUNING PRIMER –
– By: Matthew Montgomery –

MySQL Version 4.1.20 i686

Uptime = 5 days 10 hrs 46 min 5 sec
Avg. qps = 4
Total Questions = 2020809
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

———– snipped ————–

QUERY CACHE
Query cache is enabled
Current query_cache_size = 8 M
Current query_cache_used = 7 M
Current query_cach_limit = 1 M
Current Query cache fill ratio = 89.38 %
However, 254246 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size
MySQL won’t cache query results that are larger than query_cache_limit in size

———– snipped ————–

Looks like I need to increase my query cache… I set it to only 8MB but it’s cleaning out the cache far too often.

———– snipped ————–

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 35170 temp tables, 74% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

———– snipped ————–

This type of information is just invaluable when you are trying to tune the performance of your website.

Download MySQL Performance Tuning Primer Script

some important URL s

http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

Tuesday, 21 May 2013

Memcache Install in Red Head Linux server

Q:-How to memcache in linux(Red Head) plateform?

Ans:-

1- put libevent-1.4.9-stable(OR higher Version) and memcached-1.3.0(OR higher Version) in /usr/src folder and memcache-2.2.7(Or higher version) in /usr/local/src.

2-then in linux prompt go to

cd /usr/src
cd libevent-1.4.9-stable/
./configure
make
make install
Then open file in Vi Editor using following command:-
vi /root/.bash_profile
then append (in not exist) LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH

Then after save it using :wq.

3:- sudo ln -s /usr/local/lib/libevent-1.4.so.2 /usr/lib

4:- cd memcached-1.3.0/

5:- ./configure

6:- make

7:- make install

8:- memcached -d -m 1024 -u root -l 127.0.0.1 -p 11211 .u nobody
OR
memcached -d -m 1024 -u root -l 127.0.0.1 -p 11211(OR USING BOTH),

9:- cd /usr/local/src

10:- cd memcache-2.2.7/(all ready available this rpm in particular directory ).Then

phpize
./configure
make && make install

Then Open following file in vi editor

vi /etc/php.ini

append extension=memcache.so

Then use given below command

cp modules/memcache.so /usr/lib64/php/modules/

command in linux

touch /etc/php.d/memcached.ini

echo 'extension=memcache.so' > /etc/php.d/memcached.ini

then in vi editor

vi /etc/ld.so.conf

include include ld.so.conf.d/*.conf

service httpd restart(Restart apache).

I hope above Doc will help u.

Thanks