В статье приведены результаты SQL-тестирования для трех клиентов:
1. Python (v. 3.8.10)
2. Java (v. 11.0.16) / Scala (v. 2.11.12)
3. Go (v. 1.19)
Тестировались две базы данных:
1. PostgreSQL (v. 12)
2. Clickhouse (v. 18) / (v. 22)
Для PostgreSQL все тесты проведены на версии 12.
Для Clickhouse использовалась в основном 18-я версия, но в случае с Clickhouse jdbc клиентом
пришлось специально использовать 22-ю версию.
Сами сервера находились на реальном, а не виртуальном железе.
Основными клиентами являются Python и Java. Go здесь присутствует для разнообразия, поскольку в отличие от интерпретатора Python и виртуальной машины Java, он является компилятором, да еще со сборщиком мусора.
Тестирование заключалось в проведение двух операций:
1. Вставка 10 миллионов записей посредством батчей
2. Выборка этих записей
При этом замерялось время выполнения теста, а в случае с выборкой еще и количество памяти,
выделяемое под формирование динамического массива размером в 10 миллионов записей.
Тесты проводились на простой таблице с 5-ю полями.
В случае PostgreSQL это была таблица:
CREATE TABLE if not exists test
(
id bigserial,
amount int,
summa numeric(10,2),
name varchar(1000),
datetime timestamp default now()
) ;
В случае Clickhouse:
create table if not exists test(
id integer,
amount integer,
summa Float64,
name String ,
datetime DateTime DEFAULT now())
ENGINE = MergeTree()
ORDER BY (id)
SETTINGS index_granularity = 8192
В таблице время - в секундах, память, выделяемая клиенту операционной системой на выборку при селекте - в гигабайтах
В глаза бросаются несколько моментов:
1. Python - самый медленный на чтение.
2. Scala - очень медленная реализация вставки в постгрес (> 3 минут !), даже с использованием батча.
3. Go - быстрое чтение, но скорость вставки в постгрес медленнее, чем у Python, возможно, из-за того,
что я неудачно выбрал драйвер.
По результатам этого теста:
1. Самая быстрая вставка в Postgres - Python, как ни странно.
2. Самая быстрая вставка в Clickhouse - Go.
3. Самое быстрое чтение в PostgreSQL - Go / Scala.
4. Самое быстрое чтение в Clickhouse - Java.
Ниже приведена сравнительная таблица результатов тестирования:
PostgreSQL
Clickhouse
insert
select
memory
insert
select
memory
Python
50 с.
25 с.
6 Гб.
5 с.
20 с.
2 Гб.
Scala/Java
192 c.
7 c.
4 Гб.
8 c.
2 c.
4 Гб.
Go
70 с.
7 с.
3.5 Гб.
2 с.
6 с.
3.5 Гб.
Ниже приводятся исходники всех клиентов, участвующих в тесте.
Python клиент для PostgreSQL:
import psycopg2
from datetime import datetime, timedelta
import datetime as dt
import os
from io import StringIO
from numpy.random import randint
import string
import random
data = []
def get_connect():
conn = psycopg2.connect(dbname='postgres', user='postgres', host='localhost')
return conn
def create_table(connect, table_name):
connect.set_isolation_level(0)
cursor = connect.cursor()
try:
try:
_str = '''drop TABLE %s''' % table_name
cursor.execute(_str)
except:
pass
_str = '''
CREATE TABLE if not exists test
(
id bigserial,
amount int,
summa numeric(10,2),
name varchar(1000),
datetime timestamp default now()
) ;'''
cursor.execute(_str)
_str = 'ALTER TABLE test REPLICA IDENTITY FULL;'
cursor.execute(_str)
except Exception as ex:
print('... create_table error:', ex)
pass
cursor.close()
def select_sql(connect, _sql, table_name):
connect.set_isolation_level(0)
cursor = connect.cursor()
cursor.execute(_sql)
for f in cursor.fetchall():
print(table_name, f)
cursor.close()
def select_sql_test(connect, _sql, table_name):
connect.set_isolation_level(0)
cursor = connect.cursor()
cursor.execute(_sql)
# while(True):pass # 3.5 Гб
for f in cursor.fetchall():
_data = []
_data.append(f[0])
_data.append(f[1])
_data.append(f[2])
_data.append(f[3])
_data.append(f[4])
data.append(_data)
#print(_data)
#while(True):pass # 9.5 Гб
cursor.close()
def bulk_copy(f, cursor, table_name, _columns):
try:
columns = tuple(_columns)
cursor.copy_from(f, table_name, columns = columns, sep=";", null='')
except Exception as ex:
print('... bulk_copy error:', str(ex))
def insert(connect, table_name, columns, test_count):
connect.set_isolation_level(0)
cursor = connect.cursor()
d_start = datetime.now()
c = 0
count = 1000
_sql = ''
_id = 0
for i in range(0, count):
_amount = randint(0, 1000000)
_summa = randint(0, 1000000)
_name = ''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(100))
_sql += str(_amount)
_sql += ';'
_sql += str(_summa)
_sql += ';'
_sql += str(_name)
if table_name == 'test2':
_sql += ';'
_sql += str(_name)
_sql += '''
'''
count2 = int(test_count / 1000)
for i in range(0, count2):
f = StringIO(_sql)
bulk_copy(f, cursor, table_name, columns)
cursor.close()
select_sql(connect, 'select count(*) from %s' % table_name, table_name)
connect = get_connect()
print(connect)
test_count = 10000000
create_table(connect, 'test')
datetime_start = datetime.now()
insert(connect, 'test', ['amount', 'summa', 'name'], test_count)
datetime_end = datetime.now()
diff = (datetime_end - datetime_start).total_seconds()
print('Время вставки %s записей: ' % test_count, diff)
datetime_start = datetime.now()
select_sql_test(connect, 'select * from test order by id', 'test')
datetime_end = datetime.now()
diff = (datetime_end - datetime_start).total_seconds()
print('Время селекта %s записей: ' % test_count, diff)
print('Размер прочитанного массива: ', len(data))
Python клиент для Clickhouse:
from clickhouse_driver import Client
from datetime import datetime
import random
from numpy.random import randint
import string
def create_clickhouse(client):
res = client.execute(' drop table if exists test')
res = client.execute('''
create table if not exists test(
id integer,
amount integer,
summa Float64,
name String ,
datetime DateTime DEFAULT now()
)
ENGINE = MergeTree()
ORDER BY (id)
SETTINGS index_granularity = 8192
''')
return res
def insert_clickhouse(client, rows):
try:
_str =''' insert into test(
amount,
summa,
name)
values '''
res = client.execute(_str, rows)
except Exception as ex:
print('... insert_clickhouse error:', ex)
for row in rows:
_str = str(row)
def select_clickhouse():
client = Client(host='localhost', port='9000', user='default', database='default')
data=[]
date_start = datetime.now()
res = client.execute('select * from test')
# while True: pass # 5 Гб
for f in res:
_data = []
_data.append(f[0])
_data.append(f[1])
_data.append(f[2])
_data.append(f[3])
_data.append(f[4])
data.append(_data)
date_end = datetime.now()
_diff = (date_end - date_start).total_seconds()
print("Время выборки 10 миллионов записей - %s секунд" %_diff)
# while True: pass
client = Client(host='localhost', port='9000', user='default', database='default')
start = 1
res = create_clickhouse(client)
count = 0
rowcount = 0
rows = []
for i in range(0, 100000):
_amount = randint(0, 1000000)
_summa = randint(0, 1000000)
_name = ''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(100))
_row = []
_row.append(int(_amount))
_row.append(float(_summa))
_row.append(_name)
rows.append(_row)
date_start = datetime.now()
count = 0
while True:
res = insert_clickhouse(client, rows)
count += 1
if count == 100:
break
date_end = datetime.now()
_diff = (date_end - date_start).total_seconds()
print("Время вставки 10 миллионов записей - %s секунд" %_diff)
select_clickhouse()
Scala клиент для PostgreSQL:
import java.sql.{Connection, DriverManager, ResultSet}
import java.sql.PreparedStatement;
import java.sql.*;
import java.time.*;
import java.io.InputStream
import java.util.Properties;
import scala.collection.mutable.ArrayBuffer
class InsertToPostgres {
classOf[org.postgresql.Driver]
val con_str = "jdbc:postgresql://localhost:5432/postgres?user=postgres"
val conn = DriverManager.getConnection(con_str)
try {
val stmt: Statement = conn.createStatement();
val sql = "DROP TABLE if exists test;"
stmt.executeUpdate(sql);
val cstmt: Statement = conn.createStatement();
val sql2 = """
CREATE TABLE if not exists test
(
id bigserial,
amount int,
summa numeric(10,2),
name varchar(1000),
datetime timestamp default now()
) ;"""
cstmt.executeUpdate(sql2);
val t1 = java.time.LocalDateTime.now;
var count_batch = 0
while(count_batch < 1000) {
val ps: PreparedStatement = conn.prepareStatement("INSERT INTO test (amount, summa, name) VALUES (?,?,?)");
var count = 0
while(count < 10000) {
ps.setInt(1, 1);
ps.setFloat(2, 2);
ps.setString(3,"12345лофврадлфоыраорkasjdgflsajdhflaksjdhflkajsdhflkajsdhljdfsdfsdfsdfsdfsdfsdfs");
ps.addBatch();
count += 1
}
ps.executeBatch();
count_batch += 1
}
val t2 = java.time.LocalDateTime.now;
var duration = Duration.between(t1, t2);
System.out.printf("Время вставки 10 миллионов записей = %s секунд.%n", duration.getSeconds());
} finally {
conn.close()
}
}
class SelectFromPostgres {
classOf[org.postgresql.Driver]
val con_str = "jdbc:postgresql://localhost:5432/postgres?user=postgres"
val conn = DriverManager.getConnection(con_str)
try {
val stm = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)
val rs = stm.executeQuery("SELECT * from test")
val t1 = java.time.LocalDateTime.now;
var id = ArrayBuffer[Int]()
var amount = ArrayBuffer[Int]()
var summa = ArrayBuffer[Float]()
var name = ArrayBuffer[String]()
var datetime = ArrayBuffer[java.sql.Timestamp]()
while(rs.next) {
id += rs.getString("id").toInt
amount += rs.getString("amount").toInt
summa += rs.getString("summa").toFloat
name += rs.getString("name")
datetime += Timestamp.valueOf(rs.getString("datetime"))
}
val t2 = java.time.LocalDateTime.now;
var duration = Duration.between(t1, t2);
System.out.printf("Время выборки 10 миллионов записей = %s секунд.%n", duration.getSeconds());
} finally {
conn.close()
}
}
object pgconn extends App {
//val Insert = new InsertToPostgres()
val select = new SelectFromPostgres()
}