mysql query cache

Mysql 4+ has a feature known as query cache. Here mysql caches the result set. So suppose a query is run and it takes 5 seconds to run and query cache is enabled, so results are cached in the cache. Next time if the same query is run again (remember - exactly same query that is strcmp(old_query, new_query) == 0) then the results are fetched from the cache and shown. And this takes very less time - say only 0.1 seconds.

I think, all of you who would be working with mysql for some time now, would be aware of this feature. The above para was just to refresh your memories.

Now lets check out the variables in mysql configuration file (my.cnf) which control the query cache.

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)

have_query_cache says whether mysql supports query cache.

query_cache_limit Dont cache results which are larger than this size. By default it is 1 MB. If your result set is larger, you can increase it as you like.

query_cache_min_res_unit The minimum size for blocks allocated by query cache. Default is 4096 Bytes (4KB). Will talk about this later.

query_cache_size Amount of memory allocated for caching results. Default is 0 - which disables query cache. You can set it to 128 MB or 1 GB. Depending on the memory available with your machine

query_cache_type 0 or OFF would turn query caching off. 1 or ON would turn the query cache on and the result set of every mysql query would then be cached. 2 or DEMAND would enable query cache but all result sets wont be cached. To cache results in this case you will have to specify "SQL_CACHE" in the query.

query_cache_wlock_invalidate Setting this variable to 1 causes acquisition of a WRITE lock for a table to invalidate any queries in the query cache that refer to that table. This forces other clients that attempt to access the table to wait while the lock is in effect.

Now lets see how query cache works and how to tune it.

mysql> show status like '%qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 7 |
| Qcache_free_memory | 133638224 |
| Qcache_hits | 284 |
| Qcache_inserts | 626 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 550 |
| Qcache_total_blocks | 1116 |
+-------------------------+-----------+
8 rows in set (0.00 sec)


Qcache_free_blocks Number of free memory blocks in query cache

Qcache_free_memory Amount of free memory in query cache

Qcache_hits Number of hits to the query cache. Or, the number of times a query was found in the query cache.

Qcache_inserts Number of queries inserted in the query cache.

Qcache_lowmem_prunes Number of queries that where deleted from the query cache due to low cache memory.

Qcache_not_cached Number of not-cached queries

Qcache_queries_in_cache Number of queries registered in the query cache

Qcache_total_blocks Total number of blocks in the query cache

So as and when queries are inserted in the cache, the Qcache_inserts and Qcache_queries_in_cache would increase. Qcache_free_memory would ofcourse decrease. Whenever any DML query is run on a table, the queries in the cache related to that table are removed.

Some variables which let us know the efficiency of the query cache :

If the number of Qcache_hits is less than the number of queries_in_cache then the queries cached are not being used efficiently. And if Qcache_not_cached increases very quickly then queries are not being cached. This could be due to the fact that the result set of the queries are bigger than the variable query_cache_limit. So you should then increase this variable from its default value of 1M to 2M or maybe more.

If the variable Qcache_low_mem_prunes is increasing very fast, it would mean that the memory allocated to query cache is low. Cause mysql is freeing up memory to allocate new queries. Mysql is indirectly asking you to increase the query_cache_size

Mysql allocated memory for query result set in blocks. The default block size is 4K. So Qcache_free_blocks can be an indication of fragmentation. A high number as related to the Qcache_total_blocks means that the cache memory is seriously fragmentation. If the result set size is much less than 4K then fragmentation is high. There is another variable query_cache_min_res_unit which could then be used to decrease the block size from 4K to maybe 2K and help reduce fragmentation.

I had come across a similar issue on one of my servers. Where Qcache_free_memory was high, Qcache_total_blocks was also good. But Qcache_free_blocks was high and Qcache_lowmem_prunes was increasing very fast. And the server was performing very badly. What actually happened was that the cache memory was seriously fragmented. So almost every query was being executed and inserted in the query cache which would replace another query. The insertion and removal of queries from the query cache was taking up extra resources in addition to the resources needed for executing the query. This situation called for defragmentation of the query cache memory. So i ran FLUSH QUERY CACHE. It brought down Qcache_free_blocks and brought the increment of Qcache_lowmem_prunes to a crawl. And automatically the server performance improved. Now the query cache is flushed every 24 hours.

MySQL query cache is a very efficient tool if used properly.

mysql wait_timeout

There is an issue with mysql if you are using mysql_pconnect.

What happens is that if you have configured http or apache for 150 max connections and mysql for 100 max connections and you are using mysql_pconnect then each http thread (supposing you are running php scripts only) creates a connection with mysql. And maintains the connections. So what happens, the first 100 http take up the 100 mysql connections and then even if the query is not over, dont free them. And the 101th http gives an error Mysql:Too many connections.

The way to handle this is

Dont use mysql_pconnect for connecting with the mysql database from php scripts. Use mysql_connect.

I have come across cases when even when mysql_pconnect is not being used, still the mysql connections are used up and though mysql does not show any query in its processlist, still all the threads of mysql are still live and are in sleep state. Now that's strange.

I had a server which was performing perfectly fine. And suddenly one day i start getting "too many connections" in the server. And on checking the processlist i found that all threads of mysql are in sleep state. That's strange. Suddenly one day the server starts misbehaving. And even on restarting the server, it does not behave properly. As soon as the server is restarted, the number of connections get maxed out.

There is one variable in mysql which can save the day. The "wait_timeout". By default it is set to 28840 (or something like that). And it defines the number of seconds a mysql thread waits in idle state before mysql preempts and kills it. So all you have got to do is to change this variable to something like 30 or so. For that you will have to run a "show processlist" and check the maximum time of a thread in sleep state and what time ought to suit your environment.

simply run

set global wait_timeout = 30

in mysql prompt and your task is done. But better edit the my.cnf and save it there, so that it is incorporated when mysql is restarted. With this done, no thread of mysql will remain active if it is idle for over 30 seconds. And a query running for over 30 seconds would be allowed to execute since that is not an idle thread.

bike VS car

This is just something that i want to write. It has been there in my mind for quite some time now.

People tell me that i should always travel by car. And people want to buy a car more than they want to buy a bike. It is more of a status symbol. And my friends say that they use a car cause they believe that it is safer than a bike in high traffic area like delhi.

I own both a car and a bike. My car resides at its place almost 5 days a week and i take it out only on week ends - for "leisure" driving. Though there is so much traffic in delhi that there is no "leisure" driving option available here. I always land up in a traffic jam. And spend hours honking, shifting gears and moving slowly in bumper to bumper traffic - always concerned whether someone would scratch or dent my car.

Here comes my bike to the rescue. Just a kick and twist of throttle and i can wriggle between all the cars. without being concerned whether anyone would scratch my bike. There is not enough stuff to scratch here. Though i despise people who if possible would bodily push you to one side and drive out. They dont care a damn about their bike and would try to wriggle even from the smallest hole available. These are the people who scratch cars and cause fights on the road.

Well, in addition to this, a bike also gives me the advantage of driving though even the narrowest lanes in delhi. And if i see a jam ahead, i can turn left or right and take another path without worrying too much.

Though a car ensures safety to the driver and passenger from the killer busses of delhi. But for short distances, i would prefer a bike rather than a car. And ofcourse for long distances, car seems better. I wish i could get bikes with AC at cheaper costs in india.

But that's not all. The pleasure of riding a bike is much more than that of driving a car. You feel free when you drive a bike. Closer to nature. As some one has said "Only a dog knows why it keeps its face out of the car window". You could never get that feel when you are driving your car. And it also adds to style. Get a bullet and modify it. Add some paint work. And you have a bike like none other.

Yes, i love my bike and i would prefer to ride my bike rather than drive my car in delhi.

Document scoring in lucene - Part 2

This is an addition to the previous post document scoring/calculating relevance in lucene. If you find the link inadequate you can refer scoring@lucene.org and the formula at Default similarity formula.

What i did was i created some txt file and some code to index the file and have tried to find out in practice how lucene calculates relevance using the DefaultSimilarity class. Here is the file and the source code.

file: file_a.txt
jayant project manager project leader team leader java linux c c++ lucene apache solaris aix unix minix gnome kde ubuntu redhat fedora rpm deb media player vlc evolution exchange microsoft java vb vc vc++ php mysql java

source code: FIndexer.java
import java.io.*;
import java.util.Date;
import org.apache.lucene.index.*;
import org.apache.lucene.document.*;
import org.apache.lucene.analysis.*;

public class FIndexer
{

public static void main (String[] args) throws Exception
{
String src_path = args[0];
String des_path = args[1];

File f_src = new File(src_path);
File f_des = new File(des_path);

if(!f_src.isDirectory() || !f_des.isDirectory())
{
System.out.println("Error : "+f_src+" || "+f_des+" is not directory");
System.exit(1);
}

IndexWriter writer = new IndexWriter(f_des,new WhitespaceAnalyzer(), true);

File[] files = f_src.listFiles();
for(int x=0; x < files.length; x++)
{
Document doc = new Document();
if(files[x].isFile())
{
BufferedReader br = new BufferedReader(new FileReader(files[x]));
StringBuffer content = new StringBuffer();
String line = null;
while( (line = br.readLine()) != null)
{
content.append(line).append("\n");
}

Field f1 = new Field("name",files[x].getName(), Field.Store.YES, Field.Index.NO);
doc.add(f1);
Field f2 = new Field("content", content.toString(), Field.Store.NO, Field.Index.TOKENIZED);
doc.add(f2);
Field f3 = new Field("content2", content.toString(), Field.Store.NO, Field.Index.TOKENIZED);
doc.add(f3);

}
writer.addDocument(doc);
}
writer.optimize();
writer.close();
}
}


I created copies of the file_a.txt as file_b.txt and file_c.txt and edited file_c.txt. Such that file_a.txt and file_b.txt have the same content (that is word java occurs 3 times in both the files). And file_c.txt has word java occuring only 2 times.

I created an index using FIndexer.java and used luke to fire queries on the index.

Firstly did a search content:java. And as expected i got 3 results with the following score.

Rank	score	filename
0	0.1928	file_b.txt
1	0.1928	file_a.txt
2	0.1574	file_c.txt

Lets take the score for file_b.txt and see how it is calculated. The score is a product of
tf = 1.7321 (java occurs 3 times)
idf = 0.7123 (document freq = 3)

And the score of file_c.txt is a product of
tf = 1.4142 (java occurs 2 times)
idf = 0.7123 (document freq = 3)

Here score is equivalent to the fieldWeight since there is just one field. If more than one fields are used in the query, then the score would be a product of the queryWeight and fieldWeight

So if i change the query to Content:java^5 Content2:java^2. Here i am boosting java in content by 5 and java in content2 by 2. That is java in content is 2.5 times more important than java in content2. Lets check the scores.

Rank	score	filename
0	0.2506	file_b.txt
1	0.2506	file_a.txt
2	0.2046	file_c.txt

Lets look at how the score was calculated

Again for file_b.txt
0.2506 = 0.1709 (weight of content:java^5) * 0.0716 (weight of content2:java^2)

Out of which Weight of content:java^5 is
= 0.9285(Query weight) [ 5 (boost) * 0.7123 (idf docFreq=3) * 0.2607 (queryNorm) ]
* 0.1928(field weight) [ 1.7321 (tf=3) * 0.7123 (idf docFreq=3) * 0.1562 (fieldNorm) ]

And weight of content2:java^2 is
= 0.3714(Query weight) [ 2 (boost) * 0.7123 (idf docFreq=3) * 0.2607 (queryNorm) ]
* 0.1928(Field weight) [ 1.7321 (tf=3) * 0.7123 (idf docFreq=3) * 0.1562 (fieldNorm) ]

The same formula is used for calculating of score of file_c.txt except for the fact that termfrequency = 1.4142 (tf of content:java or content2:java is 2)

This explains a little about scoring in lucene. The scoring can be altered by either changing the DefaultSimilarity class or extending the DefaultSimilarity and changing certain factors/calculations in it. More on how to change the scoring formula later.

My Chances of being a multimillionaire

Your Chances of Being a Multimillionaire: 76%

You have a good chance of being a multimillionaire. Better than most people. You simply have a natural knack for money and the personality for success.

Will You Be a Multimillionaire?

change boot splash screen ubuntu

Easy steps to change the boot splash screen in ubuntu 7.04

1. firstly get the available splash screens. Do a

sudo apt-get install usplash*

It will display a list of available splash screens with ubuntu.

2. Next check out the available splash screens in /usr/lib/usplash

ls -lh /usr/lib/usplash

total 12M
-rw-r--r-- 1 root root 43K 2006-11-23 17:42 debian-edu-usplash.so
-rw-r--r-- 1 root root 2.3M 2007-03-30 17:33 edubuntu-splash.so
lrwxrwxrwx 1 root root 36 2007-05-17 23:20 usplash-artwork.so -> /etc/alternatives/usplash-artwork.so
-rw-r--r-- 1 root root 2.0M 2006-10-17 15:13 usplash-theme-ichthux.so
-rw-r--r-- 1 root root 2.3M 2007-04-07 15:36 usplash-theme-kubuntu.so
-rw-r--r-- 1 root root 2.6M 2007-04-10 18:28 usplash-theme-ubuntu.so
-rw-r--r-- 1 root root 2.0M 2007-03-19 16:17 usplash-theme-xubuntu.so


Here you can see that you have 6 splash screens and one soft link. Check out the softlink.

ls -lh /etc/alternatives/usplash-artwork.so

lrwxrwxrwx 1 root root 41 2007-06-29 08:38 /etc/alternatives/usplash-artwork.so -> /usr/lib/usplash/usplash-theme-xubuntu.so

It points back to one of the screens from the /usr/lib/usplash directory. So to change the screen simply change the soft link

sudo ln -sf /usr/lib/usplash-theme-kubuntu.so /etc/alternatives/usplash-artwork.so

And now check the new link

3. reconfigure the linux image

sudo dpkg-reconfigure linux-image-

Running depmod.
update-initramfs: Generating /boot/initrd.img-2.6.20-16-generic
.
.
Updating /boot/grub/menu.lst ... done

Thats done...

To check the new screen, no dont reboot simply type in

sudo usplash

And you can see the new screen - bingo. To get back to your xwindows environment press CTRL-ALT-F7

P.S.

here is the link for advanced users for creating their own splash screens
http://codeidol.com/unix/ubuntu/X11/Change-the-Ubuntu-Splash-Screen/
Anyone who develops his/her own splash screen can share it out with other ubuntians...

lucene in php & lucene in java

Something i found out while solving some issue from Mr. Nguyen from vietnam.

He used lucene-php in zend framework for building a lucene index and searching on the index, and was facing issues with search times. It turned out that mysql full text index was performing better than lucene index.

So i did a quick benchmark and found the following stuff

1. Indexing using php-lucene takes a huge amount of time as compared to java-lucene. I indexed 30000 records and the time it took was 1673 seconds. Optimization time was 210 seconds. Total time for index creation was 1883 seconds. Which is hell lot of time.

2. Index created using php-lucene is compatible to java-lucene. So index created by php-lucene can be read by java-lucene and vice versa.

3. Search in php-lucene is very slow as compared to java-lucene. The time for 100 searches are -

jayant@jayantbox:~/myprogs/java$ java searcher
Total : 30000 docs
t2-t1 : 231 milliseconds

jayant@jayantbox:~/myprogs/php$ php -q searcher.php
Total 30000 docs
total time : 15 seconds


So i thought that maybe php would be retrieving the documents upfront. And changed the code to extract all documents in php and java. Still the time for 100 searches were -

jayant@jayantbox:~/myprogs/java$ java searcher
Total : 30000 docs
t2-t1 : 2128 milliseconds

jayant@jayantbox:~/myprogs/php$ php -q searcher.php
Total 30000 docs
total time : 63 seconds


The code for php search for lucene index is:

/*
 *      searcher.php
 *      On 2007-06-06
 * By jayant 
 *
 */

include("Zend/Search/Lucene.php");

$index = new Zend_Search_Lucene("/tmp/myindex");
echo "Total ".$index->numDocs()." docs\n";
$query = "java";
$s = time();
for($i=0; $i<100; $i++)
{
 $hits = $index->find($query);
// retrieve all documents. Comment this code if you dont want to retrieve documents
 foreach($hits as $hit)
  $doc = $hit->getDocument();

}
$total = time()-$s;
echo "total time : $total s";
?>

And the code for java search of lucene index is

/*
 *      searcher.java
 *      On 2007-06-06
 * By jayant 
 *
 */

import org.apache.lucene.search.*;
import org.apache.lucene.queryParser.*;
import org.apache.lucene.analysis.*;
import org.apache.lucene.analysis.standard.*;
import org.apache.lucene.document.*;

public class searcher {

 public static void main (String args[]) throws Exception
 {
  IndexSearcher s = new IndexSearcher("/tmp/myindex");
  System.out.println("Total : "+s.maxDoc()+" docs");
  QueryParser q = new QueryParser("content",new StandardAnalyzer());
  Query qry = q.parse("java");

  long t1 = System.currentTimeMillis();
  for(int x=0; x< 100; x++)
  {
   Hits h = s.search(qry);
// retrieve all documents. Comment this code if you dont want to retrieve documents
   for(int y=0; y< h.length(); y++)
   {
    Document d = h.doc(y);
   }

  }
  long t2 = System.currentTimeMillis();
  System.out.println("t2-t1 : "+(t2-t1)+" ms");
 }
}

Hope i havent missed anything here.

installing zte mc315 on linux

Here are 4 steps to get the reliance aircard ZTE MC315 on linux. I got it running on ubuntu 7.04.

1. Insert the card and look at dmesg
output : 0.0: ttyS3 at I/O 0x2e8 (irq = 3) is a 16C950/954

This says that your card was detected properly.
You can also run "pccardctl info"
output =
PRODID_1="CDMA1X"
PRODID_2="CARD"
PRODID_3=""
PRODID_4=""
MANFID=0279,950b
FUNCID=2
PRODID_1=""
PRODID_2=""
PRODID_3=""
PRODID_4=""
MANFID=0000,0000
FUNCID=255


2. edit /etc/wvdial.conf

[Dialer Defaults]
Modem = /dev/ttyS3
Baud = 57600
SetVolume = 0
Dial-AT-OK ATDT Command =
Init1 = ATZ
FlowControl = Hardware (CRTSCTS)
Phone = #777
Username =
Password =
New PPPD = yes
Carrier Check = no
Stupid Mode = 1

3. Run set serial
(download and install setserial - if you dont have setserial)
"setserial /dev/ttyS3 baud_base 230400"

4. run wvdial
wvdial

So to get it running everytime you will need to create a shell script doing steps 3 & 4. Say mydial

---------------
setserial /dev/ttyS3 baud_base 230400
wvdial
---------------

PS
This method is also not perfect. I got one card working with this procedure, but could not get another card working using the same procedure.

Let me know if this helps you getting your ZTE MC 315 card running on linux.

PS 2
And to add to it, finally i have got my card running.
The trick is simple - you have to play with the UART and the baud_base.

Just run setserial on your machine and it will give the following output for UART and baud_base

* uart set UART type (none, 8250, 16450, 16550, 16550A,
16650, 16650V2, 16750, 16850, 16950, 16954)
* baud_base set base baud rate (CLOCK_FREQ / 16)


What i did was that i kept on changing my UART and kept the baud_base as 230400. I changed my UART to 16850 and then to 16750. And my card responded at 16750. And i got connected to the internet

So, My dialup script does this before doing wvdial

setserial /dev/ttyS3 uart 16750
setserial /dev/ttyS3 baud_base 230400

And this gets my card running. So to get your card running, i would suggest you try setting different UART and baud_base...

Anger Management

Having a bad day?

When you occasionally have a really bad day, and you just need to take It out
on someone, don't take it out on someone you know -- take it out on someone
you don't know. I was sitting at my desk when I remembered a phone call I had
forgotten to make. I found the number and dialed it.

A man answered, saying, "Hello."

I politely said, "Could I please speak with Robin Carter?"

Suddenly, the phone was slammed down on me. I couldn't believe that Anyone
could be so rude. I realized I had called the wrong number. I tracked down
Robin's correct number and called her. I had accidentally transposed the last
two digits of her phone number. After hanging up with her, I decided to call
the 'wrong' number again.

When the same guy answered the phone, I yelled, "You're an *******!" and hung
up.

I wrote his number down with the word '*******' next to it, and put it in my
desk drawer.

Every couple of weeks, when I was paying bills or had a really bad day, I'd
call him up and yell, "You're an *******!" It always cheered me up.


When Caller ID came to our area, I thought my therapeutic '*******' calling
would have to stop. So, I called his number and said, "Hi, this is John Smith
from the Telephone Company. I'm just calling to see if you're familiar with
the Caller ID program?"

He yelled, "NO!" and slammed the phone down.

I quickly called him back and said, "That's because you're an *******!"

One day I was at the store, getting ready to pull into a parking spot.
Some guy in a black BMW cut me off and pulled into the spot I had patiently
waited for. I hit the horn and yelled that I had been waiting for that spot.
The idiot ignored me. I noticed a "For Sale" sign in his car window . . so, I
wrote down his number.

A couple of days later, right after calling the first ******* ( I had his
number on speed dial), I thought I had better call the BMW *******, too.

I said, "Is this the man with the black BMW for sale?"

"Yes, it is."


"Can you tell me where I can see it?"


"Yes, I live at 1802 West 34th Street . It's a yellow house, and the car's
parked right out in front."

"What's your name?" I asked.

"My name is Don Hansen," he said.

"When's a good time to catch you, Don?"

"I'm home every evening after five."

"Listen, Don, can I tell you something?"

"Yes?"

"Don, you're an *******."

Then I hung up, and added his number to my speed dial, too. Now, when I had a
problem, I had two assholes to call.
But after several months of calling them, it wasn't as enjoyable as it used to
be. So, I came up with an idea. I called ******* #1.

"Hello."

"You're an *******!" (But I didn't hang up.)

"Are you still there?" he asked.

"Yeah," I said.

"Stop calling me," he screamed.

"Make me," I said.

"Who are you?" he asked.

"My name is Don Hansen."

"Yeah? Where do you live?"

"*******, I live at 1802 West 34th Street , a yellow house, with my black
Beamer parked in front."

He said, "I'm coming over right now, Don. And you had better start saying your
prayers."

I said, "Yeah, like I'm really scared, *******."

Then I called ******* #2.

"Hello?" he said.

"Hello, *******," I said.

He yelled, "If I ever find out who you are...!"

"You'll what?" I said.

"I'll kick your ass," he exclaimed.

I answered, "Well, *******, here's your chance. I'm coming over right now."

Then I hung up and immediately called the police, saying that I lived at 1802
West 34th Street, and that I was on my way over there to kill my gay lover.

Then I called Channel 13 News about the gang war going down on West
34th Street.

I quickly got into my car and headed over to 34th street .

When I got there, I saw two assholes beating the crap out of each other
in front of six squad cars, a police helicopter, and the channel 13
news crew.

NOW, I feel better - This is "Anger Management" at its very best.

mysql multi-master replication - Act II

SETUP PROCEDURE
Created 4 instances of mysql on 2 machines running on different ports. Lets call these instances A, B, C and D. So A & C are on one machine and B & D are on another machine. B is slave of A, C is slave of B, D is slave of C and A is slave of D.

(origin) A --> B --> C --> D --> A (origin)

Each instance has its own serverid. A query originating from any machine will travel through the loop replicating data on all the machines are return back to the origniating server - which in effect will identify that the query had originated from here and would not execute/replicate the query further.

To handle auto_increment field, two variables are defined in the configuraion of the server.

1. auto_increment_increment : controls the increment between successive AUTO_INCREMENT values.

2. auto_increment_offset : determines the starting point of AUTO_INCREMENT columns.

Using these two variables, the auto generated values of auto_increment columns can be controlled.

Once the replication loop is up and running, any data inserted in any node would automatically propagate to all the other nodes.

ISSUES WITH MULTI-MASTER REPLICATION & AUTOMATIC FAILOVER

1. LATENCY

There is a definite latency between the first node and the last node. The replication steps lead to the slave reading the master's binary log through the network and writing it to its own relay-bin log. It then executes the query and then writes it to its own binary log. So each node takes a small amount of time to execute and then propagate the query further.

For a 4 node multi-master replication when i replicated a single insert query containing one int and one varchar field, the time taken for data to reach the last node is 5 ms.
This latency would ofcourse depend on the following factors
a) amount of data to be relicated. Latency increases with increase in data
b) Network speed between the nodes. If the data is to replicated over internet, it will take more time as compared to that on nodes in LAN
c) Amount of indexes on the tables being replicated. As the number of indexes increase, the time taken to insert data in tables also increases. Increasig the latency.
d) The hardware of the machine and the load on the machine will also determine how fast the replication between master and slave will take place.

2. AUTOMATIC FAILOVER

Automatic failover can be accomplished using events and federated tables in mysql 5.1. Federated tables are created between master and slave and are used to check connection between master and slave. An event is used to trigger a query on the federated table which checks the connection between master and slave. If the query fails, then a stored procedure can be created which should chunk the master out of the replication loop.

So suppose in the loop shown above, A goes out, then the event on B would detect that A is out and would make D as a master of B.

This is the theoretical implementation of automatic failover. Practically there are a few issues with its implementation.

a) for failover, you need to know the position of master's master from where the slave should take over. (So B should know the position on D from where it has to start replication). One of the ways to do this is that each slave logs its master's position on a table which is replicated throughout the loop. But this again is not possible using events & stored procedures - cause there is no query which can capture the information available from "SHOW SLAVE STATUS" query in a variable and write it in a table. Another way to do this is to have an external script running at an interval of say 10 seconds which logs this information and checks the federated tables for any disruption in the master-slave connection. With this methodology, the problem is that there could be a 10 second window period during which data can be lost.

b) You could also land into an infinite replication situation. Lets look how using the 4 node example above. Suppose "A" goes down and It has a query in its binary log which has to be replicated throught the loop. So the query propagates from "B" to "C" and finally to "D". Now since "A" is down and failover has happened, "B" would be the slave of "D". So the query which originated from "A" would go from "D" to "B". Thats because if "A" would have been there, it would have identified its own query and stopped it. But since "A" is out of the loop, the query will not be stopped and it will propagate to "B" and will always be in the loop. This can result in either the query running in the loop indefinitely or an error on all the slaves and all the slaves going down.

These are the major issues with multi-master replication and automatic failover. Though one can still live with the automatic failover scenario - as it might occur once in a while. But the latency between first and last node during replication has no solution. This latency is due to physical constraints and cannot be avoided.

A work around would be distributing the queries based on tables on all the nodes. So that queries for a table would always be served from a single node. But this then would result in table locks on that node and again we would not be able to reap the benefits of multi-master replication.