LargeDataSets DBMSTesting 2012 13

From Wiki
Jump to: navigation, search

Nullius in verba (Motto of the Royal Society, the oldest "learned society for science" in the world. Roughly translated: "Trust no one's word.")

DBMS Performance Testing Group work

main AHPCRC wiki page

Fall 2012 Performance Group

No test results posted by students?? MT sighs sadly.

Oct 15 2012

To Do: generate fake data using code below. Use scripts to load data into your MySQL database, and time how fast the data loads. Record your OS and hardware specs, and timing results here.

Student nameOShardwarenumber tuples how long it took to load
?OS?10,000 ?
?OS?1,000,000 ?
?OS?100,000,000 ?
?OS?200,000,000 ?
?OS?300,000,000 (USA pop) ?
?OS?500,000,000 ?


Sam's script for inserting files full of tuples into a MySQL DBMS and timing the insertion, on a Unix-based OS. Might need to run "chmod ogu+x scriptname.script" at the command line before the script will work. (Changes permissions on the script file, telling the OS to make it executable.)

Starting the MySQL server via Terminal window on a Mac, type this in a Terminal:

cd /opt/local ; sudo /opt/local/lib/mysql55/bin/mysqld_safe

(You will have to start another Terminal to do anything else, since MySQL will be running in the Terminal window you type the command in.)

Once you've done that, type this in a different Terminal window to open an interactive command line session with MySQL:

/opt/local/lib/mysql55/bin/mysql -u root -pyourpassword

To set root password via terminal window, try:

/opt/local/lib/mysql55/bin/mysqladmin -u root password 'new-password'


/opt/local/lib/mysql55/bin/mysqladmin -u root -h nameofyourownmachine password 'new-password'

(nameofyourownmachine can by found by typing "uname -a" at Terminal window. Will be second item on resulting list.)

A helpful page for MySQL can be found at Files for generating fake Census data.,,, and, to support the generation of last names (Names are copied from, lastNames1-250.txt, lastNames251-500.txt, lastNames501-750.txt, lastNames751-1000.txt. Lists of names are courtesy of

New SQL create table statement to match above text file:

drop table fakecensus;

create table fakecensus ( identifier int auto_increment, gender char(1), income int, lastname char(50), age int, primary key (identifier) );

load data local infile '/Users/wherever/whatever.txt' into table fakecensus fields terminated by ',';

Notes: There must be a comma at the front of each line, for the auto_increment field to be created properly.

A webpage that can probably explain you how to work with SQL

Oct 8 2012

Start by reading: "Pathologies of Big Data", Communications of the ACM, August 2009. Read this by next week. You might need to be on a campus computer to access the full text of the article.

Install MySQL on your own machine. (Set up passwords! Don't leave root/superuser/administrator account on MySQL unprotected.)

Thomas slides from Aug 2011 DBMS/MySQL quickie workshop - might be useful in figuring out MySQL installation. Or not. Depends on your computer.