Christos Andronis wrote:
Hi all,
we are trying to run the following query on a table that contains over 600 million rows:
'ALTER TABLE `typed_strengths` CHANGE `entity1_id` `entity1_id` int(10)
UNSIGNED DEFAULT NULL FIRST'
The query takes ages to run (has been running for over 10 hours now). Is this
normal?
Yes, this is normal, but not desirable of course!
The fundamental issue is that your table is likely too large, and
rebuilding indexes cannot fit into memory, and goes to disk.
You need to break the table up into smaller shards or partitions using
horizontal table partitioning methodologies.
Usually what one will do is have say 10M or 100M rows in a table say
for one months data or some such. If this is MyISAM, then all of those
sub tables can be put into a MERGE, and queried normally:
http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html
If this is InnoDB, then you have to teach your application how to
query the partitions individually and aggregate the results on its own.
We will have a better partitioning implementation in MySQL 5.1, being
developed still, but you can get early information on this in the
Partitioning Forum here:
http://forums.mysql.com/list.php?106
One nice side effect in data warehousing type applications is that
when you go to delete the data, you can just drop the old table,
with having to do large deletes and rebuilding the table to defragment.
As a side issue, is MySQL suited for such big tables? I've seen a couple of
case studies with MySQL databases over 1.4 billion rows but it is not clear to
me whether this size corresponds to the whole database or whether it is for a
single table.
Yes, its fine. You just need to partition your huge tables, this is true
in all database platforms.
The MySQL distribution we're using is 4.1.12. The database sits on a HP
Proliant DL585 server with 2 dual-core Opterons and 12 GB of RAM, running Linux
Fedora Core 3.
If this is InnoDB, then you might make sure that your InnoDB buffer pool
is set very large, say 10GB, and this will improve performance there.
If this is MyISAM, then you want to set myisam_sort_buffer_size and
key_buffer_size
to 4GB or just under. There is a 4GB limit currently on those settings.
myisam_sort_buffer_size is used for rebuilding an index and you need to make
sure your index will fit in that amount of memory when you partition your
tables.
If you continue to need help, then you might want to enlist our
on-site consulting for your project needs:
http://www.mysql.com/consulting/packaged/performance.html
Regards,
Josh
--
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL! www.mysql.com/consulting
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
