Kenneth,
there was discussion about Parallel MySQL on the internals mailing
list 3 months ago. I have pasted at the end of this message what
I then wrote there about a parallel InnoDB.
I think Jeremy Zawodny is right in suggesting that you create several
application server processes on several computers, and put the database
on a single big SMP box from Sun or HP, for example. In the SAP R/3
OLTP bechmark the application servers use more CPU than the database
server. I think you can find the description of the benchmark
systems from the SAP website.
The perennial question in distributed databases is whether you can
really get more speed from them than from a single SMP box. For a
read-only database the answer is definitely yes, and you can, for example,
use the MySQL replication. For an update-intensive database the problem
is that communication between the nodes uses CPU and introduces latency.
Recent TPC-C records have been run on shared-nothing Windows 2000
clusters, but they take advantage of the TPC-C specification which
has been designed so that it is possible to divide the TPC-C load between
several nodes, and still get only a moderate inter-node communication
load.
Actually, the bottleneck in SMP computers is exactly the same as
in a distributed system: in an update-intensive database application,
processors in the computer have to send data from a processor cache
to another processor cache when they read and update memory locations.
If there are a lot of processors in the computer, this traffic will
saturate the memory bus. This is one of the reasons why the biggest SMP
computers have only 64 processors. And adding bigger processor
caches (Alphas could have up to 16 MB, I think) only helps in a
read-intensive application.
Regards,
Heikki Tuuri
Innobase Oy
>On Fri, Jun 01, 2001 at 10:00:01AM -0700, Kenneth Kopelson wrote:
>>
>> I am hoping there are people who use MySQL for serious business
>> applications on this list.
>
>We're occasionally serious here. Other times, it just depends on the
>sort of mood that people seem to be in. :-)
>
>(I just think it's dangerous to equate "expert" and "serious business"
>given what I've seen at some companies...)
>
>> HERE IS MY QUESTION:
>>
>> I have need for multiple servers to share the same database files.
>
>Instead of replication?
>
>Folks have done that before. Often times they'll put the data files on
>a Netapp and point a cluster of MySQL servers at it. It's VERY
>important, of course, to have really fast (switched) network
>connections between the MySQL servers and the Netapp.
>
>> We are implementing a site that will have hundreds of thousands of
>> users at the same time, so we need a large pool of Linux servers
>> that are load-balanced.
>
>That is a lot of users. How many simultaneous MySQL sessions to you
>think that will equate to? Will you have some sort of connection
>pooling at the application layer?
>
>> Then, all these servers, each of which will run Apache and MySQL (or
>> another database if MySQL just can't handle the load), needs to
>> access the single large RAID disk array through a giga-bit network
>> connection.
>
>Why not separate the Apache servers from the MySQL servers? It would
>seem to be easier to scale that way.
>
>> Certainly, this is not uncommon, as any large database driven
>> website needs to have a similar setup (like eBay, Microsoft, Apple,
>> Amazon, etc.).
>
>You'd be surprised. Some rather high-traffic sites have been able to
>get away with a single monster database server that had lots of
>clients talking to it. I don't know that should say who this
>particular example is, but they managed to handle a lot of traffic
>using this model with [gasp!] Oracle.
>
>> The servers need to be setup in a cluster to provide the required
>> high-availability. Also, replication will NOT work, as we need
>> changes to the database to be immediately available to everyone
>> else, and managing 100 or more replicated databases would be a
>> nightmare.
>
>You're going to really need 100 servers?
>
>> So, how do we get support in MySQL so that multiple instances of the
>> mysqld daemon running on separate machines can all access the same
>> database files located on a central server? I can see how the file
>> system of the central database server could be exported through NFS
>> (or something similar) so that all the machines in the cluster can
>> access the database files. I also imagine the daemons would need to
>> have some sort of locking mechanism to avoid stepping on each other.
>
>You'll find a very brief mention of what you need to know here:
>
> http://www.mysql.com/doc/S/y/System.html
>
>What this documentation doesn't say, however, is that this model
>really only works with MyISAM tables. I'm pretty sure it doesn't do
>you any good with InnoDB, BDB, or Gemini tables. And those are the
>ones whith more granular locking and better performance in heavy
>update situations.
>
>If you can tolerate a small amount of latency in propogating updates,
>you could have a single (very beefy) master server which only gets the
>update queries and many slaves which you can load balance the read
>queries across. The master probably ought to be a many-CPU Solaris or
>Linux box, given the sort of loads you're talking about.
>
>If you can keep the updates on a single server, you'd be able to use
>InnoDB or Gemini tables, which should give you good update
>performance.
>
>Just some ideas...
>
>[Grr. ispell just decided not to work right. Sorry for any horrible
>spelling in this note...]
>
>Jeremy
>--
>Jeremy D. Zawodny, <[EMAIL PROTECTED]>
>Technical Yahoo - Yahoo Finance
>Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 439-9951
>
>MySQL 3.23.29: up 8 days, processed 50,950,321 queries (67/sec. avg)
>
>---------------------------------------------------------------------------
-----
...................................
My post to the internals list:
...................................
>Hi,
>and sorry this reply comes a few days late. I have been busy putting
>together the MySQL/Innobase release.
>
>>Denis> Bad news (for me), BDB use mmap. That's really bad in our cluster
context.
>>Denis> I could try to "msync" after each query/trx but I don't know if it's
>>Denis> possible and safe. Somebody knows ?>
>>Denis> I'm waiting hard for innobase... Hoping there is no fatal mmap, and
that I
>>Denis> could "parallelize" it!
>>
>>Heikki, any comments ?
>
>Denis, is your idea that several nodes in computer cluster would have
>an MySQL/Innobase server running and users could then do transactions,
>inserts, updates, selects to any of these nodes?
>All nodes would see the same database files on disk?
>
>If this is your plan, it is equivalent to Oracle parallel server.
>The problem is keeping synchronized copies of global information in each
>of the nodes. In practice the copies cannot be totally synchronized, since
>then the messages needed between the nodes would make the systemvery slow.
>Innobase caches data and indexes in a 'buffer pool' in main memory.
>
>In a parallel server a principal problem is to decide when to ship
>modified database pages (the default is 16 kB) between the nodes.
>Other problems include keeping (partially) synchronized copies of
>lock state information and latch (semaphore) state information,
>especially latches set on database pages in buffer pool.
>
>I have read a couple of academic papers on Oracle parallel server,
>DB2 cluster version, and DEC distributed file system. The way
>to decrease the amount of messages needed between different nodes
>is to use hierarchical locking: the hierarchy could be: whole database
>-> a table -> a database page -> a row. Each node in the computer
>cluster reserves (in some way 'locks') a subtree of the
>hierarchy for its own use. Other nodes which need to access
>information reserved by another node have to request permission
>from the other node.
>
>One can dynamically tune the reservations of each node. For example,
>if the access profile at one moment is lots of updates into the tables,
>then it can be sensible that the individual nodes reserve single database
>pages at a time. If the access profile changes to pure queries on data,
>then each node can acquire a single shared lock on the whole database
>(several nodes can have a shared access to data at the same time).
>
>A parallel read-only database is the trivial case which is easy to
>implement.
>
>Making a parallel Innobase server is possible, but it certainly requires
>several man-years of work, since one has to modify code in the lock table,
>buffer pool etc. A possible solution would be to use the table-level
>locking in MySQL to build a simpler parallel database. The problem is
>however how to synchronize the database pages in the buffer pools
>of several nodes? Should we ship modified pages immediately between the
>nodes, which would make inserts and updates very slow? Another problem
>is how to write the database log. Maybe there should be one coordinator
>node which would take care of the whole log?
>
>Best regards,
>Heikki Tuuri
>Innobase Oy
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
