It’s easy to load multiple rows of data from a file into a MySQL table with a single LOAD DATA INFILE command, but if the file is over a certain size you may want to load it in multiple chunks. When I have a file with millions of lines I typically use pt-fifo-split to separate the file into multiple chunks in order to limit the size of each transaction. Unlike most tools in Percona Toolkit, pt-fifo-split doesn’t actually interact with your database, but this is one MySQL use case where it’s very helpful.
Here’s a quick example in bash that illustrates how I can take a large tab-delimited file, break it into multiple 100,000 line chunks, and load the chunks into a table:
“`
FLAT_FILE="/tmp/big_file.txt"
FIFO_PATH="${FLAT_FILE}.fifo"
LOAD_FILE="${FLAT_FILE}.load"
CHUNK_SIZE=100000
Split the file
pt-fifo-split –force –lines ${CHUNK_SIZE} ${FLAT_FILE} –fifo ${FIFO_PATH} &
Sleep 10 seconds to assure ${FIFO_PATH} exists before entering loop
sleep 10
while [ -e ${FIFO_PATH} ]
do
# Write chunk to disk
cat ${FIFO_PATH} > ${LOAD_FILE}
# Load chunk into table
mysql –database=test \
–show-warnings \
-vve "load data infile ‘${LOAD_FILE}’ into table my_table;"
done
“`
YMMV, so you should do some testing to determine the optimal chunk size based on your schema, storage engine, replication setup, complexity of LOAD DATA INFILE statement, etc.
via Planet MySQL
Loading large flat files into MySQL with LOAD DATA INFILE and pt-fifo-split
Dilbert 2014-12-12
6 Cross-Platform & Free File Sync Tools
How to Analyze a Business Opportunity
Use this condensed checklist as a way to begin judging whether the business opportunity will be successful and profitable.
via Business Opportunities Weblog
How to Analyze a Business Opportunity
Bake Muffins with Beautifully Domed Tops by Adding a Little More Flour
Homemade muffins and cupcakes are usually a far cry from professionally baked ones, coming out flatter and more compact. If you really care about that domed, picture-perfect top, though, Food52 offers a few tricks.
Consulting expert baker Zoe Nathan, the Food52 folks tested a few different approaches, from adding more flour (increasing from 1 1/4 cups to 1 2/3 cups), starting at a higher temperature, using the oven’s convention setting, and leaving the original chocolate chunk muffins recipe as is.
The extra flour muffin was the only one that came out rounded. However, it also ended up tasting more flour-y and dryer, more like a purchased muffin than homemade. Despite the sinking of the muffin in the original recipe, however, staff preferred that version.
A floury muffin taste might not be worth the mounded look, but if you want to try it, Food52 suggests going with lighter and airier recipes (without heavy add-ins) and making the batter as light as possible by using a folding motion.
Check out their post for even more tips and details.
How to Make Domed Muffins | Food52
via Lifehacker
Bake Muffins with Beautifully Domed Tops by Adding a Little More Flour
My Most Useful MySQL Performance Tools
Here is a list of my most useful tools that I use when doing performance audits.Please note, I am writing this mainly for myself, because I sometimes end up trying to find them in my other blog post about mastering indexing and this may save me time as well as a few changes that have happened over the years.Regular Slow Log Reportpt-query-digest slow_query.log >slow.txtAll Queries (that use indexes) for a certain tablept-query-digest slow_query.log –filter ‘($event->{fingerprint} =~ m/^(!?select|update|delete)/) && ($event->{arg} =~ m/mytable /) ‘ –limit=100% >mytable.txtLongest Running Select Queries – most painful queries with response time % right next to them.pt-query-digest slow_query.log –filter ‘($event->{fingerprint} =~ m/^(!?select)/)’ –order-by=Query_time:max > select.txtFilter the slow log for all Select queries for a certain tablept-query-digest slow_query.log –filter ‘($event->{fingerprint} =~ m/^(!?select)/) && ($event->{arg} =~ m/mytable /) ‘ –no-report –output=slowlog >mytable.logFind unused indexes from pre-filtered table’s logs pt-index-usage mytable.log –host 127.0.0.1 –tables mytable >mytable_indexes.txt Find Top 15 Largest tables on a server (use with caution) – (from http://ift.tt/Ifs2kC)—————————–delimiter $$create procedure dba.largest()beginSELECT CONCAT(TABLE_SCHEMA, ‘.’, TABLE_NAME) AS TABLE_NAME,CONCAT(ROUND(TABLE_ROWS / 1000000, 2), ‘M’) ROWS,CONCAT(ROUND(DATA_LENGTH / ( 1024 * 1024 * 1024 ), 2), ‘G’) DATA,CONCAT(ROUND(INDEX_LENGTH / ( 1024 * 1024 * 1024 ), 2), ‘G’) IDX,CONCAT(ROUND(( DATA_LENGTH + INDEX_LENGTH ) / ( 1024 * 1024 * 1024 ), 2), ‘G’) TOTAL_SIZE,ROUND(INDEX_LENGTH / DATA_LENGTH, 2) IDXFRACFROM INFORMATION_SCHEMA.TABLESORDER BY DATA_LENGTH + INDEX_LENGTH DESCLIMIT 15;end $$delimiter ;———————————ToDo:Run filtered log against database with the percona toolkit log player to test index improvements.
via Planet MySQL
My Most Useful MySQL Performance Tools
2014: The Year in Photos, September-December
New Hot Tub Time Machine Trailer? New Hot Tub Time Machine Trailer!
Having conquered the demons of their past in 1986; our plucky heroes from Hot Tub Time Machine are living the good life in a modern world of their own making. But sometimes leaving well enough alone just isn’t good enough so it’s back into the hot tub for Nick, Lou, and Jacob for another riotous romp through space-time. This time, however, they’re going back…to the future!
via Gizmodo
New Hot Tub Time Machine Trailer? New Hot Tub Time Machine Trailer!
The Avengers Sing Xmas Carols
Video editor and game developer James Covenant wishes you a very Marvel Christmas with this funny edit. Watch as he makes it seem like Cap, Iron Man, their friends and foes sing Christmas carols, with a special guest at the end.
via The Awesomer
The Avengers Sing Xmas Carols
Hear the terrifying sound of the A-10 tank killer’s gun in an attack run
This video contains one of the most terrifying human-produced sounds you can hear in the planet: The screaming noise from hell of an A-10 Warthog’s GAU-8 Avenger gatling gun. Some of the very few who were on its reticle and survived say its banshee scream was the worst thing they have ever heard.
It is a demonic sound indeed.
SPLOID is a new blog about awesome stuff. Join us on Facebook
via Gizmodo
Hear the terrifying sound of the A-10 tank killer’s gun in an attack run