Kinman: You might want to try dumping it many records at a time then
Kinman: SELECT * FROM pfam_data WHERE taxonomy LIKE ‘%Bacteria%’ LIMIT 0,10000; SELECT * FROM pfam_data WHERE taxonomy LIKE ‘%Bacteria%’ LIMIT 10000,10000; SELECT * FROM pfam_data WHERE taxonomy LIKE ‘%Bacteria%’ LIMIT 20000,10000;
Newlan: OneSploit, can you try “lock table pfam_data;” to skip row level locks?
Stramel: Limit is no good without order by
Rumsey: Jkavalik, the database and anything within that database is part of public domain because it was generated from the NIJ, are you ****ing kidding me? is this some kind of legal court room?
Rumsey: All the info and any part of the info is public domain
Wuitschick: Jkavalik, thanks will try
Pankiw: Rumsey, no, the database is maybe run by some private company which is getting paid for running it, the DATA in it are public, but you ACCESS to them is currently only possible through search box and other channels they provide
Elling: Jkavalik, I think the pfam_data table has no key/index, that’s why even select count* from pfam_data; takes forever.
Bowhall: OneSploit, but your buffer pool should be set to some 70% of memory available to mysql or at least as big as your database your is bigger so the 70% should be good one
Parise: OneSploit, count in innodb has to actually count, but yes, having no index will make it even slower
Slaugenhaupt: Jkavalik, I tried setting innodb_buffer_pool_size=8MB which is suppose to be default and even then restarting mysql freezes it
Khammixay: OneSploit, is there not even a primary key? is the table itself innodb?
Kinman: Jkavalik: pretty sure he’s just trollin, save your efforts for OneSploit 😛
Jacobson: Nate, I don’t use SQL everyday, I am not even a programmer, trying to do something for my research, so not trolling, but it’s kind of unkind to accuse someone of that
Vanoven: OneSploit, 8MB was default 10 year ago maybe, now it is not enough for innodb to even start probably
Kinman: OneSploit: I told him to save his efforts FOR you, wasn’t implying you were trolling 😛
Fisser: Ok sorry missunderstood
Atallah: OneSploit, you should set it to 3-5GB depending on what else is running on that computer.
Burnaman: Http://pastebin.com/4F0RBcmE
Takach: OneSploit, but if the table itself is bigger than your ram then it might be a problem without index.
Mickey: So it seems there is no key
Wacht: OneSploit, preferably use “show create table pfam_data;”, it gives precise statement to replicate in case of need
Tamkin: I plan to run this query on a 60GB myisam table “ALTER TABLE __wce__obj_property_eu PARTITION BY HASHPropID PARTITIONS 384;” is it supposed to run long time and on what does it depend ?
Ly: What’s the best way to go about combining two WordPress databases?
Koiner: Jkavalik, data_length column says 19310575616
Terepka: Leeyaa: yes it will be a long time. hope you have a space 60+G free. depends on IO speed.
Norrod: And yup none of the columns are unique here
Madi: Daspence: different table prefix per wp instance maybe. depends on the goal
Mcfarlain: Especially taxonomy record is a long string I want to search with “like” statement so making index out of it wont help I guess
Warnberg: OneSploit, and multiple columns making unique pair/tripple etc?
Gieck: Tried that on all columns
Yazzle: OneSploit, if you search the text for words, you might use fulltext index instead
Levecke: Hmm will google for that thanks
Campolongo: If there are totally duplicate columns, do they have any reasonable meaning? or is it just error in data?
Bumbaca: It happens to be one of tables, which has all together data I am interested in, other tables have primary keys I guess, but yeah they’re all correct records and meaningful, database is not corrupted
Rome: OneSploit, I mean – if there are two rows which are the same – does it have any meaning that there are two or more of them or would it mean the same thing if you removed the duplicates?
Grauer: Herriges, I see. you might add autoincrementing primary key to help you organize things a bit and then other indexes for speed fulltext for taxonomy if you have some use for it, standard for other columns you query if any
Gregoire: Konkel, trying that now, thanks a lot for help
Qualey: Rubner, check errorlog for actual reason for the freeze
Heppert: Cresencio, it did not stop on any of these errors so they are weird but not a probable reason for the freeze
Meabon: I will try to check buffer size now
Tajima: When I try to change it mysel does not restart. maybe I should reboot computer to free some memory
Kading: Im using curl now to download 30k pages
Ragar: Im going to test it on a clone server anyway but i find it interesting
Hackney: Totosz: both read and write 60G. it will recreate the table before swapping it over.
Hilliard: I did set innodb_buffer_pool_size to 1G hope that is enough
Hoiness: Does set profiling=1 affect performance a lot ?
Alba: Reeder, depends on how much you can spare, the more you offer the faster it will work, but as it seems you use your personal/development pc then try with 1GB and you will just have to wait longer
Malinchalk: Most important – recheck if partitioning is what you want. the limitation on unique indexes always tells me it is not the thing for my use cases.
Cruson: Apperanly there will be some downtime too
Ybarro: Mysql version is 5.5.43
Suares: Lacson: im not sure how stable that tool is
Uribe: Sorry, seems like my client is sending the same message multiple times.
Stenback: Jesus this is still running. i started it on a test server like 10m ago
Goslee: Well disks are pretty fast since they are netapp volumes but still .
Picciuto: Hey, can someone help a noob out with a mysql import query please?
Rozeboom: Cogswell: https://bpaste.net/show/eabd7e3351d5 thats bad ;p
Cogswell: Rozeboom, can you check the datadir to see how much has already been processed?
Boebinger: Blatnick: try your package manager
Showe: Wait, does 5.7 offer alter for myisam without locking? or i am wrong
Rumery: Botelho, check at https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html but I can find nothing at all about myisam in there. so no idea
Botelho: Rumery: we are stuck with myisam. changing engine is out of the question.