Post #294,439
10/8/07 11:09:32 PM
|
Some things are still slow in MySQL.
It's quicker to let it build the indices during import than to generate them later. Mostly this is because altering a table - including changing indices - involves copying the table. This is what takes time. This is why if you have a lot of changes to make to a table's structure, it is far and away fastest to do it in a single ALTER TABLE statement.
Wade.
Is it enough to love
Is it enough to breathe
Somebody rip my heart out
And leave me here to bleed
| |
Is it enough to die
Somebody save my life
I'd rather be Anything but Ordinary
Please
|
-- "Anything but Ordinary" by Avril Lavigne. | · my ·
· [link|http://staticsan.livejournal.com/|blog] ·
· [link|http://yceran.org/|website] · |
|
Post #294,442
10/8/07 11:59:22 PM
|
Oh yeah - I forgot about that
I was wondering why I saw the temp table that seemed to mirror the data.
That's OK, I'll be rebuilding a few times as I test out various schema definitions.
That particular index build was hung when I ran out of disk in the logging partition. I don't need logs, so I turned that off.
It seemed to have taken 3 hours to build when I restarted. I've blindly created varchar field and need to turn a bunch into real numbers. Should get a bit quicker then as well.
|
Post #294,444
10/9/07 3:28:52 AM
|
I saw an Oracle guy do a Bit Of Learning...
He was very idealistic, tended to believe in The Power Of The Database kind of stuff. Didn't understand that we needed to look after some scaling concerns outside of MySQL because MySQL couldn't do them. Liked references and stored procedures, you probably get the idea.
And got caught by the same re-indexing trap you fell in. :-) It may indeed be faster in Oracle to enable the indexing after you load the table, but this is not so in MySQL.
Wade.
Is it enough to love
Is it enough to breathe
Somebody rip my heart out
And leave me here to bleed
| |
Is it enough to die
Somebody save my life
I'd rather be Anything but Ordinary
Please
|
-- "Anything but Ordinary" by Avril Lavigne. | · my ·
· [link|http://staticsan.livejournal.com/|blog] ·
· [link|http://yceran.org/|website] · |
|
Post #294,452
10/9/07 9:20:16 AM
|
You teased me
I thought you were about to show me the magic of MySql Bitmapped indexes.
|
Post #294,485
10/9/07 4:58:50 PM
|
On the other hand, maybe those MySQL people
might be learning and copying from those Oracle people.
[link|http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html|http://dev.mysql.com...insert-speed.html]
With some extra work, it is possible to make LOAD DATA INFILE run even faster for a MyISAM table when the table has many indexes. Use the following procedure:
1. Optionally create the table with CREATE TABLE.
2. Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.
3. Use myisamchk --keys-used=0 -rq /path/to/db/tbl_name. This removes all use of indexes for the table.
4. Insert data into the table with LOAD DATA INFILE. This does not update any indexes and therefore is very fast.
5. If you intend only to read from the table in the future, use myisampack to compress it. See Section 13.1.3.3, “Compressed Table Characteristics”.
6. Re-create the indexes with myisamchk -rq /path/to/db/tbl_name. This creates the index tree in memory before writing it to disk, which is much faster that updating the index during LOAD DATA INFILE because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced.
7. Execute a FLUSH TABLES statement or a mysqladmin flush-tables command.
|
Post #294,496
10/9/07 9:52:48 PM
|
OT have you looked at hadoop?
[link|http://lucene.apache.org/hadoop/|http://lucene.apache.org/hadoop/] looks like very interesting tech for write once read lots high volume applications. A database that only contained a path to the information would be fairly quick.
thanx,
bill
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Any opinions expressed by me are mine alone, posted from my home computer, on my own time as a free american and do not reflect the opinions of any person or company that I have had professional relations with in the past 51 years. meep
reach me at [link|mailto:bill.oxley@cox.net|mailto:bill.oxley@cox.net]
|
Post #294,499
10/9/07 10:35:25 PM
|
Nope
My type of queries are counts across a hundred million records.
Hadoop would bounce that around a few machines.
|
Post #294,515
10/10/07 8:37:47 AM
|
/me rubs eyes
did I just read "hadpoop"?
Smile,
Amy
|
Post #294,530
10/10/07 4:48:24 PM
|
You might have read it, but it's not what it said.
|
Post #294,532
10/10/07 5:15:03 PM
|
I'm aware of that now
but this morning I was getting ready to leave for a colonoscopy/endoscopy. I think it affected my eyes ;-)
Smile,
Amy
|
Post #294,534
10/10/07 5:26:34 PM
|
Ah. Forgot to get your head out, hadya?
;-)
Hope you had fun. Heh. ;-)
[link|mailto:MyUserId@MyISP.CountryCode|Christian R. Conrad]
(I live in Finland, and my e-mail in-box is at the Saunalahti company.)
Ah, the Germans: Masters of Convoluted Simplification. — [link|http://www.thetruthaboutcars.com/?p=1603|Jehovah]
|
Post #294,498
10/9/07 10:21:48 PM
|
Interesting.
It's a bit of extra work, though. And wouldn't be useful for an InnoDB table.
I've seen a few areas where MySQL programmers need to spend some serious time at the codebase. Re-indexing is one of them, although I suspect a lot of the issues are to do with the way it handles table alterations, which are likely due to the engine system. (ALTER TABLE is not transactionable in any engine, and blocks all other activity until it completes.)
Wade.
Is it enough to love
Is it enough to breathe
Somebody rip my heart out
And leave me here to bleed
| |
Is it enough to die
Somebody save my life
I'd rather be Anything but Ordinary
Please
|
-- "Anything but Ordinary" by Avril Lavigne. | · my ·
· [link|http://staticsan.livejournal.com/|blog] ·
· [link|http://yceran.org/|website] · |
|
Post #294,500
10/9/07 10:38:18 PM
|
"It's a bit of extra work" - not that much
My style of usage is monthly builds with extensive queries in between.
A simply script can handle it all.
|
Post #294,509
10/10/07 7:22:01 AM
|
Ah. I see. Wouldn't suit my use. :-)
Is it enough to love
Is it enough to breathe
Somebody rip my heart out
And leave me here to bleed
| |
Is it enough to die
Somebody save my life
I'd rather be Anything but Ordinary
Please
|
-- "Anything but Ordinary" by Avril Lavigne. | · my ·
· [link|http://staticsan.livejournal.com/|blog] ·
· [link|http://yceran.org/|website] · |
|
