MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:January 6 2000 3:20am
Subject:Re: Request for opinions regarding perfomance (LONG)
View as plain text  
Leonard Rosenbeck wrote:
> Hello everybody
> (if it's not too much of a problem, please cc: me in any reply as I
> subscribe to the digest version of the list and would like to get your
> responses as soon as possible)
> I am in the process planning a database driven site using PHP and MySQL.
> I have a rough idea of what I'm going to use for hardware for the MySQL
> server and would like any input or comments any of you have out there.
> The purchase of the production hardware is still a few months out, but I
> have to finalize my budgets for it...
> The plan is to build a really strong server with the intention of doing
> one way replication to slave servers when (optimism) load becomes an
> issue (Hopefully, Monty will have 1-way replication working soon).  I
> would like to put replication off as long as possible for the sake of
> simplicity.  All inserts would be done to the master server and then
> replicate to the slaves.  The application will be about 80%/20%
> SELECTS/(UPDATES+INSERTS) so this should work well. Most of the selects
> will be on 1 of 2 large tables (with occasional joins) with up to
> 200,000 records.
> Pentium III (or AMD Athlon if the upcoming motherboard offerings are
> good)  800MHZ or more --hopefully > 1GHZ :)
> 1024MG RAM
> 3x Adaptec 2940U2W SCSI Controller
>         1 SCSI-SCSI RAID CONTROLLER  (most likely Mylex)  128MB CACHE --Write
> Back, 72 Hour Battery Backup for Cache Memory
>         2x9GB 10,000 RPM U2W Drives (Configured as RAID 1) for OS files -- this
> will be the boot partition) connected to controller #1
>         5*18GB 10,000 RPM U2W Drives (RAID 5 for MySQL Database files)
> connected to controller #2
> 2x4GB 10,000RPM U2W Drives connected to controller #3 (each configured
> as swap)  (I don't think i'll really need 8GB of swap, but it's really
> nice to  split your swap across multiple drives
> A really high quality 100MB Ethernet Card (Intel something or other -- i
> don't have the model handy)

A good approach is to develop your code on very cheap hardware (possibly the one
you already have ), write a test suite that will benchmark it under heavy load,
run tests, take out bugs, run more tests, take out more bugs, then optimize, get
every bit of speed you can out of your system on cheap hardware, then decide
what you really need.

Today I was helping somebody who posted recently on the list with a performance
problem, and decided to modify some code I already had to do a rather crude
one-threaded select benchmark for his type of queries, so here is the benchmark
(for mod_auth_mysql):

 - create table mysql_auth (username char(25) not null primary key, pass
 groups char(25) );
 -  for lack of interesting data I loaded /usr/dict/words into username, setting
pass and groups to the same value -- this produced a 3.4 MB table with 0.8 MB
 - then I ran the following benchmark -- establish N connections from one
process, and then iterate through them M times executing SELECT passwd FROM
mysql_auth WHERE username = <random_word_in_the_dictionary>

On Pentium 166 with 32 MB EDO RAM I got 500 queries per second with 30
concurrent connections

On AMD 350 with 64 MB SDRAM, 80 connections, the result was 1500 queries per

It would be interesting to try it on a bigger table to see what happens, but I
would imagine the numbers will remain in the same order of magnitude as long as
there is enough RAM to fit the data and index into it. 

I will sometime get around to making the code available and writing the details

> --
> Does anybody have any performance stats on an Athlon???
> Is this enough RAM?
> I'm leaning strongly toward running FreeBSD (more on this below) as I am
> much more competent with it (I haven't touched a Linux kernel since
> 1.2.12).
> FreeBSD isn't going to use MySQL on multiple processors (damn those
> userland threads), but would it still be to my benefit to use an
> System??
> ----
> <I don't mean to begin a holywar, but I must ask for peoples opinions>
> >From what I have gathered from the mailing list archives about MySQL
> performance on FreeBSD & Linux  (correct me if I am wrong), FreeBSD has
> a bit better disk access, Linux is a bit faster (due to kernel threads),
> but FreeBSD degrades more gracefully under HEAVY load.  Linux also
> performs better in SMP as it has kernel threads (vs userland) so MySQL
> threads can run on more than 1 processor.  The bottom line after
> everybody has finished their pokes and jabs is: go with which ever you
> are more comfortable with.
> Does anybody have any reason why Linux would be a signifigantly better
> choice in this particular implementation?
> </holywar>
> I thank you all for your time and thought
> Leonard Rosenbeck
> lgr@stripped
> --
> ---------------------------------------------------------------------
> Please check "" before
> posting. To request this thread, e-mail mysql-thread23196@stripped
> To unsubscribe, send a message to:
>     <>

Sasha Pachev

| TcX  ____  __     _____   _____  ___     ==  mysql@stripped        |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sasha Pachev            |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|     mailto:sasha@stripped  |
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Provo, Utah, USA        |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                         |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                        |
|             /*/             \*\ Developers Team                  |
Request for opinions regarding perfomance (LONG)Leonard Rosenbeck6 Jan
  • RE: Request for opinions regarding perfomance (LONG)Cheng-Wei Cheng6 Jan
    • RE: Request for opinions regarding perfomance (LONG)sinisa6 Jan
  • Sequential UpdatesCheng-Wei Cheng6 Jan
    • Re: Sequential UpdatesSasha Pachev6 Jan
  • Re: Request for opinions regarding perfomance (LONG)Sasha Pachev6 Jan
    • Re: Request for opinions regarding perfomance (SHORT)Mark Zieg6 Jan
  • Re: Request for opinions regarding perfomance (LONG)Benjamin Pflugmann7 Jan
    • How do I..Cheng-Wei Cheng7 Jan
      • Re: How do I..sinisa7 Jan
      • Re: How do I..Paul DuBois7 Jan
Re: Sequential UpdatesSasha Pachev6 Jan