wiki:db/tests/MySql/MergeEngine2
Last modified 5 years ago Last modified on 10/21/2013 02:25:28 PM

Merge Engine - scaling to 1K tables

Related pages:

Test Motivation

We might use merge engine to manage subchunks in Qserv. We estimate the number of subchunks per chunk will not exceed few hundred, thus a test with ~1K tables should be sufficient. Since there might be limits ~1024, we tested with 1350 tables.

Executive summary

  • merge engine always opens all underlying tables - that can potentially lead to many open files on mysqld
  • queries such as near neighbor incurred ~11 sec overhead when run on merged table, comparing to running similar query directly on the chunk table.

Details below.

Test description

The test described below was run on lsst-dbdev5 at ncsa, on 1350 tables, each table had 1,000 rows. The tables were named o0001, o0002, ... o1350, and the merge table was called merged.

The entire data set (~1.5GB) easily fit in memory, and we didn't attempt to flush any caches, thus data was likely read from memory.

open_files_limit

It is worth noting that the merge engine opens all underlying tables. Initial test of "select count(*) from merged" failed with "Can't open file: './test/o0510.frm' (errno: 24)", increasing the limit from 1K to 2K, resulted in similar error, but on table " o1022.frm". After setting the limit to 4K the query completed in 0.69 sec. This is problematic, because we don't want mysqld to open excessive number of files.

Fetching single row through index

SELECT ra_PS, decl_PS 
FROM   merged 
WHERE  objectId = <id>

takes 0.68 sec.

Full table index scan

SELECT COUNT(*) 
FROM   merged
WHERE  chunkId = 4567

takes 0.54 sec. This query will need to touch every index file to complete.

Near neighbor

First, we measure join speed within a single chunk:

SELECT COUNT(*) 
FROM   o1319 AS o1, 
       o1319 AS o2
WHERE  scisql_angSep(o1.ra_PS, o1.decl_PS,  o2.ra_PS, o2.decl_PS) < 0.1

It takes 0.41 sec.

A near neighbor query on merged table:

SELECT COUNT(*) 
FROM   merged AS o1, 
       merged AS o2
WHERE  o1.chunkId = 1319
AND    o2.chunkId = 1319
AND    scisql_angSep(o1.ra_PS, o1.decl_PS,  o2.ra_PS, o2.decl_PS) < 0.1

takes 11.44 sec. (Note that we do have an index on "chunkId" column, although it is rather "boring", as all rows in a given table have the same value).

Appendix (how we generated data)

1K tables generated using script https://dev.lsstcorp.org/cgit/contrib/dbutils.git/tree/createManyTables.py

followed by running "create table merged <schema> ENGINE MERGE UNION(o0001, o0002, ..., o01350);