IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Ok, playing with 5.0
160MM tiny records loaded in 4.5 minutes, but the index creation is taking FO-EVA!

Any tweaks you recommend to speed it up?
New 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] ·

New 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.
New 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] ·

New You teased me
I thought you were about to show me the magic of MySql Bitmapped indexes.
New 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.
New 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]
New Nope
My type of queries are counts across a hundred million records.
Hadoop would bounce that around a few machines.
New /me rubs eyes
did I just read "hadpoop"?
Smile,
Amy
New You might have read it, but it's not what it said.
New 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
New 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]
New 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] ·

New "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.

New 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] ·

     Hey Greg! Database choice time - (crazy) - (40)
         PICK? /me dux -NT - (boxley) - (1)
             You can't duck low enough. </CLUB> -NT - (crazy)
         Firebird? - (CRConrad)
         Well, I've got a new religion lately... - (folkert) - (21)
             Hmm, are they repeating Oracle's error: TOO MUCH "tweakage"? -NT - (CRConrad)
             :-O - (static)
             Oh and here is the MySQL Proxy page - (folkert)
             Ok, playing with 5.0 - (crazy) - (14)
                 Some things are still slow in MySQL. - (static) - (13)
                     Oh yeah - I forgot about that - (crazy) - (12)
                         I saw an Oracle guy do a Bit Of Learning... - (static) - (11)
                             You teased me - (crazy)
                             On the other hand, maybe those MySQL people - (crazy) - (9)
                                 OT have you looked at hadoop? - (boxley) - (5)
                                     Nope - (crazy)
                                     /me rubs eyes - (imqwerky) - (3)
                                         You might have read it, but it's not what it said. -NT - (CRConrad) - (2)
                                             I'm aware of that now - (imqwerky) - (1)
                                                 Ah. Forgot to get your head out, hadya? - (CRConrad)
                                 Interesting. - (static) - (2)
                                     "It's a bit of extra work" - not that much - (crazy) - (1)
                                         Ah. I see. Wouldn't suit my use. :-) -NT - (static)
             You play with partitioning yet? - (crazy) - (2)
                 Nevermind - needed key() -NT - (crazy) - (1)
                     Unusable - (crazy)
         Rare updates, mostly reads? - (admin) - (4)
             Still need SQL access - (crazy) - (3)
                 there is a sql like addon for berkley I dont care for it tho -NT - (boxley)
                 "Guide to Oracle Berkeley DB for SQL Developers": - (CRConrad) - (1)
                     I'm sorry, you must have thought I wote (new thread) - (crazy)
         Here we go again - (crazy) - (9)
             Did you use what version of MySQL. - (folkert) - (2)
                 No you didn't - (crazy) - (1)
                     This is the 5.1 list of enchancements - (crazy)
             MySQL has lots of ah incompleteness in it's optimizer. - (static) - (2)
                 I have a tough time restructuring - (crazy) - (1)
                     Fair enough. -NT - (static)
             Sigh... Didn't look too deeply into Firebird, didja? - (CRConrad) - (2)
                 Sigh - This time it's an option - (crazy)
                 Ehh, you never CARED! - (crazy)

Department of Redundancy Department
91 ms