I found High Performance MySQL http://highperformancemysql.com/ very
useful. Also, throwing some money at the people at MySQL should get
you a highly informed answer.
One of the first tests I run for a customer is to see if the
structure of their data and indices match the needs of the queries
being submitted.
Can you use a divide and conquer strategy on the problem? e.g., are
all of the queries the problem or are just a subset causing the
problem? Is there any information you can get to focus in on the problem?
Have you run samples of problem queries against the database using
EXPLAIN? Are responses taking multiple queries? Can the number of
queries by reduced, say, by co-locating data items instead of using a
join or multiple queries to pull together everything needed for a response?
The architecture in figure 1 might work fine if you can structure the
data so that the various servers answer different questions. That way
you might be able to partition the databases to eliminate some
queries or indices. Perhaps the "Load Balancer" can be made smarter
and feed each query to the server that's best able to respond. That
way you might be able to make multiple servers smarter instead of
just more iron.
Since you update in batch mode there might be some optimizing that
can be done to prepare responses to common queries, analyze the
nature of the queries to respond to changes in the data being requested, etc.
Every once in a while getting facts about the problem leads to a
better solution.
Hope this helps.
Eldon Ziegler
Atlantic Database Systems, Inc.
www.atlanticdb.com
At 10:20 am 4/8/2006, Elias wrote:
We are currently building a webfarm to replace our all in one box
solution. We are experiencing problems when we get linked to a site
like the drudgereport and we are seeing 1000's of hits a minute.
Both apache and mysql start chocking and we get dropped because we
cant deliver content. I am trying to understand what is the best way
to redeploy our Mysql.
I am thinking of two possible designs, if feasible. The clients are
read only there is no inserting or deleting. The database is updated
nightly from a single source.
Fig 1
_____________
| http |
| Mysql |
|_____________|
_____________
| http |
_______ _____________ | Mysql | _____________
| PIX | | Load Balance
| |_____________| | |
| |
---| |------ _____________ -------| SAN |
|_______| |____________
| | http | |____________ |
| Mysql |
|_____________|
_____________
| http |
| Mysql |
|_____________|
In Fig 1 I am thinking that each server runs both apache and the
mysql engine and the database files will live on the SAN. Is this
possible? can multiple engines talk to one database file on the SAN?
Fig 2
_____________
| http |
| |
|_____________|
_____________
| http |
_______ _____________ | |
_____________ ______________
| PIX | | Load Balance
| |_____________| | | |
|
| |
---| |------ _____________ -------| Mysql
|---- | SAN |
|_______| |____________
| | http | |____________ | |_____________ |
| |
|_____________|
In fig 2 the 3 apache servers would query the single myql server.
All the data and scripts would live on the SAN. Or would it be
better and faster to have the data live on the mysql server and the
scripts live on the SAN?
We are going to deploy HP proliant ml 585 with quad AMD 2.2 Opterons
with 12 gigs of ram. With that in mind how many mysql sessions can I
be able to support?
Any suggestions, comments, thoughts of clarity would be greatly appreciated.
Regards to all,
Michael
