Baron Schwartz wrote:
> On Jan 2, 2008 10:04 AM, Luis Motta Campos wrote:
>> Baron Schwartz wrote:
>>> What are the biggest changes you anticipate? I'd say they will be
>>> the version upgrade, converting to InnoDB, and using
>>> replication. It looks like you have planned well for all but
>>> using replication.
>> That's interesting. What kind of activities (besides configuration,
>> maybe?) are interesting to add?
>
> Lots of experimentation!
>
> Let me ask it another way: how are you planning to use replication?
> -- for load balancing/scaling reads, backups, a hot standby machine?
OK, I guess a bit more of information can help. I have a very sensitive
database at the company, almost unprotected at the moment. It's big
hardware, lots of hot-replaceable parts, and a quite big energy
generator attached, but nothing guarantees our data between the last
backup and the present moment case the building burns down to the ground.
The database I/O activity obey the 80-20 rule: 80% reads and 20% writes.
I have an average of 80,000 read-queries per second (that's average - I
got 'nice' peaks sometimes, with 4 times more activity). We have 20,000
write-queries per second in the same database.
As we're addressing the risk of major disasters (like the building
burning down to ashes), the plan is spreading this database through the
two data centers we hire: one data center would hold the master
database, and a spare reading slave, and the other data center would
hold "the" reading slave and a spare master database.
Don't worry about connectivity issues: I have plenty of bandwidth
between those two sites, and I can ask for more. I just need to know in
advance how much to ask for, and I will surely get it.
The master/spare and slave/spare machines would be connected through
heartbeat and will keep the database in a DRDB filesystem. This will
guarantee that, in case of failure, the other machine can raise the same
IP address in it's own interface and continue operations after a short
delay.
Now, about the existing database: it runs Debian Stable, and a
pre-compiled mysql server. As I said before, it's a big machine, but
it's getting old (more than 2 years already), and must be replaced.
The system running on it is quite old, more than 10 years old. This
means that the design and implementation aren't nice. And the
maintenance added new improvements where needed (as InnoDB tables).
The basic idea is to completely re-design and re-implement the database
as soon as the new server is in place (being stability and failure
resistance the priorities fixed by management for the first stage).
>> Isn't predicting that the replication is one of the three biggest
>> changes I have in my database a bit of an exaggeration?
>
> Definitely not. It is a huge change. From one server to a
> two-server replication setup is a quantum leap. You'll have all
> kinds of new things to think about, such as data consistency, dealing
> with replication lag, performance changes on the master due to
> binary logging, etc.
>
> It depends a lot on WHY you're using replication (see my earlier
> question).
Well, I guess I don't need to worry about multi-master replication for
now, the application must be re-designed and re-implemented before we
can start thinking about this. It's currently quite hard to maintain,
and we don't know a lot of important things about it. There is a team
working on this already.
About data consistency, I would like to have some pointers: is this an
issue, provided that I stick to single-master architectures?
About binary logging, I must first read more about this. Thanks for the
warning, though.
Replication lag is another thing that worries me a lot - is there a
mathematical model I can use in order to forecast the expected average
replication lag?
The application can handle quite big replication lags at the moment.
It's not web-based, and there is no users directly interacting with it
at the present moment, what makes everything much simpler - you can have
a program waiting for 5 minutes to "see" a change without a lot of fuss
about it.
Sorry, I guess this email is a bit bigger than I was expecting... :(
I hope this gives you a general idea about my current problem.
Please feel free to ask more about it if you feel that this details
aren't enough.
Kind regards.
--
Luis Motta Campos (a.k.a. Monsieur Champs) is a software engineer,
Perl fanatic evangelist, and amateur {cook, photographer}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
