As Mike pointed out in a comment to my earlier post "seconds behind master only tells you the difference between when a statement was put into the relay log on the slave and when it was executed, it doesn't account for the time it takes for the statement to get from the master to the slave."
On a slave server, MySQL compares the TIMESTAMP value taken from the slave and subtracts the TIMESTAMP value present in the binary log and then adds the difference between the UNIX_TIMESTAMP value taken from the master and the slave. (See Seconds Behind Master weirdness if you are getting a Seconds_Behind_Master value of 18446744073709551615).
If we want to decrease the value of the Seconds_Behind_Master, we need to find the queries that take extraordinarily long to execute on both the master and the slave. To do this we can use the slow query log (also see slow query log manual page for the latest version).
We can enable the logging of slow queries by starting the mysqld with the
--log-slow-queriesoption. If we want we can also specify the filename where the slow queries log should be kept.
By default, MySQL will create the slow query log as hostname-slow.log in the data directory.
Using another option long_query_time, we can specify the amount of time that is considered acceptable. For instance, if we specify
long_query_time = 5in the my.cnf file then all queries that take longer than 5 seconds will be considered slow queries and therefore logged.
I believe, the default value for long_query_time is 10 seconds.
Once you have the slow queries log, you can read it with mysqldumpslow.
By investigating and optimizing the "slow queries" we can help our slaves not get too behind the master.
More to come. Comments, questions and suggestions are welcome.