More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
MySQL - What should I do to prepare my client code to use performance-enhancing replication?
If the part of your code that is responsible for database access has been properly abstracted/modularized, converting it
to run with the replicated setup should be very smooth and easy - just change the implementation of your database access to
read from some slave or the master, and to awlays write to the master. If your code does not have this level of abstraction,
setting up a replicated system will give you an opportunity/motivation to it clean up. You should start by creating a
wrapper library /module with the following functions:
safe_writer_connect()
safe_reader_connect()
safe_reader_query()
safe_writer_query()
safe_ means that the function will take care of handling all the error conditions.
You should then convert your client code to use the wrapper library. It may be a painful and scary process at first, but it
will pay off in the long run. All applications that follow the above pattern will be able to take advantage of
one-master/many slaves solution. The code will be a lot easier to maintain, and adding troubleshooting options will be
trivial. You will just need to modify one or two functions, for example, to log how long each query took, or which query,
among your many thousands, gave you an error. If you have written a lot of code already, you may want to automate the
conversion task by using Monty's replace utility, which comes with the standard distribution of MySQL, or just write your
own Perl script. Hopefully, your code follows some recognizable pattern. If not, then you are probably better off re-writing
it anyway, or at least going through and manually beating it into a pattern.
Note that, of course, you can use different names for the functions. What is important is having unified interface for
connecting for reads, connecting for writes, doing a read, and doing a write.
(Continued on next question...)