Bachinski: Robsco: not using the default innodb buffer pool and log file sizes, are you?
Bachinski: Robsco: what’s going to happen when you enable the unique constraint after the inserts? :-
Paparo: Been toying with these over the past few days too http://pastie.org/10424366
Maren: Enabling the unique checks hasn’t impacted things so far from what i’ve seen
Bachinski: Robsco: maybe you want to look at the IGNORE keyword for LOAD DATA INFILE
Pape: There’s no issues to ignore
Gitthens: Or does that stop it even checking?
Pratts: In addition to disabling unique checks?
Bachinski: Robsco: if you violate the unique constraint mysql will consider it an error, skip the insert and go on to the next
Gaufin: That’s ok, i’m confident in the data being unique for that indexed column
Deason: Innodb ignores your unique check disable
Bachinski: Robsco: you can stop checking for the unique constraint during the insert, but then what? I mean, the constraint is there for a reason, right?
Vedder: Bachinski, well nobody told all the results on Google that! :
Suite: I’ve just seen a million comments on removing that check for m*** inserts, if you’re confident
Bachinski: Robsco: none of those results are duplicates
Farrah: None of mine are duplicates, i know the data is unique
Bachinski: Robsco: ok, then why have a unique constraint on the column?
Koester: Because it is unique, i thought it would help with other possible updates later on during our workflow
Bachinski: Robsco: anyway, whatever. your buffer pool looks large, should be about 75%-80% of total available ram
Sirmans: The biggest issue is why are the inserts taking longer each time, with more data
Deason: Robsco: the docs tell you to disable unique checks: https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html
Bachinski: Robsco: what about innodd log file size? I’m not seeing that in the my.cnf file.
Emberling: 24 cores, tho such a shame I can’t take advantage of them
Mcanallen: Deason, i am disabling the unique checks
Bachinski: Robsco: also, start with auto_commit=0, and then back to 1 when the insert is complete
Deason: Robsco: yes, that doesn’t do anything despite millions of google results and the docs i sent you
Deason: Robsco: http://pastie.org/10424379
Bachinski: Deason: if he is using multiple separate connections and no unique constraints, shouldn’t he benefit from the multiple cores?
Peper: I’ve had 10 concurrent LOAD TABLE’s running and they always queue
Deason: Robsco: auto_inc primary key?
Diekema: Yup, was a rand string before but I fixed that
Bachinski: Robsco: https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_log_file_size — specific recommendations as to size
Bachinski: Robsco: that differs from log buffer size setting
Deason: Robsco: what is binlog_format, tx_isolation, and innodb_auto_inc_lock_mode ?
Helo: Thanks, thought i’d tried that but mysql didn’t like to start with it
Donnan: Since I got errors previously
Hazelhurst: Transaction-isolation=READ-COMMITTED
Fraker: Xgc: So what I would like to do is append these two select statements with a JOIN http://sqlfiddle.com/#!9/e9642/6
Skaff: Xgc: However I need to get the accountId from the first select statement.
Deason: Robsco: your innodb_log_file_size is most likely to blame
Cossano: But is that available to change on 5.1.73?
Deason: Robsco: yes*. also enable innodb plugin. * you have to do so manually, with care
Ostermann: Yeah, need to look more into the plugin
Volkman: This is all standard install on centos 6.7