scaling performance vs number of CPUs

[Scott Koranda]

Hello,

I have two large tables across which I need to do 25 queries.

Each table contains approximately 25 million rows. Each table is in a seperate database but both are InnoDB tables, and I have InnoDB configured to use a single file on disk. The tables are well indexed and I have a good amount of RAM so I configured

innodb_buffer_pool_size = 6290M

A typical query looks like this:

SELECT lfn.name FROM
LSCmetadata lfn
LEFT JOIN lrc1000.t_lfn t_lfn ON t_lfn.name = lfn.name
WHERE t_lfn.name IS NULL AND
lfn.gpsStart > 832371121 AND
lfn.gpsStart < 834790321 AND
lfn.site = 'H' AND
lfn.runTag = 'S5' AND
lfn.frameType = 'RDS_R_L3'
ORDER BY lfn.gpsStart ASC LIMIT 1000;

The other 24 queries are similar but with changes to constraints for columns site, runTag, frameType, and gpsStart.

I can post the table schemas if it would be instructive. Note that I have chosen the column types carefully and they are indexed appropriately.

I am happy with the performance of the 25 queries when I run them one after another. It takes on average 93 seconds to complete all 25 of the queries when they are run in serial one after another. During the queries a single CPU is kept pegged at 100% utilization, indicating to me that I am not suffering from any I/O issues.

I then split up the queries into two sets (13 and 12) and ran them in parallel. When running the queries in parallel two processors are kept pegged at 100% and the total time drops to approximately 70 seconds, which I am also happy about.

Next I split up the queries into three sets and ran them in parallel but to my surprise the total time now increased to 111 seconds. This time 3 CPUs were kept pegged at 100%.

With 4 CPUs the total time went up to 150 seconds.

I am surprised that the total time for the queries did not scale with the number of threads/processors.

Was my expectation that running more threads in parallel would help descrease the total time for the 25 queries unrealistic?

I would appreciate any insights people have. Please let me know what other information I might post to engage folks.

Thanks,

Scott