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:[EMAIL PROTECTED]
Sent: Thursday, May 25, 2006 1:41 AM
To: Moritz Möller; mysql@lists.mysql.com
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
