From: Dan Trainor
Date: May 25 2006 12:00am
Subject: Re: mysql performance / ndb 5.1 performance
List-Archive: http://lists.mysql.com/mysql/198282
Message-Id: <4474F39D.3060306@v2web.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Moritz Möller wrote:
> Hi Dan,
> 
> there are about 2GB free, so the net size would still be 32 GB.
> 
> The queries are really optimized, >99.9% of all queries can be satisfied
> without table scans.
> 
> Well, I guess I have to give NDB a chance, I hope it will help. The only
> alternative I come to is to cluster the database on application level (use
> server userID%numServers), which would be a [insert favourite non-swear-word
> here] lot of work ;)
> 
> Moritz
> 
> 
> -----Original Message-----
> From: Dan Trainor [mailto:dan@stripped] 
> Sent: Thursday, May 25, 2006 1:41 AM
> To: Moritz Möller; mysql@stripped
> Subject: Re: mysql performance
> 
> Moritz Möller wrote:
> 
>>Hi list,
>>
>>we're running some large high-traffic mysql servers, and are currently
>>reaching the limit of our machines.
>>
>>We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware
> 
> is
> 
>>quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is
>>CPU.
>>The queries run very fast (I seldom see a process that's running longer
> 
> than
> 
>>a second), but there are too many of them, I guess.
>>
>>As far as I know, NDB keeps the whole database in memory, so with indices
>>and some mem as reserve, we'd need ~48GB (3x16 or something) in total for
>>NDB :(
>>
>>Does someone know other solutions to this? Is NDB the only storage engine
>>supporting clustering?
>>
>>Thanks in advantage,
>>
>>Moritz
>>
>>
>>
> 
> 
> Hi -
> 
> That's quite a large database.  I, too, have been dealing with what I 
> thought was a large database for this new project.  Being 2G, it hardly 
> compares to your database size.
> 
> Keep in mind, however, that a 36G ibdata file does not necessarily mean 
> that you are using 36G to store data.  InnoDB documents from the MySQL 
> site explain ways to compact these files, possibly shrinking the size of 
> ibdata files.  Another way to get a better idea of how much data you're 
> actually using is to use the 'SHOW TABLE STATUS' query from within 
> MySQL.  Take the "InnoDB Free:" item under the 'Comment:' column, and 
> subtract this from the total size of the ibdata file(s).  This will give 
> you a more accurate representation of how much of that ibdata file 
> you're actually using.  I think.  (Someone mind correcting me if I'm way 
> off here?)
> 
> NDB may not be your solution.  Even though disk-based storage is 
> included with NDB in 5.1 and beyond, I'm not too sure how this will 
> affect the speed of your operations.  I suppose it's worth a try, however.
> 
> Please take this advise with a grain of salt, as InnoDB is still quite 
> new to me, as well.  Other things I've found to speed up large databases 
> are to properly make indexes, and testing them with the EXPLAIN 
> function.  This alone has let me to speed up our operations as much as 
> 30% in most cases.
> 
> Thanks
> -dant
> 
> 

Hi -

Well, go ahead and do that and let us know how it turns out.  There's a 
whole mailing list on cluster.

Like I said, 5.1 (don't remember specifically which version) has 
file-based storage for cluster as an option.  Good luck with that.

Thanks!
-dant