Thanks a bunch for the insight and for proving the links to the
following benchmarking tools. Unfortunately, business is requiring
that each database live in it's own instance, so it sounds like
moving in the direction of having multiple servers and spreading the
data around would be the best idea.
At 06:39 PM 3/15/2007, Alex Greg wrote:
>On 3/14/07, Clyde Lewis <cllewis@stripped> wrote:
>>System Configuration: Sun Microsystems sun4u Sun Fire E2900
>>System clock frequency: 150 MHZ
>>Memory size: 65536 Megabytes
>>CPU: 12 @ 1200 MHz
>>I'm looking for a tool that will allow us to determine the max number
>>of databases that can run in a single instance of MySQL on a pretty
>>beefy server( Spec above).
>>In total we will have about ~40 MySQL
>>instances running on this server. Each instance of MySQL, there will
>>have between 30-60 individual databases supporting an OLTP
>>application. I know that there are no know internal limits that MySQL
>>have regarding the number of databases that can be created, but I
>>would like get my hands on a tool that can simulate the number of
>>databases and identify where we would potentially run into
>As I mentioned above, your performance issues are going to come not
>from the number of databases, but from (primarily) how well-designed
>your database tables and queries are, and (secondly) how you configure
>the mysql server(s).
>One important factor to bear in mind is that with 40 separate MySQL
>instances on the single 64GB server, you will have a maximum 1.6GB of
>RAM per instance (excluding memory used by the O/S and other
>applications). This will have to be divided up between the various
>memory buffers (key_buffer, innodb_buffer_pool, etc.) allocated by
>each mysql process, so you might want to reconsider if you really need
>to run 40 separate mysql processes, or whether all the databases can
>live in the same MySQL instance and thus probably make better use of
>the available RAM.
>With regards to stress-testing and benchmarking, two popular tools for
>benchmarking MySQL servers are:
>Super Smack: http://vegan.net/tony/supersmack/
>>We need to determine whether to have multiple
>>servers to support the ~40 instances or have all ~40 instances on the
>>same machine. Any help of ideas would be greatly appreciated with
>I would be inclined to have separate machines, rather than put
>everything on one huge server. By spreading the data around, you are
>reducing the risk if the one mega-machine were to become unavailable,
>and also reducing resource contention (on the disks, CPU, RAM etc.).
General Parts, Inc.