Created
October 9, 2018 06:48
-
-
Save ander94lakx/1ef27f497eccbda8e57114e441cf20ee to your computer and use it in GitHub Desktop.
A simple database load test with tables with random values
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import mysql.connector | |
import random | |
import sys | |
import time | |
import threading | |
DB_host="localhost" | |
DB_port="3307" | |
DB_user="root" | |
DB_passwd="toor" | |
DB_database="test" | |
multiThreading = True | |
threadNum = 3 | |
# Default values, can be changed with arguments | |
tableNumber = 5 | |
recordsToInsert = 1000000 | |
insertInBlock = True | |
blockSize = 500 | |
def singleQuery(tableNumber): | |
sql = "INSERT INTO table" + str(tableNumber) + " VALUES " | |
sql += singleValueSet(tableNumber) | |
sql += ";" | |
return sql | |
def singleBlockQuery(tableNumber): | |
global blockSize | |
sql = "INSERT INTO table" + str(tableNumber) + " VALUES " | |
for i in range (1, blockSize + 1): | |
sql += singleValueSet(tableNumber) | |
if i < blockSize: | |
sql += ", " | |
sql += ";" | |
return sql | |
def singleValueSet(tableNumber): | |
valueSet = " (" | |
for i in range (1, tableNumber + 1): | |
valueSet = valueSet + str(round(random.random(), 5)) | |
if (i != tableNumber): | |
valueSet += ", " | |
valueSet += ")" | |
return valueSet | |
def loadInDatabase(tableNumber, recordsToInsert, insertInBlock): | |
mydb = mysql.connector.connect( | |
host=DB_host, | |
port=DB_port, | |
user=DB_user, | |
passwd=DB_passwd, | |
database=DB_database | |
) | |
mycursor = mydb.cursor() | |
insertedRecords = 0 | |
while insertedRecords < recordsToInsert: | |
if insertInBlock: | |
sql = singleBlockQuery(tableNumber) | |
mycursor.execute(singleBlockQuery(tableNumber), multi=True) | |
insertedRecords += mycursor.rowcount | |
else: | |
mycursor.execute(singleQuery(tableNumber)) | |
insertedRecords += mycursor.rowcount | |
mydb.commit() | |
def startTest(): | |
global tableNumber | |
global recordsToInsert | |
start = time.time() | |
threads = [] | |
if len(sys.argv) > 2: | |
tableNumber = int(sys.argv[1]) | |
recordsToInsert = int(sys.argv[2]) | |
if multiThreading: | |
for num in range(0, threadNum): | |
thread = threading.Thread(target = loadInDatabase, args = (tableNumber, recordsToInsert / threadNum, insertInBlock)) | |
thread.start() | |
threads.append(thread) | |
for thread in threads: | |
thread.join() | |
else: | |
loadInDatabase(tableNumber, recordsToInsert, insertInBlock) | |
end = time.time() | |
delta = end - start | |
print("Took", delta ,"seconds to insert ", recordsToInsert, " records") | |
print("Inserts per second: ", recordsToInsert/delta) | |
if __name__ == "__main__": | |
startTest() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment