>Hi. I realise this is getting off-topic, so I suppose replies should go
>direct to me unless they'll interest the list.
I think it's of a general interest to the list really. :)
>I work on a site that makes use of mod_perl, Apache and MySQL. We are
>currently toying around with our server set-up, trying to spread the
>load across multiple machines. For web-serving, this is fairly simple,
>but we're concerned about our MySQL server. Currently, different apps
>sit on different boxes, each with its own MySQL. However, for ease of
>upgrading, we're thinking of moving all MySQL databases to dedicated
>machine(s).
Note that once you do find a stable version of MySQL, unless you need
new features there's hardly much of a reason to upgrade. I've found
3.22.25 and up to be pretty stable, while older versions would go down
every now and then (on a Solaris 2.5 Ultra 2).
Not that crashing has been much of a problem - the safe_mysqld script
checks the databases [with only once a problem due to lack of diskspace
in creating the temporary tables] and restarts the server. I know the
server crashed once every 1-2 days, but I've never heard any complaint
over it having done that. With the newer versions it stays up a lot
longer, if it even crashes at all.
> > MySQL is quick, it's by far the fastest you can get at most
> operations. On the
> > other hand, you can't easily spread the load over multiple servers
>
>This is our current concern. Is the single machine a good way to go? If
>one app takes down MySQL (which unfortunately does happen once in a
>while) then all apps lose their database. But if the machine gets
>bogged down, we can throw more ram/disk space at it. Is it possible to
>run MySQL across multiple servers? Should we be looking at a solution
>with multiple database servers instead of one machine? At the
>hardware level, this would be more reliable, but at the script level,
>we'd have to keep track of multiple machines, and being a lazy perl
>monkey, I want all my scripts to talk to the database in an identical
>manner.
I'd best direct you to section 19.1 of the MySQL manual for this. But
basically it boils down to you having to do all the hard work - it isn't
too hard to maintain a mirror server of your database (through the update
log) but once you start updating your slave server as well you will get
out of sync with your data on the master. So if you only need to read
from your database it might be worth the performance to have a slave
server to only read from.
Another alternative are of course different databases, if they are not
or only very slightly related then run multiple servers with these
different databases. (Perhaps one might contain all your persistent
session data, and another your customer data - people with a persistent
session are not necessarily your customer too).
What's probably also an important factor is whether you have one database
and a set of scripts accessing it, or lots of databases and more scripts
accessing them. The latter will give you problems with persistent
database connections and the database server will have to work a lot
harder to cache all the different sets of data.
And do try to keep your queries down. If you can cache data then by
all means go for it. I have a CGI server with a MySQL database on it
and dozens of CGI's accessing different databases on that server. It
has to do an average of some 5500 SQL queries per 5 minutes, peaking at
12200 queries per 5 minutes at peak load. It consumes 60% of the CGI's
server CPU capacity, and it takes a lot of simultaneous connects with
this peak load. As it takes so much performance it will also bring
down the number (and performance) of the mod_perl CGI's it can serve
in a snowball effect. I'm sure once I'm done converting the heavily
used scripts to HTML::Mason and implementing a lot of caching there
the load on the MySQL server will be a lot less and leave room for the
useful stuff. :)
As someone else already mentioned, the MySQL people are working hard on
making a distributed database possible. I'm curious to see what will
come of that, although I don't think I'll use the feature myself.
Grtz,
Pascal
--
Pascal Eeftinck - arcade^xs4all.nl - Perl is not a language, it's a way of life
