I kinda wondered how long it would take...
mysql> insert into foo_new select * from foo; Query OK, 60000000 rows affected (3 hours 3 min 52.94 sec) Records: 60000000 Duplicates: 0 Warnings: 0
Not bad.
(Note: Those are not actual table names.)
Posted by jzawodn at April 01, 2004 04:13 PM
A metric like that doesn't seem to provide much usable information. I can do 60M rows of one column on a 18 Ghz box and a terabyte of memory. What's the table type? What's the Column specification? How many indicies on the table? What are the specs of the box? Other info that would make this a meaningful number?
I've seen 7M rows take 32 hours (!?) to insert, but that was on a crappy 500 MHz sparc II w/2G, IDE, a few indices, etc., even using MySQL's extended insert...
Jeremy,
We had a similiar problem and we found a decent solution. Doing a select * from foo, no matter how big or small it is with 60 million rows will take some time. Especially since it won't use an index.
So figure out if you can put a where clause in there somehow that uses an index. And then do multiple inserts, like:
insert into foo_new select * from foo where bar < 5
insert into foo_new select * from foo where bar =5
But if you can't find one that will get you at least 20% of the rows than it's not worth your time.
Donny
what's wrong with:
cd /data/mysql
cp foo.frm foo_new.frm
cp foo.MYD foo_new.MYD
cp foo.MYI foo_new.MYI
chown mysql foo_new.*
or am i missing the point completely?
Justin - Unless you shutdown MySQL when you copy via the way you mentioned, the table will automatically repair itself. And with 60,000,000 rows, I can imagine it will take a while, depending on how many indexes.
Last week, I had a similiar problem, with a table wih 135,000,000 rows, it was only about 15 gigs of just data, and about 4.5 gigs of index and it took about 4 hours to rebuild.
Donny
A-ha! i didn't know about that table auto-repairing stuff. thanks.
"A metric like that doesn't seem to provide much usable information. I can do 60M rows of one column on a 18 Ghz box and a terabyte of memory. What's the table type? What's the Column specification? How many indicies on the table? What are the specs of the box? Other info that would make this a meaningful number?"
I think this post should not be a benchmark. It's more the fact that the insert took a while and now after more than 3,5 hours its over.
When my database does a job like this one, I would be happy to if its done.
Greetings
Dominik
"When my database does a job like this one, I would be happy to if its done."
And boy was I happy when the 32 hour insert was done...
Phew.
off topic: why are yahoo pushing "penis enlargment pill" adverts , when i'm browsing around their French regional directory? I wouldn't mind ads to do with France, but tacky penis adverts? Come on yahoo - you can do way better.
hey guys, I know this will sound ludicrous, but I know of a very specialized database that can perform this type of "copy" operation (2 million rows, each row is about 50 bytes) in about 10 seconds (on a thinkpad 240x 500Mhz PIII, 192 MB of RAM).
In fact, on a reasonably sized box (lots of CPU, lots of RAM), it can insert about 4 TeraBytes of Data in 20 minutes. Sounds unbelievable right?!!!!
What if I told you that this database was being funded by the US Army? ....
:)
--Anuj
