Mysql performance
I am testing MySQL as a replacement for SQL Server and I am running into something really weird. I am testing for both inserts and reads and am maxing out 50 requests per second anyway.
My test table looks like this:
DROP TABLE IF EXISTS `webanalytics`.`test`;
CREATE TABLE `webanalytics`.`test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;
And my C # test program looks like this:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Diagnostics;
namespace ConsoleApplication1
{
class Program
{
const int QUERY_COUNT = 1000;
const string CONNECTION_STRING = "server=localhost;database=WebAnalytics;uid=root;pwd=root";
static void Main(string[] args)
{
using (var db = new MySqlConnection(CONNECTION_STRING))
{
db.Open();
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "insert into Test(Name) values (?Name);";
cmd.Parameters.AddWithValue("?Name", "");
var timer = new Stopwatch();
timer.Start();
for (var i = 0; i < QUERY_COUNT; i++)
{
cmd.Parameters["?Name"].Value = "Test" + i;
cmd.ExecuteNonQuery();
}
timer.Stop();
var rate = QUERY_COUNT / (timer.ElapsedMilliseconds / 1000);
Console.WriteLine("Query rate: {0}/s", rate);
}
}
}
}
}
This seems to be a pretty basic test case. When installing for MySQL, I run the default 32-bit OLTP server standard setup, although I had to tweak the buffer pool for innodb down from 2G to 1G. I don't understand where the bottleneck is. Is the MySQL data connector buggy? The dottrace profile session shows the following:
alt text http://img18.imageshack.us/img18/6812/performance.png
I don't know the inner details of the MySQL connector, but the calls to mysqldatareader.nextresult are confusing me. Why does it read when I do an insert?
source to share
You are using InnoDB tables. Therefore, you must pay very close attention to the numerous settings that can affect the performance of your MySQL database. MySQLPerformanceBlog has a couple of really good articles on InnoDB Optimization that you should read.
source to share