CODEWITHSUNDEEP
mysqlsqlperformancestress-testing
Ilya Gazman
Ilya Gazman

Reputation: 32231

MySQL Insert Performance stressTool

*if you are to lazy to read the description you can find my question in the end...

Hi, I build a simple database:

CREATE TABLE `users` (
  `id` varchar(45) NOT NULL,
  `full_name` varchar(45) NOT NULL,
  `first_name` varchar(45) NOT NULL,
  `last_nmae` varchar(45) NOT NULL,
  `login` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  PRIMARY KEY (`id`,`full_name`,`login`),
  UNIQUE KEY `idusers_UNIQUE` (`id`),
  UNIQUE KEY `login_UNIQUE` (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

Now I am testing the performance of Inserting a single row.

I created a stress tool with C# and run it to 1M rows. This is the code if you are interested:

abstract class Job
    {
        private static object synObject = new object();
        private static int _id = 0;
        private static DateTime startingTime = DateTime.Now;
        private const int JOB_PER_SIRCLE = 10;

        protected int id;
        public Job()
        {
            lock (synObject)
            {
                _id++;
                id = _id;
            }
        }

        public void run()
        {
            while (true)
            {
                for (int i = 0; i < JOB_PER_SIRCLE; i++)
                {
                    doJob();
                }
                lock (synObject)
                {
                    calculate();
                }
            }
        }

        private static DateTime lastLog = DateTime.Now;
        private static long numOfJobsAcomplished = 0;
        private static long totalNumOfJobsAcomplished = 0;

        private static void calculate()
        {
            totalNumOfJobsAcomplished += JOB_PER_SIRCLE;
            numOfJobsAcomplished += JOB_PER_SIRCLE;
            DateTime now = DateTime.Now;
            TimeSpan timePass = now - lastLog;

            if (timePass.TotalSeconds > 1)
            {
                double total = 1000000;
                TimeSpan speed = TimeSpan.FromMilliseconds(timePass.TotalMilliseconds / numOfJobsAcomplished * total);
                Console.WriteLine("Speed = " + String.Format("{0:00.0000}", speed.TotalMinutes) + " Completed " + String.Format("{0:00.000}", totalNumOfJobsAcomplished / total * 100) + "% time pass " + (now - startingTime));
                lastLog = now;
                numOfJobsAcomplished = 0;
            }
        }

        protected abstract void doJob();
    }

In the doJob() method I am doing the insert and I am running 16 Job's, 16 threads(I found that it is the best performance on my machine)

Anyway my question is about the result, I am getting between 85 to 105 minutes to insert 1,000,000 rows. Is this fast or should I look for different database to work with?

P.S * When I am inserting I also hashing with MD5Crypt algorithm

Upvotes: 1

Views: 954

Answers (2)

Assaf Karmon
Assaf Karmon

Reputation: 923

If you want to optimize your insert performance may I suggest that you change your primary key to synthetic auto increment bigint. The reason is that Innodb is using a clustered index on the primary key which stores keys by range. If you use a non sequential primary key you'll need to read a memory block and wrote it back for each row in the worst case instead of using the cache as you will with a sequential key. When you insert sequential keys the performance is increased by orders of magnitude, try it out. 

CREATE TABLE `users` (
  `id` bigint(20) auto increment not null PRIMARY KEY,
  `user_id` varchar(45) NOT NULL,
  `full_name` varchar(45) NOT NULL,
  `first_name` varchar(45) NOT NULL,
  `last_nmae` varchar(45) NOT NULL,
  `login` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  UNIQUE KEY (`user_id`,`full_name`,`login`),
  UNIQUE KEY `idusers_UNIQUE` (`user_id`),
  UNIQUE KEY `login_UNIQUE` (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

btw, I'm not sure what id(user_id) was before so I left in, but you could take it out if you where using it as synthetic primary key.

Upvotes: 0

N.B.
N.B.

Reputation: 14071

I always find it funny when someone tries to "blame" write speed on software without taking other factors in consideration. So, let's start.

MySQL has 2 engines that are widely used. MyISAM and InnoDB. InnoDB is a transactional engine that uses clustered primary key to write records down to the hdd. That means that it does all kinds of calculations in order to write the data to the disk safely and to have sequential records written next to each other. That means using primary key lookups is fast, but it takes a while to write down.

To translate this into plain English, that means that your computer will work slightly harder to physically write the data to the disk safely and in such a way that it can find the records fast.

That also means that the bottleneck in this whole ordeal is your hard drive. 85 minutes to insert 1mil. records is roughly 200 inserts per second. That's a pretty nice figure for a regular 7200 RPM mechanical drive (my drive can reach 350ish IOPS). So out of 350 IOPS (let's take my drive), you achieved 200 which is a great figure seeing that your HDD is used to read and write and you probably have several services ran by your OS that require HDD I/O.

TL;DR - if you use different software, writing can be slightly faster at the start. You need to take all the factors into account before swapping software.

Upvotes: 1

Related Questions