Dear Fabrice,

Have you had a chance to consider my previous email? Teng has made some further progress w/ MySQL and Qserv, for ingesting and indexing a ~100GB test table, using ‘myisamchk’ as follows:

-        Setting a larger sort_buffer_size produces significant performance improvements, up to around 16GB.

-        The choice of indexing mode (recover vs. safe-recover) is significant and, given we have plenty of spare disk space on our server, the ‘recover’ mode also produces good speed-up.

We’re also considering using multithreading (parallel-recover), though note this is classed as ‘Beta’, so wonder if that will lead to problems. Teng is best placed to explain further (and correct anything above that I may have misunderstood).

Thanks again,

George.

Cc: Teng.

Dear Fabrice,

I hope this email finds you well. I’d like to introduce you to a colleague of mine, Teng Li (cc’ed), who has recently joined the LSST:UK team here in Edinburgh.

Teng is working on an evaluation of Qserv, which has a couple of different angles. First, we are creating a suite of benchmark queries with which to test performance of Qserv for what will be crucial use cases for UK astronomers. Second, we are investigating the ingest mechanism for Qserv, given that we expect to need to quickly ingest large tables from LSST (and other surveys) during the Commissioning and Operations phases.

Teng has set up a couple of Qserv and MySQL instances into which he is ingesting copies of the UKIDSS and SDSS datasets. However, he has found the process to be very slow for the largest tables (especially indexing). A look through the LSST webpages led us to a report that you prepared some time ago on Qserv ingest/ indexing:

https://dev.lsstcorp.org/trac/wiki/mysqlLargeTablesAtIn2p3

and

https://dev.lsstcorp.org/trac/wiki/mysqlLargeTables.

Based on this, we have some ideas of how to improve ingest – e.g. looking at influence of sort_buffer_size and other indexing parameters. However, before we go too far with this investigation, we thought it worthwhile to check with you if there is any more up-to-date results, or if the situation has changed in the last four years.

Thanks in advance for any advice. I’ll leave it with Teng to answer any more technical questions you may have on our planned experiments, or our setup.

Speak to you soon,

George.

Cc: Teng.

--

Dr George Beckett,

University of Edinburgh

Email: [log in to unmask]

Tel: +44 131 651 3577 (EPCC) /

      +44 131 6688 255 (Astronomy)

Web: http://www.epcc.ed.ac.uk/

The University of Edinburgh is a charitable body, registered in

Scotland, with registration number SC005336.