1

I'm seeing a slow down when doing load testing on my app. I have a query that takes about 350ms to run, but what I run it in parallel 8 times (not to mention 32 times), it goes up to 2.5 seconds.

I verified on a profiler that the execution is really what that is taking up the time.

the query:

SELECT SUM([_Facts_].[Sales]) [_measures___Sum Sales_], [_Date_].[year] [_Date___year_]
FROM [pp].[Facts] [_Facts_], [pp].[Date] [_Date_]
WHERE [_Facts_].[dateKey] = [_Date_].[dateKey]
GROUP BY [_Date_].[year]
ORDER BY [_Date_].[year] ASC

I'm running 8 parallel processes that make 10 calls in sequence. For 1 parallel I get:

360, 350, 345, 360, 365, 360, 395, 786, 395, 370, avg:408

For 8 parallel:

515, 1571, 1471, 1326, 1862, 2478, 1922, 3098, 2413, 2032, 2773, 3048, 2453, 2092, 2077, 3359, 2898, 2733, 3018, 2483, 1887, 3023, 3088, 3724, 2317, 2753, 2643, 3284, 3299, 2418, 1907, 1862, 2498, 2838, 2518, 3203, 2613, 2207, 3434, 2613, 3198, 2257, 2593, 2448, 2518, 2968, 2828, 2122, 2963, 2212, 3299, 2988, 3153, 2803, 2157, 2543, 2758, 2998, 2538, 2257, 2788, 2443, 2082, 2613, 3173, 4205, 2603, 2387, 1747, 3854, 3068, 2788,  2603, 3103, 2703, 3198, 1832, 1421, 2217, 1326

avg:

2535, 2523, 2571, 2627, 2689, 2469, 2521, 2610

When going to 16 in parallel it went up to even more.

I tried testing on MySql and got the same jump (different test times, but at least 4 times slower when parallel).

Can't these DBs handle the load?!

This happens both in C# and Java: C#:

class Program
{
    static void Main(string[] args)
    {
        Parallel.For(0, 8, i => run());
    }

    static void run()
    {
        using (var conn = new SqlConnection("Data Source=.;Initial Catalog=;User ID=;Password="))
        {
            conn.Open();

            var cnt = 10;

            long avg = 0;

            for (int i = 0; i < cnt; i++)
            {
                var sw = DateTime.Now.Ticks;

                using (var cmd = new SqlCommand("SET ARITHABORT ON", conn))
                {
                    cmd.CommandText =
                        "SELECT SUM([_Facts_].[Sales]) [_measures___Sum Sales_], [_Date_].[year] [_Date___year_]\n" +
                        "FROM [pp].[Facts] [_Facts_], [pp].[Date] [_Date_]\n" +
                        "WHERE [_Facts_].[dateKey] = [_Date_].[dateKey] \n" +
                        "GROUP BY [_Date_].[year]\n" +
                        "ORDER BY [_Date_].[year] ASC";

                    using (var reader = cmd.ExecuteReader())
                    {
                        var x = 0;
                        while (reader.Read())
                        {
                            x++;
                        }

                        reader.Close();
                    }

                }

                var dif = (DateTime.Now.Ticks - sw) / 1000;
                avg += dif;
                Console.WriteLine(dif);
            }

            conn.Close();

            Console.WriteLine("avg:" + avg / cnt);
        }
    }
}

Java:

@Test
public void asdf() throws SQLException {

   Runnable r = () -> {
      try (Connection conn = DriverManager.getConnection("jdbc:sqlserver://;databaseName=", "", "")) {

         long avg = 0;

         for (int i = 0; i < 10; i++) {

            try (Statement statement = conn.createStatement()) {
               StopWatch sw = new StopWatch();
               sw.start();

               ResultSet resultSet = statement.executeQuery("SELECT SUM([_Facts_].[Sales]) [_measures___Sum Sales_], [_Date_].[year] [_Date___year_]\n" +
                     "FROM [pp].[Facts] [_Facts_], [pp].[Date] [_Date_]\n" +
                     "WHERE [_Facts_].[dateKey] = [_Date_].[dateKey] \n" +
                     "GROUP BY [_Date_].[year]\n" +
                     "ORDER BY [_Date_].[year] ASC");

               int x = 0;
               while (resultSet.next()) {
                  x++;
               }

               sw.stop();
               avg+=sw.getTime();
               System.out.println(sw);
            }
         }

         System.out.println(avg/10);

      } catch (SQLException e) {
         e.printStackTrace();
      } finally {

      }
   };



   ExecutorService executor = Executors.newFixedThreadPool(8);
   for (int i = 0; i < 8; i++) {
      executor.execute(r);
   }


   try {
      executor.shutdown();
      executor.awaitTermination(12312313, TimeUnit.MINUTES);
   } catch (InterruptedException e) {
      e.printStackTrace();
   }
}

MORE INFO

The 2 tables are ~1,000 rows and ~420,000 rows. The join is ~420,000 rows and the result it 3 rows.

There is a PK on date.dateKey and FK on facts. These are tables, not views.

I checked with profiler to verify that the duration is the actual execution time of the query, and not the app's run-time.

The SqlServer has 16 cores, so I would hope for the 8 queries to run in parallel and not stack up.

On the server, CPU for 1 query goes up to 50%. for 8 it gets to 95%.

network/memory/IO doesn't seem to change dramatically.

plan

UPDATE

So i tried playing with the limitations of the parallelism. The conclusion is that the DB uses all cores for each query and the cores are overwhelmed by multiple calls. if i turn down the parallelism to 1 then each query is slower but i can run multiple queries without taking a hit.

guessing that it's just the hardware limitations and there is not "magic" solution for the general case - only tweaking the queries.

btw, it seems that the biggest part of the hit is the joining.

Thanks everyone!

  • 1
    Have you monitored the system resources when you run the query, like cpu, memory and IO. May be use perfmon and see which resources are getting used more when you bump up the load. Also how big is your table and what is the execution plan like? – jesijesi Feb 8 '17 at 9:40
  • 2
    Do you have any reason to expect anything else other than linear slow down? – David דודו Markovitz Feb 8 '17 at 10:16
  • 1
    @DuduMarkovitz: I think the questioner's expectation is that 8 calls would be run in parallel, so take the same amount of CPU time but spread over 8+ cores so the wall-clock time is much lower that base*8. – David Spillett Feb 8 '17 at 10:56
  • How is the load distributed between the cores? – David דודו Markovitz Feb 8 '17 at 11:16
  • they all jump to 90% together. – Imbar M. Feb 8 '17 at 11:17
2

If running your query multiple times in serial takes 50% overall of a 16 core CPU, that means it's going parallel internally (i.e. SQL Server is splitting up the work over multiple cores), and you can't expect linear gains by running it in parallel "externally" (or whatever the correct terminology would be...).

Try your tests again with "OPTION(MAXDOP 1)" added to the query (SQL Server only), which will ensure each run only uses one core, and I think you'll see what I mean.

  • That could make sense but does not fit well with the claim of the OP that a single query invokes a single core – David דודו Markovitz Feb 8 '17 at 18:37
2

If it is I/O-bound, parallelism won't help significantly. Once the I/O is fully saturated, you can't squeeze down the time any more.

Assuming it is not I/O-bound, let's discuss CPU. There is contention between threads -- usually in the form of locking common resources (caches, I/O requests, etc). This means that splitting any job N-ways will not give you a full N-fold improvement.

But there is a solution, probably 10-fold faster, probably works equally well in SQL Server and MySQL. Your query seems to be what I call a "report" against a Data Warehouse, correct? The best way to do that is by thinking ahead and building a "Summary Table". (In SQL Server, a 'materialized view' may be appropriate; in MySQL, you need custom code to do the something similar.) Then the query is so fast, run against the Summary table, that you don't need to parallelize it. For MySQL, I have this blog.

  • Why would you claim that "If it is I/O-bound, parallelism won't help significantly"? – David דודו Markovitz Feb 8 '17 at 19:10
  • There is (usually) only one path from disk to the processing. If, when single-threaded, it is consuming all the bandwidth from disk, why would multiple threads be able to run any faster? The same disk reads need to be done, although maybe in a different order. And they are still serialized (by the OS and/or disk controller). If you have RAID with striping (0/5/10/etc), you can get some parallelism in the disk reads. – Rick James Feb 8 '17 at 22:43
0

... query that takes about 350ms ... run it in parallel 8 times ... goes up to 2.5 seconds.

350ms * 8 = 2800ms = 2.8s

So your time complexity is roughly linear (slightly better than, infact, but that could just be statistical noise). Are you sure that your process is submitting the queries in parallel and not serial? Or that they aren't holding locks that make SQL Server serialise them even though they are submitted to it in parallel?

Are you in fact definitely using SQL Server as you've tagged the question with both it and mySQL? If you are getting the same undesired behaviour with both, that might point to the calling code not being as parallel as you expect.

In SQL Server you can use the profiler or extended events to monitor for statements starting and completing, the results of that will give useful clues. If they all start together but end in sequence then most likely something is making SQL Server serialise the queries, if one starts after the previous completes then the problem is more likely in the caller.

The query in your question looks pretty simple on face value so should be able to run concurrently quite happily, but if instead of simple tables Facts and/or Dates are complex views with some nasty mix of UDF calls and query planner hints there might be something that is forcing locks you don't expect. Perhaps copy in the query plan(s) and/or table/view definitions for people to look at to see if this is the case.

Also, I'm assuming that the queries are being served from memory and aren't scanning anything large, so you could expect each to run on a CPU core so they are are (given enough cores) run in parallel. If they are instead I/O bound because of table scanning due to bad indexing then I would expect at best linear time growth with attempts at parallelism, but your base time of 350ms would suggest this is not the case if you have a lot of data in one or both of the tables.

  • MySQL -- For a single connection, there is no case where MySQL will internally use more than one CPU (except for some background activities). – Rick James Feb 8 '17 at 23:04
0

MySQL optimizes single user throughput rather than concurrent behavior. MySQL index usage is not optimal also. It is a good database for very simple queries and moderate concurrency.

SQL server runs on Windows. In my experience Windows does not handle well multithreading. By our scalability results I believe windows have some sort of global locking on memory that generates contention. For our tests we used app that does processing on RAM and our results was similar to yours. More processes runs slower. We used a VM with 32 cores and 256gb RAM running 32 instances... It ran slower. Then we tested with 32 VMs with 1 core and 4gb ram each to check and it was 32 times faster.

If you want a database that handles concurrency well... I suggest PostgreSQL or Oracle. Emphasis on the first. In both cases, you must use Linux as OS. No database will run well on Windows.

You can also have a bottleneck on disk access. Check the OS logs. Multiprocessing will only scale up if you are processing on RAM. Run your app single thread... If you already are maxing the one core you can take no advantage of multithreading.

  • One does not simply give up on a market this size :-) techcrunch.com/2016/03/07/… – David דודו Markovitz Feb 8 '17 at 18:46
  • And it should not. Microsoft has created interesting things. It would be nice to see them get serious with their database engine. Maybe become competition with Oracle and PostgreSQL on bigdata. Even the threat of competition may push the market forward. I just hope this is not a marketing stunt. – Lucas Feb 8 '17 at 20:20
  • MySQL.. Before 5.1, 4 to 8 threads was the max; after that throughput would actually slow down due to the contention. Then Oracle poured significant effort into parallelism (in the CPU) in MySQL. Mutexes were split / eliminated / etc. Certain caches (buffer_pool, table) were split. The default is now to have the "Query cache" off; this had been a big contention problem on SELECTs. In the current version, benchmarks show no slowdown until after 48 threads. (And you trust benchmarks, right?) Still, things are better than Lucas's original statement. – Rick James Feb 8 '17 at 22:49
  • MySQL -- Separate connections can use separate CPU cores. In the extreme, you end up being I/O-bandwidth limited. I have tackled hundreds of "slow" mysql systems; almost none of them maxed out the CPU, except those that could be improved by query/index/schema improvements. My suggestion of Summary Tables is a significant one for any DW app on any vendor's RDBMS. – Rick James Feb 8 '17 at 22:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.