//--------------------------------------------------------------------------------------------------------------------------------------------------------------
public static void RunMySQLSearchingTest()
{
//-----1----- MySQL
Logger.LogSubHeading("Now testing the MySQL to get a benchmark ...");
DateTime MySQLTest = DateTime.Now;
if (TestParameters.UseThreading == false)
{
PerformanceTestMySQLThreadBlock(TestParameters.NumSearchPatterns, 0);
}
else
{
//bool success = DNThreadManager.Run("RedisRead", NumIterations, 2500, new Action<int, int>( PerformanceTestRedisRead));
bool success = DNThreadManager.Run("MySQLRead", TestParameters.NumSearchPatterns, PerformanceTestMySQLThreadBlock);
}
TimeSpan MySQLTested = DateTime.Now.Subtract(MySQLTest);
Logger.Log("");
Logger.Log("Time to process:" + MySQLTested.TotalSeconds);
Logger.Log("Found " + IDs.Count + " objects matching one or more of the search patterns ...");
Logger.Log("MySQL read testing completed");
// clean up the static variables to minimise memory usage
IDs = new HashSet <uint>();
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------------
public static void PerformanceTestMySQLRead()
{
Logger.LogSubHeading("Testing MySQL Threaded!");
bool success = false;
Logger.Log("Now starting to test the patterns");
DateTime readStart = DateTime.Now;
if (TestParameters.UseThreading == false)
{
PerformanceTestMySQLThreadBlock(TestParameters.NumSearchPatterns, 0);
}
else
{
//bool success = DNThreadManager.Run("RedisRead", NumIterations, 2500, new Action<int, int>( PerformanceTestRedisRead));
success = DNThreadManager.Run("MySQLRead", TestParameters.NumSearchPatterns, PerformanceTestMySQLThreadBlock);
}
Logger.Log("Found " + IDs.Count + " objects matching one or more of the search patterns ...");
TestParameters.ReadMySQL = DateTime.Now.Subtract(readStart);
Logger.Log("MySQL read testing completed");
// clean up the static variables to minimise memory usage
IDs = new HashSet <uint>();
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// This is fast because it is non-blocking ....!
/// </summary>
/// <param name="chunkSize"></param>
/// <param name="startIndex"></param>
public static void PerformanceTestRedisCSharpThreadBlock(int chunkSize, int startIndex)
{
Logger.Log("Starting chunk with index " + startIndex + " and size " + chunkSize + " ...");
StringBuilder log = new StringBuilder();
StringBuilder queryTxt = new StringBuilder();
int searchIndex = startIndex;
for (int i = startIndex; i < (startIndex + chunkSize); i++)
{
List <DatabaseSearchPattern> rsps = TestParameters.PatternsToSearchFor[i];
// Scan for the objects ...
List <TestObj> rtos = rw.ScanObjects(TestObj.ObjectName, TestParameters.AllObjects, rsps, true);
// Now append the ids to the global list ...
foreach (TestObj rto in rtos)
{
if (IDs.Contains(rw.ConvertID(rto.ID)) == false)
{
lock ( IDs) {
IDs.Add(rw.ConvertID(rto.ID));
}
}
}
// Do the printing of all the specific queries only if DoDebug is set to true ....
if (RedisWrapper.DoDebug == true)
{
queryTxt.Clear();
foreach (DatabaseSearchPattern rsp in rsps)
{
queryTxt.Append(" " + rsp.AsText());
}
log.Append("\n" + "Search " + i + " found " + rtos.Count + " objects. Query Text: " + queryTxt);
}
// log.Append("\n" + "Search " + (searchIndex++) + " found " + rtos.Count + " objects. Query Text: " + queryTxt);
if (TestParameters.UseThreading == true)
{
DNThreadManager.IncrementNumIterationsCompleted();
}
else
{
Logger.Log(i, 100, chunkSize);
}
}
Logger.Log("");
Logger.Log(log.ToString());
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------------
public static void PerformanceTestRedisCSharp()
{
bool success = false;
Logger.LogSubHeading("Testing Redis with C Sharp checks!");
Logger.Log("Reading data (including loading all the object into memory in the first place!)");
DateTime readStart = DateTime.Now;
// Read the data from the DB ...
Logger.Log("Getting all the objects...");
List <object> objs = rw.GetAllObjects(new TestObj());
TestParameters.AllObjects = TestObj.ParseList(objs);
Logger.Log("Extracted " + TestParameters.AllObjects.Count + " objects...");
IDs = new HashSet <uint>();
// And now do the pattern matching - no need to set a specific chunk size for these ones ...
Logger.Log("Starting threaded processing ...");
if (TestParameters.UseThreading == true)
{
success = DNThreadManager.Run("RedisCSharp", TestParameters.PatternsToSearchFor.Count, PerformanceTestRedisCSharpThreadBlock);
}
else
{
PerformanceTestRedisCSharpThreadBlock(TestParameters.PatternsToSearchFor.Count, 0);
}
Logger.Log("Found " + IDs.Count + " objects matching one or more of the search patterns ...");
TestParameters.ReadRedisCSharp = DateTime.Now.Subtract(readStart);
Logger.Log("Redis with CSharp tests completed");
// clean up the static variables to minimise memory usage
IDs = new HashSet <uint>();
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// So this loads the full object for each search match found ... but does not persist those as that would likely kill the memory allocation
/// </summary>
/// <param name="chunkSize"></param>
/// <param name="startIndex"></param>
public static void PerformanceTestMySQLThreadBlock(int chunkSize, int startIndex)
{
Logger.LogSubHeading("Testing MySQL!");
//bool success = false;
int i = startIndex;
if ((startIndex + chunkSize) >= TestParameters.PatternsToSearchFor.Count)
{
chunkSize = TestParameters.PatternsToSearchFor.Count - startIndex;
}
Logger.Log("Starting chunk with index " + startIndex + " and size " + chunkSize + " ...");
// HashSet<ulong> ids = new HashSet<ulong>();
// Now lets connect to the MySQL db
Logger.Log("Connecting to the database...");
DatabaseWrapper dbInfo = new DatabaseWrapper(new DatabaseConnectionInfo("localhost", "mysql", "redis_comparison",
SecureStringWrapper.Encrypt("forum"), SecureStringWrapper.Encrypt(TestParameters.RedisAuth), 3306));
dbInfo.Connect();
Logger.Log("Reading data");
StringBuilder log = new StringBuilder();
StringBuilder queryTxt = new StringBuilder();
//long searchIndex = startIndex;
for (i = startIndex; i < (startIndex + chunkSize); i++)
{
List <DatabaseSearchPattern> rsps = TestParameters.PatternsToSearchFor[i];
/////////////
//bool storeQuery = false;
StringBuilder sql = new StringBuilder();
StringBuilder searchParams = new StringBuilder();
// And build the query here ...
sql.Append("SELECT ID, TestInt, TestLong, TestDouble, TestBool, TestStr, TestDT FROM " + TestObj.ObjectName + " WHERE ");
foreach (DatabaseSearchPattern rsp in rsps)
{
if (searchParams.Length > 0)
{
searchParams.Append(" AND ");
}
if (rsp.SearchType == SearchType.PrimaryKey)
{
searchParams.Append(" " + rsp.Parameter + "=" + rsp.Score);
}
else if (rsp.SearchType == SearchType.Text)
{
searchParams.Append(" " + rsp.Parameter + " LIKE " + DataUtilities.Quote(rsp.PatternStartsWith(RedisWrapper.TextIndexLength) + "%"));
//searchParams.Append(" " + rsp.Parameter + " LIKE " + DataUtilities.Quote(rsp.Pattern.Substring(0, 1) + "%"));
}
else if (rsp.SearchType == SearchType.Bool)
{
searchParams.Append(" " + rsp.Parameter + "=" + rsp.Score);
}
else if (rsp.SearchType == SearchType.DateTime)
{
// could also add switches on ==, >= and <=
if (rsp.SearchComparisonExpression == SearchComparisonExpression.Equivalent)
{
//if (rsp.ScoreMin == rsp.ScoreMax) { // an exact date and time
searchParams.Append(rsp.Parameter + " = "
+ DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(new DateTime(rsp.Score), true, true)));
}
else if (rsp.SearchComparisonExpression == SearchComparisonExpression.RangeLessThanOrEqualTo)
{
//} else if (rsp.ScoreMin == DateTime.MinValue.Ticks) {
// less than or equal to ...
// searchParams.Append(rsp.Parameter + " <= "
// + DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(new DateTime((long)Math.Round(rsp.ScoreMax)), true, true)));
// Note that we want to search inclusively. Which means we need to search less than the specified max day PLUS A DAY
// equivalent to <= 2016-04-20 23:59:59 to ensure that the day itself is included ....
//DateTime dt = new DateTime((long)Math.Round(rsp.ScoreMax));
DateTime dt = new DateTime((long)rsp.ScoreMax);
searchParams.Append(rsp.Parameter + " <= "
+ DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(dt, true, true)));
/////////////////////////////////////
//string checkTHIS = "";
}
else if (rsp.SearchComparisonExpression == SearchComparisonExpression.RangeGreaterThanOrEqualTo)
{
//} else if (rsp.ScoreMax == DateTime.MaxValue.Ticks) { // greater than or equal to ...
//DateTime dt = new DateTime((long)Math.Round(rsp.ScoreMin));
DateTime dt = new DateTime((long)rsp.ScoreMin);
searchParams.Append(rsp.Parameter + " >= "
+ DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(dt, true, true)));
/////////////////////////////////////
//string checkTHIS = rsp.AsText();
//checkTHIS = rsp.AsText();
//storeQuery = true;
}
else if (rsp.SearchComparisonExpression == SearchComparisonExpression.RangeBetween)
{
// } else { // Must be a real between
// Note that we want to search inclusively. Which means we need to search less than the specified max day PLUS A DAY
// equivalent to <= 2016-04-20 23:59:59 to ensure that the day itself is included ....
searchParams.Append(rsp.Parameter + " BETWEEN "
//+ DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(new DateTime((long)Math.Round(rsp.ScoreMin)), true, true))
+ DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(new DateTime((long)rsp.ScoreMin), true, true))
+ " AND "
//+ DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(new DateTime((long)Math.Round(rsp.ScoreMax)), true, true)));
+ DataUtilities.Quote(DateTimeInformation.FormatDatabaseDate(new DateTime((long)rsp.ScoreMax), true, true)));
}
else
{
string ohFuck = "";
}
}
else if (rsp.SearchType == SearchType.Score)
{
if (rsp.SearchComparisonExpression == SearchComparisonExpression.Equivalent)
{
//if (rsp.ScoreMin == rsp.ScoreMax) { // an exact value
searchParams.Append(rsp.Parameter + " = " + rsp.ScoreMin.ToString("0." + new string('#', 339)));
}
else if (rsp.SearchComparisonExpression == SearchComparisonExpression.RangeLessThanOrEqualTo)
{
//} else if (rsp.ScoreMin == Double.MinValue) { // less than or equal to ...
searchParams.Append(rsp.Parameter + " <= " + rsp.ScoreMax.ToString("0." + new string('#', 339)));
}
else if (rsp.SearchComparisonExpression == SearchComparisonExpression.RangeGreaterThanOrEqualTo)
{
//} else if (rsp.ScoreMax == Double.MaxValue) { // greater than or equal to ...
searchParams.Append(rsp.Parameter + " >= " + rsp.ScoreMin.ToString("0." + new string('#', 339)));
}
else if (rsp.SearchComparisonExpression == SearchComparisonExpression.RangeBetween)
{
//} else { // Must be a real between
searchParams.Append(rsp.Parameter + " BETWEEN "
+ rsp.ScoreMin.ToString("0." + new string('#', 339)) + " AND "
+ rsp.ScoreMax.ToString("0." + new string('#', 339)));
}
else
{
string ohFuck = "";
}
}
}
sql.Append(searchParams);
sql.Append(";");
List <string[]> sqlData = dbInfo.GetDataList(sql.ToString());
int objCount = 0;
if (sqlData != null)
{
objCount = sqlData.Count;
foreach (string[] row in sqlData)
{
ulong id = 0;
ulong.TryParse(row[0], out id);
int testInt = 0;
int.TryParse(row[1], out testInt);
long testLong = 0;
long.TryParse(row[2], out testLong);
double testDouble = 0;
double.TryParse(row[3], out testDouble);
bool testBool = (row[4] != null && row[4].Equals("1") == true) ? true : false;
// string is obvs good to go!!
DateTime testDT = DateTimeInformation.FormatDateTime(row[6]);
TestObj rto = new TestObj(id, testInt, testLong, testDouble, testBool, row[5], testDT);
// Keep adding the objects, as long as they have not already been added and the MaxNumObjects is not exceeded - probably 1m
// Now append the ids to the global list ...
lock (IDs) {
if (IDs.Contains((uint)rto.ID) == false)
{
IDs.Add((uint)rto.ID);
TestObjects.Add(rto);
}
}
}
}
// Do the printing of all the specific queries only if DoDebug is set to true ....
if (RedisWrapper.DoDebug == true)
{
// 19-Aug-2016 - F**k a duck - DO NOT SORT THE QUERIES HERE
// We need to make sure not to sort the master list as it potentially confuses the calculations in the search objects method ...
queryTxt.Clear();
foreach (DatabaseSearchPattern rsp in rsps)
{
queryTxt.Append(" " + rsp.AsText());
}
log.Append("\n" + "Search " + i + " found " + objCount + " objects. Query Text: " + queryTxt);
}
//foreach (RedisSearchPattern rsp in rsps) {
// queryTxt.Append(" " + rsp.AsText());
//}
////log.Append("\n" + "Search " + (++searchIndex) + " found " + objCount + " objects. Query Text: " + queryTxt);
//log.Append("\n" + "Search " + (++searchIndex) + " found " + objCount + " objects. Query Text: " + queryTxt + "\t" + sql);
////////////////////////////
//if (storeQuery == true && (sqlData == null || sqlData.Count == 0)) {
// log.Append("\t" + sql);
//}
if (TestParameters.UseThreading == true)
{
DNThreadManager.IncrementNumIterationsCompleted();
}
else
{
Logger.Log(i, 10, TestParameters.PatternsToSearchFor.Count);
}
}
Logger.Log("");
Logger.Log(log.ToString());
// And lastly, lets disconnect from the database!
dbInfo.Disconnect();
}
//--------------------------------------------------------------------------------------------------------------------------------------------------------------
/// <summary>
///
/// </summary>
public static void PerformanceTestRedisRead()
{
Logger.LogSubHeading("Testing Redis threaded reading!");
DateTime RedisThreadedStartTime = DateTime.Now;
//bool success = false;
NumCompleted = 0;
// Now lets load the numberic indices into virtual memory
//if (RedisWrapper.LoadIndicesIntoVM == true) {
// Logger.Log("Loading the indices into VM...");
// success = rw.LoadIndices("RedisTestObj", new List<string>() { "TestInt", "TestLong", "TestDouble" });
//}
Logger.Log("Now starting to test the patterns");
// 15-Aug-2016 - And lastly - lets make sure we can get all the indexes!!!
RedisWrapper.IndexInfo = rw.IndexFindAll(new TestObj());
/*
* 2-Aug-2016 - OK, we need to be cleverer with the chunk sizes. Redis is ONLY faster than MySQL when it 100% performs in RAM.
* As soon as the data size of the Redis database and the extraction code spills over our RAM limits and starts caching, it dramatically slows down.
* So when building our pipeline of queries we need to have an idea of how much data we are potentially throwing into memory.
*
* The problem comes when trying to parcel together lots of Redis queries to speed these up and utlise the pipeline. This means that instead
* of just one query pulling out e.g. IDs, we have all of the queries trying to load their results into memory at the same time!!
* To give an example - if we have 1,000 queries and a database with 1,000,000 objects in it
*
* Long is –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
* uLong is 0 to 18,446,744,073,709,551,615
*/
// Good to chunk this up!!
int chunkSize = DNThreadManager.CalculateOptimalChunkSize(TestParameters.NumSearchPatterns);
chunkSize = (chunkSize > 1000) ? 1000 : chunkSize;
// HACK IT!!!
chunkSize = 1000; // Good at 100000
//chunkSize = 100;
//chunkSize = 500; // good at 1m
//chunkSize = 400;
//chunkSize = 350;
// And catch piddly tests ...
if (chunkSize > TestParameters.NumSearchPatterns)
{
chunkSize = TestParameters.NumSearchPatterns;
}
// disable threading for now on this option!!!
//if (TestParameters.UseThreading == false) {
int startIndex = 0;
NumCompleted = 0;
IDs = new HashSet <uint>();
//PerformanceTestRedisReadThreadBlock(TestParameters.NumSearchPatterns, 0);
PerformanceTestRedisSearchThreadBlock(chunkSize, startIndex);
while (NumCompleted < TestParameters.NumSearchPatterns)
{
startIndex += chunkSize;
PerformanceTestRedisSearchThreadBlock(chunkSize, startIndex);
}
//} else {
//bool success = DNThreadManager.Run("RedisRead", NumIterations, 2500, new Action<int, int>( PerformanceTestRedisRead));
// and lets also make sure these chunks are a sensible size too
//success = DNThreadManager.Run("RedisRead", TestParameters.NumSearchPatterns, chunkSize, PerformanceTestRedisSearchThreadBlock);
// }
Logger.Log("Found " + IDs.Count + " objects matching one or more of the search patterns ...");
TestParameters.ReadRedisThreaded = DateTime.Now.Subtract(RedisThreadedStartTime);
// Then get the objects and note that we don't count this in the timings .......
PerformanceTestRedisGetObjects();
Logger.Log("Time to process:" + TestParameters.ReadRedisThreaded.TotalSeconds + ". ");
Logger.Log("Threaded testing completed");
}
