| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Comments:
<0> nothing
<1> jtacy: http://gallery.pastebin.com/542773
<0> i'll paste
<0> CREATE TABLE `LUM_Category` (
<0> `CategoryID` int(2) NOT NULL auto_increment,
<0> `Name` varchar(100) NOT NULL default '',
<0> `Description` text NOT NULL,
<0> `Order` int(11) NOT NULL default '0',
<0> PRIMARY KEY (`CategoryID`)
<0> ) TYPE=MyISAM;
<2> next time, use a paste bin
<0> ok
<0> what should i do?
<2> how are you executing that sql file?
<3> DROP TABLE LUM_Category
<3> Heh, INT(2)? :)
<3> Don't quote the 0
<3> I'm going crazy
<2> calm down will :)
<0> firewire: ./database.sql
<4> Joe7slp: looks harsh...I wonder how hard the concat() is hitting the query...how bad is it, anyway?
<2> junix-other try piping it into mysql
<0> how so?
<2> mysql < database.sql
<1> jtacy: it's very bad.. over 0.5sec in avarage.. :/
<3> I don't think ./ gives that kind of error...
<5> junix-other: Did you post the full file [database.sql] for download?
<0> k
<0> Xgc: huh?
<5> junix-other: It might be easier to spot the error if you post the actual file.
<1> jtacy: but the main problem (imho) is that AccessSubscriberMap has over 300k+ records
<5> junix-other: or just chop down the file until you have something small that still shows the error and post that smaller file.
<4> Joe7slp: 300k should be no problem with enough indexes
<0> ok
<1> jtacy: i see. the columns in there, both indexed..
<5> junix-other: The full file would probably be best. How large is it?
<1> s/the/two
<0> Xgc: i don't know its for vanilla
<5> junix-other: You don't know the size of the file? What's vanilla?
<0> Xgc: a forum
<5> Well, those were strange responses.
<0> www.getvanilla.com
<4> Joe7slp: tricky...0.5s isn't a whole lot of time to work with
<5> Let me know if the database.sql file is made accessible.
<1> but it is, when it's called in almost every sec ;)
<0> Xgc: i ahve to change the perms on it
<4> Joe7slp: can you cache the results at the app level?
<1> i would, but the cache needs to be invalited too often :/
<4> Joe7slp: sorry, it's late and I don't have ideas
<4> seems unnecessary to throw more hardware at the problem with only 300k rows
<1> i think so..
<6> How do I set MySQL default Storage Engine type?
<6> Right now it's InnoDB
<6> I want to change it to MyISAM
<0> Xgc: http://ww.pastebin.com/542790
<4> Aloupha -> http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html
<5> junix-other: There's no error in that file.
<0> why won't it work tho....
<0> should i create the database....?
<5> junix-other: It works just fine for me.
<5> junix-other: You've never tried?
<0> i've tried
<5> junix-other: What version of MySQL?
<0> debian stable
<0> 4.x.x
<5> Ouch.
<0> why?
<0> is that why?
<5> junix-other: Be specific.
<0> 1 sec
<5> junix-other: select version();
<0> 4.0.24
<5> junix-other: That's a bit old. Maybe that's your problem.
<0> hmmm.....
<0> i guess i could do pinning....
<7> Problem: Deleting 400,000+ rows out of a myisam table (65M rows) takes forever... Box does not swap, key buffer size is set to 400MB, innodb buffer 1100 ... 2 main innodb tables that are active on sdb, and the myisam tables are on sda ... What would you guys do ? Only 1 index on the table... it's an unsigned int (has to be)
<3> What is the DELETE query?
<8> delete from TABLE where CONDITION
<8> err
<8> sorry
<3> I WANT HELP BUT DON'T WANT TO ANSWER ANY QUESTIONS ABOUT MY PROBLEM.
<9> PLUS YOU SEEM TO LOVE USING CAPS
<3> NO ACTUALLY MY SHIFT KEY GETS STUCK SOMETIMES
<3> IM TOO POOR TO BUY A NEW KEYBOARD
<9> OH, MINE DOES THAT TOO
<3> IT ALSO MAKES IT EASIER TO READ WHEN THE LETTERS ARE BIGGER.
<3> 640X480 ON THIS 22" CRT IS STILL TOO SMALL
<9> THAT IS TRUE. HOWEVER, IT ALSO MAKES ME FEEL LIKE I'M RATHER DEATH
<8> ...
<8> guys
<3> YOU ARE DEATH?
<7> it's a join... delete from table using smalltable, table where smalltable.id = table.id ;
<10> hello
<3> goodbye
<7> will, did you catch that
<3> LAKEZ: ARE THE COLUMNS INDEXED? I WANT THE FULL XACT QUERY
<3> NO I CANT CATCH BECAUSE MY EYESIGHT ****S
<7> that is the full exact query, it's indexed
<3> IDS ON BOTH TABLES ARE INDEXED?
<7> yes, and they're identical in size
<3> DARN
<3> ASK DARIEN HE KNOWS STUFF LIKE THIS
<7> it blazes if i disable the innodb .. but where the box is innodb heavy, myisam suffers bigtime.. and myisam is on a different drive
<7> will, what's with the caps ? heh
<7> d
<7> Darien: Problem: Deleting 400,000+ rows out of a myisam table (65M rows) takes forever... Box does not swap, key buffer size is set to 400MB, innodb buffer 1100 ... 2 main innodb tables that are active on sdb, and the myisam tables are on sda ... What would you guys do ? Only 1 index on the table... it's an unsigned int (has to be)
<11> will: >:|
<11> lakez: is the index on the column you're using in your WHERE clause?
<7> io'
<7> Darien, yes ...
<10> anyone know much about the C API for mysql? i'm having a bit of a problem
<7> the select version of the query shows it's using the index properly
<9> lakez: explain'ed it?
<7> yes
<7> fellas, i've gone thru the whole thing... the bases are covered.. I'm stuck where it comes to a heavy innodb box and trying to use a myisam table.. it might be best to just convert that 3rd large table to innodb and see how it goes
<11> lakez: or you could do like a LIMIT 50000 and then just keep running it until it manages zero rows
<11> lakez: did you try optimizing the table? >:D
<7> Darien, yes the tables are optimized
<7> i haven't tried the limit tho
<11> at the very least, it will keep the table from being locked the whole time
<11> so even if it takes eight hours, at least it won't be locked for eight consecutive hours :p
<7> it's just heavy innodb use on sdb, and lack of use on sda (i put the myisam table on this drive... i see it using 90-95% of drive use when i'm doing the delete)
<11> lakez: fact of the matter is, it's a lot to do
<11> deleting, updating the index, optimizing
<11> it has to remove 400000 rows worth of data, which is rarely a trivial amount
<9> daring question.. does the table with which you're deleting from fit totally into RAM?
<11> did you try doing an EXPLAIN ?
<11> NeoThermic: haha
<11> NeoThermic: I hope I don't see where you're going with this :p
<7> Darien, I'm thinking of doing a new table... create new table .. select a.* from table left join todeletetable using(id) where ... id is null ... and then building that table
<9> Darien: if you're laughing, you do. However, if the answer is yes, isn't that the quickest idea?
<11> NeoThermic: well theoreticall, yes
<7> no, the key MYI is larger than the buffer pool for myisam
<7> but i can't do anything about that
<7> can'
<7> can't toggle innodb buffer pool on the fly, so i'm stuck
<7> rather than toggle, modify on the fly
<12> lakez: sounds like you have the same problem I'm wrestling with now
<7> quuxman, tell me your problem
<12> lakez: table with 75 million rows, trying to update 22 million, and according to my estimates it will take several days
<7> quuxman, i've already solved that issue.. :)
<12> lakez: I gave up and dumped it to a flat file and I'm using a perl script
<7> no, there's a way to do it fast, at least for me
<12> lakez: then I'll re"LOAD DATA FROM FILE"
<13> hey
<11> quuxman: InnoDB ftw
<13> what's that sql command to dump a single row or data from your sql entries into a text file
<13> INSERT INTO ?
Return to
#mysql
or
Go to some related
logs:
opera collie hentges
xorg 7.1 cross compiling
#perl
#osdev
#postfix
#perl
centos cannot run in framebuffer mode
#web
xbunutu
DSDT a8n-e
|
|