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
