mariadb - Replace Into Table in MySQL InnoDB extremely slow -


using mysql (mariadb exact though). have following script needs run every other day update database, it's unbearably slow. each table updated takes hours run. it's shell script:

cmd_mysql="${mysql_dir}mysql  --local-infile=1 --default-character-set=utf8 --protocol=${mysql_protocol} --port=${mysql_port} --user=${mysql_user} --pass=${mysql_pass} --host=${mysql_host} --database=${mysql_db}"  ### update mysql data ### ## table name lowercase tablename=`echo $file | tr "[[:upper:]]" "[[:lower:]]"` echo "uploading ($file) ($mysql_db.$tablename) replace option..." ## let's try replace option $cmd_mysql --execute="load data local infile '$file.txt' replace table $tablename character set utf8 fields terminated '|' ignore 1 lines;" ## need erase records, not updated today echo "erasing old records ($tablename)..." $cmd_mysql --execute="delete $tablename datediff(timestamp, now()) < 0;"

you may safely ignore variables set somewhere else in file. $file txt file delimited |. each row represents 1 record, example:

airportid|airportcode|airportname|latitude|longitude|maincityid|countrycode 6024358|dme|moscow, russia (dme-domodedovo intl.)|55.414495|37.899907|2395|ru 6024360|dmm|dammam, saudi arabia (dmm-king fahd intl.)|26.468075|49.796824|180543|sa

the script runs on existing database, old records found. check it's last updated date, , performs replace into, takes 8 hours 100mb txt file.

how can improve speed?

this issue affects versions of innodb (4.1+). replacing duplicates in innodb slow. 1 place myisam superior. myisam took 0.05 seconds.

the reason thatthe duplicate key error handling in innodb relies on undo log , statement rollback: 1. lock record. 2. write update_undo log delete-marking record. 3. write insert_undo log inserting record. 4. attempt insert new record. 5. notice duplicate. 6. roll undo log written in steps 3 , 2.

this issue in mysql has been fixed & verified in version 5.7: https://bugs.mysql.com/bug.php?id=71507

they planning detect duplicate @ step 2. avoid rollback in case.

here 3 suggestions:

  1. upgrade v 5.7
  2. use myisam if applicable
  3. use queries such "insert ... on duplicate key update ..."

-

Comments

Post a Comment

Popular posts from this blog

php - regexp cyrillic filename not matches -

i take list of files php glob(."*.pdf") . files have cyrillic names. example: ООО «Рога и копыта» then load company names database , try find them in file names: preg_match("/$firm_name/ui", $file_name, $match); i have problem: firms if $firm_name visually similar $file_name , preg_match says there no match. mb_detect_encoding both variables return utf-8. there problem in file name, or maybe there encoding or something, can not understand, ask help. upd: $file_name='ООО «Рога и копыта»'; preg_match("/... .Рога и копыта./ui", $file_name, $match); as result, ОО «Рога и копыта» , 1 О missing
Read more

c# - OpenXML hanging while writing elements -

Image
i have program pulls data database, caches file , exports data multiple formats (excel, excel 2003, csv). i'm using openxml sdk 2.0 excel work. these export processes run in parallel (using parallel.foreach ), , amount of data can pretty large - e.g. csvs 800mb. during these larger exports, i've noticed writing of xml documents hang. instance, if have 8 exporting in parallel, @ point "pause". hang around same point: //this.writer openxmlwriter created worksheetpart. this.writer.writeelement(new cell() { cellvalue = new cellvalue(value), datatype = cellvalues.string }); when happens, pause debugger (vs2013 in case) , notice threads blocking around same portion of code - bit deeper in openxml sdk - stem call openxmlwriter.writeelement . i dug through source using justdecompile didn't find answers. appears there intermediary stream in use writing isolated storage , is, reason, blocking. underlying stream each of these filestream . here screenshot ...
Read more

sql - Select Query has unexpected multiple records (MS Access) -

i may tired, can't seem figure out happening. have 2 queries. query 1 has 10 fields. query 2 has 5 fields. fields 1 through 3 same in both queries. trying write select query , simple add field 5 query2 query1 result should query1 additional field (query2.field5). i join first 3 fields both queries , choose select records in query1 , in query2 match. don't know sql, use query designer. expect same number of records have query 1 instead have 3 times or more. can tell me doing wrong. tried reversing joins still seem same records. thanks in advance. here query: the fields in common divisionname , supplierid = vendorid , commodityname . select [part revenue exposed query p1].divisionname, [part revenue exposed query p1].commodityname, [part revenue exposed query p1].suppliername, [part revenue exposed query p1].partnumber, [part revenue exposed query p1].partdescription, [part revenue exposed query p1].bucode, ...
Read more