Creating a PostgreSQL Load Tester

The idea of ​​this project (namely “project”) arose spontaneously. The company uses memory-DB TimesTen, contains one big table with data, more than 150 million records, and a volume of about 15 gigs. TimesTen always worked properly, the answer for any request received in milliseconds, it suited everyone. One day, the T10 began to respond to requests for a very long time, the response time increased to 3-5 seconds. TechPod course began to work on finding a problem, but at the same time we wondered why T10 was used at all, why can't the database be transferred to a regular Oracle or Postgres RDBMS? It was necessary to find out to conduct the appropriate tests. As a result, having rummaged a little on the Internet, the necessary free software for testing was not found. As a result, a small console utility was written on the “knees” day that would measure the response time from the DBMS for different types of queries, collect statistics, and in addition it would also be multi-threaded so that the load tests were the most objective.

The Postgres DBMS was selected for testing, a table with the appropriate structure was created in the database, and the optimal indices were built:

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"); 

The filling of the table was done through a procedure (the time to fill out 100 million records was 7 hours):

  CREATE OR REPLACE FUNCTION "public"."fill"(_count int8) RETURNS "pg_catalog"."int8" AS $BODY$DECLARE i int8; j int2; _number_start int8; _pa_start int8; _pa int8; _countract int8; BEGIN _number_start:=70000000000; _pa_start:=1000000000000; FOR i IN 0.._count BY 5 LOOP 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, a console application was created to emulate the load on the DBMS with various types of queries. To connect to PostgreSQL, the npgsql project was used.
The text of the main C # program:

 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 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.NpgsqlDbType _temp_type = 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; case "date": _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(); } } } 

The XML format was used for the settings (more precisely, the serialization of the configuration object in XML)

 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 performance measurement the class “Performance” was created:

 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 a test site, a workstation with 2 gigs of RAM, Core2Duo process and a brake screw was used. The results were surprising, on 50 threads the average response was around 8-10, up to a maximum of 30 milliseconds, but the disk is heavily loaded. It is clear that on serious equipment these values ​​will be much lower. But I was pleased with something else - when working with a large table, the DBMS responds fairly adequately.
In principle, nothing prevents you from using this program for load testing any database, with any data set and any number of tables.