mysql performance

I'm testing MySQL as a replacement for SQL server and I'm running into something really strange. I'm testing both inserts and reads, and maxing out around 50 queries per second either way.

My test table looks like:

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:

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

}

Seems like a rather simple test case. On the install for MySQL, I'm running 32bit with default OLTP standard server settings, though I had to adjust the buffer pool for innodb down from 2G to 1G. I don't get where the bottleneck is. Is the MySQL data connector buggy? A dottrace profile session reveals the following:

alt text http://img18.imageshack.us/img18/6812/performance.png

I don't know the inner details of the MySQL co开发者_运维问答nnector, but the calls to mysqldatareader.nextresult confuse me. Why is it reading when I'm executing an insert?

---

You are using InnoDB tables. Therefore you should pay very careful attention to the numerous settings which can make or break performance on your MySQL database. MySQLPerformanceBlog has a couple of really good articles on InnoDB optimization that you should read.

---

If you want to replace SQL Server, I hope you don't use CTE's or others useful SQL features MySQL don't have...