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
> 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.
> 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.