List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:May 30 1999 3:00pm
Subject:Re: Performance
View as plain text  
Michael Farr wrote:
> 
> I need to know how this database would perform, before I create it.
> 
> I will have 20 000 people in the database, each with an average of 8 images
> @ 125k each.
> eg a table with 160 000 images @ 125 k each pointing to another table with
> 100 000 records
> total approx = 20 Gb

If you set it up like this you will run into the 2 GB limit for the
table size. A better way to do this is store the images on the
filesystem in a well-designed subdirectory structure.

> 
> A user will NEVER select more than 25 images at a time.
> The Images table:
> Images are uniquely indexed by an integer, they have a foriegn key which
> points to the person table
> 
> The server is running on Redhat Linux 5.2 (I'll upgrade it to 6.X once I
> hear people say its totally stable), I am imagining that MySQL needs a bit
> of memory to cache certain things, and I cant seem to find out how much it
> will need for this application.  The server is a Celeron 450 :-) with 50 Gb
> of RAID 1 hard drive.  What would be a sensible amount of memory to put in
> the server?

The more the better. See if you can get 1 GB. If I am not mistaken,
Linux cannot currently use more than 960MB, somebody correct me if I am
wrong.

> 
> There is also potential for a larger number of people being requested (they
> would probably ask for something stupid like 1 000 000 clients).  I would
> like to know how to calculate this sort of thing myself (if there is a rule
> to use that is).
> 
> I would also want to know how long each select would take on average, where
> all selects look like this
> select * from Images where personId = :personId;

To keep users from selecting 1,000,000 records use limit  in your
queries: select * from Images where personId = $personId limit 25;

Selects will be very fast if you index your tables right.

> 
> Any help is appreciated, thanks for reading.  Also if there are any issues
> I should know when using a 20 Gb database, please let me know (I dont know
> much ... yet)

As I already mentioned, you have to deal with the 2 GB limit for a
table. Even after moving the images to the filesystem, you may still
have enough data in the tables to fill up 2 GB. If that happens, split
the table into several according to some easily computable criteria.

Another thing, good table design can save you a lot of disk space and
will speed up your queries. Normalize your tables, replace chars with
ints whenever possible, index your tables well.


> 
> note: I have read 10.2 How MySQL uses memory, I couldn't get this
> information out directly.
> 
> Mike
> 
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread4170@stripped
> 
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.

-- 
Sasha Pachev
http://www.sashanet.com
Thread
PerformanceMichael Farr30 May
  • Re: PerformanceSasha Pachev30 May
    • Re: PerformanceTracy R Reed1 Jun
      • Re: PerformanceVivek Khera1 Jun
        • Re: PerformanceDavid Christian1 Jun
  • Re: PerformancePaul DuBois30 May
  • Re: PerformanceSasha Pachev1 Jun
Re: PerformanceSteve Fambro30 May
Re: PerformanceL. Stapinsky1 Jun