[ale] Building a Linux/Mysql Database server.

[ale at cogginsnet.com (Robert Coggins)]

On 5/24/2011 4:27 PM, Atlanta Geek wrote:
> I've got it isolated to a few tables.
> The queries are pretty tight, the biggest issues is that one of the
> tables contains 3 blobs that the standard row is 2k in size.
> 5,760,000 of these records.
>
> And normally the queries that I am running are on 100-1000 records 'group by'.
>
> We write about 8000 records an hour to this table.
>
> Thanks for your feedback.
>
>
>> Message: 3
>> Date: Tue, 24 May 2011 01:32:29 -0400
>> From: The Don Lachlan<ale-at-ale.org at unpopularminds.org>
>> Subject: Re: [ale] Building a Linux/Mysql Database server.
>> To: ale at ale.org
>> Message-ID:<20110524053229.GA6025 at starscape.homelinux.net>
>> Content-Type: text/plain; charset=us-ascii
>>
>> On Tue, May 24, 2011 at 12:26:08AM -0400, Atlanta Geek wrote:
>>> I wasnt very clear.  I actually need to define the specs for a mysql
>>> database server.
>>> Our current server is a Quad Core ~2GHz 4 Gig 2 15k drive (no raid)
>>> configuration. And It seems to be I/O bound on a lot of operations.
>>> The database  as is goes to around 45 Gig.
>>> This new server needs to be able to handle our same database structure
>>> with about 600 Gig of data.  There's a lot of writing and reading of
>>> data.
>>> Any suggestions on hardware.  Can Mysql deal with 32 Gig or is this a
>>> waste of resources because its not enough to cache.  Would we be
>>> better off looking at SSDs .
>>> Any guidance would be appreciated.
>> If you're referring to RAM, MySQL can handle 32GB of RAM just fine.
>>
>> Disk I/O is a common bottleneck, especially in databases. SSD is likely
>> overkill; SCSI or SAS (Serial-Attached-SCSI) in RAID10 should be fine but I
>> would avoid standard SATA.
>>
>> I'm guessing (hoping!) that expanding your DB from 45G to 600G will also
>> include some tuning of the database and application. As such, performance
>> will change and your current bottleneck(s) may be resolved.
>>
>> Without more information about your applications and what the current
>> performance is or what the _expected_ performance will be, it's impossible
>> to be specific. Is your application focused on reads from the DB? Writes?
>> 50/50%? How much data? How quickly? Are constantly you re-reading the same
>> data? How many CPU cycles for a standard query? Can't answer your questions
>> without more information. Give us some numbers, man!
>>
>> -L (But what I said about RAID10 is gospel.)
>>
>>
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://mail.ale.org/mailman/listinfo/ale
> See JOBS, ANNOUNCE and SCHOOLS lists at
> http://mail.ale.org/mailman/listinfo
What are you storing in your blobs?  It might make sense to offload them 
to the file system and reference them depending on what they are.

Robert