Create a tester for load testing of PostgreSQL

the Idea of this proektika (it "proektika") arose spontaneously. The company uses memory DB TimesTen contains a single large table with data more than 150 million records, and the volume is about 15 gigs. TimesTen always worked fine, the response for any request received within milliseconds, everyone is happy. One of these days, T10 was responding very long, the response time is increased to 3-5 seconds. Tehpotdergka of course started work on finding the problem, but in parallel we asked the question, why do T10 is used, why not move the base on a conventional RDBMS Oracle or Postgres ? needed to find out to carry out the appropriate tests. In the end, a little digging on the Internet frišnyh for testing was not. In the end of the day "on my knees" was written a small console utility which would have measured the response time of DBMS on different types of queries, collecting statistics, and in addition would also multithreaded, so load testing was the most objective.

For testing was selected the Postgres database, the database created a table with an appropriate structure, optimal indexes:
the

CREATE TABLE "public"."numbers" (
"contract" int8 NOT NULL,
"account" int8 NOT NULL,
"number" int8 NOT NULL,
"system_id" int2 NOT NULL,
"region_id" int2 NOT NULL,
"storage_id" int2 NOT NULL
);

CREATE INDEX "ix_contract" ON "public"."numbers" USING btree ("contract");
CREATE UNIQUE INDEX "ix_number" ON "public"."numbers" USING btree ("number");
CREATE INDEX "ix_account" ON "public"."numbers" USING btree ("account");

Filling in the table was done through the process (filling 100 million records amounted to 7 hours):
the

 CREATE OR REPLACE FUNCTION "public"."fill"(_count int8)
RETURNS "pg_catalog"."int8" AS $BODY$DECLARE
int8 i;
j int2;
_number_start int8;
_pa_start int8;
_pa int8;
_countract int8;
BEGIN
_number_start:=70000000000;
_pa_start:=1000000000000;
FOR i IN 0..5 LOOP BY _count
INSERT INTO number(contract,account,number,system_id,region_id,storage_id) VALUES
(round(random()*100000000000+100000000000),round(random()*100000000+_pa_start),_number_start+0+i,round(random()*10+1),round(random()*10+101),round(random()*5+1)),
(round(random()*100000000000+100000000000),round(random()*100000000+_pa_start),_number_start+1+i,round(random()*10+1),round(random()*10+101),round(random()*5+1)),
(round(random()*100000000000+100000000000),round(random()*100000000+_pa_start),_number_start+2+i,round(random()*10+1),round(random()*10+101),round(random()*5+1)),
(round(random()*100000000000+100000000000),round(random()*100000000+_pa_start),_number_start+3+i,round(random()*10+1),round(random()*10+101),round(random()*5+1)),
(round(random()*100000000000+100000000000),round(random()*100000000+_pa_start),_number_start+4+i,round(random()*10+1),round(random()*10+101),round(random()*5+1));
END LOOP;
RETURN 1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100
;

In parallel, was created a console application to emulate the load on a DBMS with different types of queries. To connect to PostgreSQL was used the project npgsql.
The text of the main C#program:
the

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Npgsql;
using System.Xml;
using System.IO;
using System.Threading;

namespace sqlPerformer
{
class Program
{
public static Configuration c;
public static string config_file = "config.xml";
//public static config config = new config();
public static Performance performance = new Performance();
public static List<Thread> threads = new List<Thread>();

static void Main(string[] args)
{
//saveConfig();
Console.WindowWidth = 120;
loadConfig();
for (int i = 1; i <= Program.c.threads; i++)
{
Thread thread = new Thread(Program.thread);
thread.Start(i);
threads.Add(thread);
}
//Program.thread();
Console.ReadLine();
}

public static void thread(object thread_id)
{
NpgsqlConnection cn = new NpgsqlConnection();
cn.ConnectionString = string.Format("Server={0}; Port={1}; Database={2}; User Id={3}; Password={4};", Program.c.host, Program.c.port, Program.c.database, Program.c.user, Program.c.password);
cn.Open();

for (int i = 0; i <= Program.c.count; i++)
{
foreach (query q in the Program.c.queries.query)
{
NpgsqlCommand cm = new NpgsqlCommand();
cm.Connection = cn;
cm.CommandText = q.text;
if (!q.status) continue;
foreach (parameter p in q.parameters.parameter)
{
NpgsqlTypes._temp_type NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
object _val = null;
switch (p.GetType().Name)
{
case "bigint":
_temp_type = NpgsqlTypes.NpgsqlDbType.Bigint;
_val = Program.getRandomInt64(((bigint)p).min ((bigint)p).max);
break;
case "integer":
_temp_type = NpgsqlTypes.NpgsqlDbType.Integer;
_val = Program.getRandomInt32(((integer)p).min, ((integer)p).max);
break;

_temp_type = NpgsqlTypes.NpgsqlDbType.Timestamp;
_val = Program.getRandomDate(((date)p).min ((date)p).max);
break;
case "string_line":
_temp_type = NpgsqlTypes.NpgsqlDbType.Varchar;
_val = Program.getRandomStringLine(((string_line)p).chars, ((string_line)p).max ((string_line)p).max);
break;
case "text":
_temp_type = NpgsqlTypes.NpgsqlDbType.Text;
_val = Program.getRandomText(((text)p).words, ((text)p).max ((text)p).max);
break;
}
cm.Parameters.Add(p.id _temp_type);
cm.Parameters[p.id].Value = _val;
}
Program.delay();
lock (Program.performance)
{ Program.performance.start(q.id thread_id.ToString()); }
cm.ExecuteNonQuery();
lock (Program.performance)
{ Program.performance.stop(); }
}
}
}

public static long getRandomInt64(long min, long max)
{ return Convert.ToInt64(Math.Round((new Random(unchecked((int)(DateTime.Now.Ticks)))).NextDouble() * (max - min) + min)); }

public static int getRandomInt32(int min, int max)
{ return Convert.ToInt32(Math.Round((new Random(unchecked((int)(DateTime.Now.Ticks)))).NextDouble() * (max - min) + min)); }

public static DateTime getRandomDate(DateTime min, DateTime max)
{
long stamp_min = min.Ticks;
long stamp_max = max.Ticks;
long stamp_new = Program.getRandomInt64(stamp_min, stamp_max);
return (new DateTime(stamp_new));
}

public static string getRandomStringLine(string chars, int min, int max)
{
string retval = "";
Random r = new Random(unchecked((int)(DateTime.Now.Ticks)));
for (int i = 1; i <= r.Next(min, max); i++)
retval += chars[r.Next(0, chars.Length - 1)];
return retval;
}

public static string getRandomText(List<string> words, int min, int max)
{
string retval = "";
Random r = new Random(unchecked((int)(DateTime.Now.Ticks)));
for (int i = 1; i <= r.Next(min, max); i++)
retval += "" + words[r.Next(0, words.Count - 1)];
return retval.Trim();
}

public static void delay()
{
if (Program.c.delay.status)
System.Threading.Thread.Sleep(Program.getRandomInt32(Program.c.delay.min, Program.c.delay.max));
}

static void saveConfig()
{
Program.c = new Configuration("localhost", 5432, "postgres", "postgres");
query q = new query("SELECT * FROM \"public\".\"table\"(?)");
q.parameters.parameter.Add(new bigint(100000000, 200000000));
c.queries.query.Add(q);
System.Xml.Serialization.XmlSerializer xs = new System.Xml.Serialization.XmlSerializer(c.GetType());
StreamWriter writer = File.CreateText(Program.config_file);
xs.Serialize(writer, c);
writer.Flush();
writer.Close();
}
static void loadConfig()
{
System.Xml.Serialization.XmlSerializer xs
= new System.Xml.Serialization.XmlSerializer(
typeof(Configuration));
StreamReader reader = File.OpenText(Program.config_file);
Program.c = (Configuration)xs.Deserialize(reader);
reader.Close();
}
}
}

Settings were used the XML format (or rather the serialization of the configuration object in XML):
the

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Serialization;

namespace sqlPerformer
{
[Serializable]
public class Configuration
{
[System.Xml.Serialization.XmlElement()]
public string host = "localhost";
[System.Xml.Serialization.XmlElement()]
public int port = 5432;
[System.Xml.Serialization.XmlElement()]
public string user = "postgres";
[System.Xml.Serialization.XmlElement()]
public string database = "postgres";
[System.Xml.Serialization.XmlElement()]
public string password = "postgres";
[System.Xml.Serialization.XmlElement()]
public queries queries = new queries();
[System.Xml.Serialization.XmlElement()]
public delay delay = new delay();
[System.Xml.Serialization.XmlElement()]
public int threads = 1;
[System.Xml.Serialization.XmlElement()]
public int count = 1;

public Configuration() { }
public Configuration(string host, int port, string user, string password)
{ this.host = host; this.port = port; this.user = user; this.password = password; }
}

[Serializable]
public class queries
{
[System.Xml.Serialization.XmlElement()]
public List<query> query = new List<query>();

public queries() { }
}

[Serializable]
public class query
{
[System.Xml.Serialization.XmlAttribute()]
public bool status = false;
[System.Xml.Serialization.XmlAttribute()]
public string id = "";
[System.Xml.Serialization.XmlElement()]
public string text = "";
[System.Xml.Serialization.XmlElement()]
public parameters parameters = new parameters();

public query() { }
public query(string text) { this.text = text; }
}

[Serializable]
public class parameters
{
[System.Xml.Serialization.XmlElement()]
public List<parameter> parameter = new List<parameter>();

public parameters() { }
}

[Serializable]
[System.Xml.Serialization.XmlInclude(typeof(bigint))]
[System.Xml.Serialization.XmlInclude(typeof(integer))]
[System.Xml.Serialization.XmlInclude(typeof(string_line))]
[System.Xml.Serialization.XmlInclude(typeof(text))]
[System.Xml.Serialization.XmlInclude(typeof(date))]
public abstract class parameter
{
[System.Xml.Serialization.XmlAttribute()]
public string id = "";

public parameter() { }
}

[Serializable]
public class bigint : parameter
{
[System.Xml.Serialization.XmlElement()]
public long min = 0;
[System.Xml.Serialization.XmlElement()]
public long max = 0;

public bigint() { }
public bigint(long min, long max)
{ this.min = min; this.max = max; }


[Serializable]
public class integer : parameter
{
[System.Xml.Serialization.XmlElement()]
public int min = 0;
[System.Xml.Serialization.XmlElement()]
public int max = 0;

public integer() { }
public integer(int min, int max)
{ this.min = min; this.max = max; }
}

[Serializable]
public class date : parameter
{
[System.Xml.Serialization.XmlElement()]
public DateTime min = DateTime.Now;
[System.Xml.Serialization.XmlElement()]
public DateTime max = DateTime.Now;

public date() { }
public date(DateTime min, DateTime max)
{ this.min = min; this.max = max; }
}

[Serializable]
public class string_line : parameter
{
[System.Xml.Serialization.XmlElement()]
public string chars = "qwertyuiopasdfghjklzxcvbnm";
[System.Xml.Serialization.XmlElement()]
public int min = 2;
[System.Xml.Serialization.XmlElement()]
public int max = 10;

public string_line() { }
public string_line(string chars, int min, int max)
{ this.chars = chars; this.min = min; this.max = max; }
}

[Serializable]
public class text : parameter
{
[System.Xml.Serialization.XmlElement()]
public List<string> words = new List<string>() { "word1", "word2" };
[System.Xml.Serialization.XmlElement()]
public int min = 2;
[System.Xml.Serialization.XmlElement()]
public int max = 10;

public text() { }
public text(List<string> words, int min, int max)
{ this.words = words; this.min = min; this.max = max; }
}

[Serializable]
public class delay
{
[System.Xml.Serialization.XmlAttribute()]
public bool status = false;
[System.Xml.Serialization.XmlElement()]
public int min = 10;
[System.Xml.Serialization.XmlElement()]
public int max = 100;
}

}

For measuring performance was created by the class "Performance":
the

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;

namespace sqlPerformer
{
class Performance
{
public Stopwatch sw = new Stopwatch();
public Dictionary<string, stat > stat = new Dictionary<string, stat > ();

public string current_id = "";

public void start(string id, string thread_id) {
this.sw.Reset();
this.current_id = thread_id + "-" + id;
if (!this.stat.ContainsKey(this.current_id))
{
this.stat.Add(this.current_id, new stat());
}
this.sw.Start();
}

public void stop() { this.sw.Stop(); addTick(); }

public void addTick()
{
this.stat[this.current_id].add(this.sw.ElapsedTicks);

Console.Clear();
foreach (KeyValuePair<string, stat> k in this.stat)
Console.WriteLine(string.Format("{0}\tCount: {1}\tAverage: {2:#0.00}\tMin: {3:#0.00}\tMax: {4:#0.00}\tTotal: {5:#}"
k.Key
k.Value.count
k.Value.timeAvg
k.Value.timeMin
k.Value.timeMax
k.Value.timeTotal
));
}

}
public class stat
{
public Int64 count = 0;
public double timeTotal = 0;
public double timeLast = 0;
public double timeMin = 9999999;
public double timeMax = 0;
public double timeAvg = 0;

public stat() { }
public void add(long ticks)
{
this.count++;
this.timeLast = ticks / 10000000.0;
this.timeTotal += this.timeLast;
this.timeAvg = this.timeTotal / this.count;
this.timeMin = Math.Min(this.timeLast, this.timeMin);
this.timeMax = Math.Max(this.timeLast, this.timeMax);
}
}
}

As the test polygons were used workstation with 2 gigs of RAM, Core2Duo % and the brake rotor. Surprising were the results for 50 flows, the average answer was around 8-10, up to a maximum of 30 milliseconds, but the disk is heavily loaded. It is clear that serious equipment these values will be much lower. But pleased with the other when working with a large table, the DBMS responds quite adequately.
In principle, nothing prevents to use this tool for load testing of any database with any data set and any number of tables.

Article based on information from habrahabr.ru