In accordance with  DM-9175 - Getting issue details... STATUS  and  DM-9757 - Getting issue details... STATUS , JOIN queries between Object and Source tables for certain chunks can be incorrectly evaluated by the local mysql optimizer. This incorrect evaluation is seen in the form of executing the SELECT/INDEX clause of queries before the WHERE clause, leading to a much larger number of rows to have to be scanned and hence much longer than expected Obj-Src JOIN query times. The problem can generally be fixed by adding table statistics metadata to the local mysql instance. We do this in our case for just the Source table.

This problem was identified and fixed at CC-IN2P3 for the LSST20 dataset (used in F16 KPMs) on ccqserv125-149. Due to the deployment infrastructure used at that time (shmux), the process for generating and loading statistics in the appropriate tables will have to be modified for future data access centers. The general procedure underneath should remain the same. It should also be mentioned that this process should be modified and optimized since it currently uses the native mariadb container to generate then plug back in the statistics to the Qserv container.

At CC-IN2P3, the following commands were run with shmux (quotes may be an issue as displayed and as run) from the build node on all the cluster nodes from ccqserv125-149. On other clusters, permission issues may also come into play for executing these commands inside containers:

  1. Run 'mysql_upgrade' on all nodes, this generates the empty table statistics tables in the default mysql information_schema 

    shmux -Bm -S all -c "docker exec qserv bash -c '. stack/loadLSST.bash; setup mariadbclient; mysql_upgrade -u root -pchangeme -S run/var/lib/mysql/mysql.sock --skip-version-check' " <all_nodes>

  2. Generate list of all chunks that need to have statistics generated with appropriate options. This is done by executing a script (here) on all nodes that was hosted on the shared filesystem. The file this script generates should be available locally on each respective node. In addition, this script also pulls the latest mariadb container on all nodes

    shmux -Bm -S all -c "sudo -u qserv bash /sps/lsst/Qserv/vaikunth/script.sh" <all_nodes>

    Note: As an alternative, this step may be done by scanning the "INFORMATION_SCHEMA" tables by executing a query similar to the following on every worker node. This would also produce a similar list of all requisite statements

    SELECT CONCAT("ANALYSE TABLE ",TABLE_SCHEMA,".",TABLE_NAME)
  FROM information_schema.tables
  WHERE TABLE_SCHEMA="LSST" AND TABLE_NAME LIKE "Source\_%";

  3. Thereafter, stop all Qserv containers using whatever mechanism (shmux/Kubernetes) is typical of the setup

  4. Run the mariadb container with the right data volumes and the file from step 2 mounted, with some other options (on only the worker nodes)

    shmux -Bm -S all -c "docker run -d --volume /qserv/data/mysql/:/var/lib/mysql --volume /qserv/analyze.sql:/tmp/analyze.sql -e MYSQL_ROOT_PASSWORD=changeme -p 3306:3306 --name maria mariadb:latest" <worker_nodes>

  5. Execute ANALYZE TABLE on all the workers with chunks that have been generated in the file from step 2

    shmux -Bm -S all -c "docker exec -d maria bash -c 'mysql -u root -pchangeme LSST < /tmp/analyze.sql'" <worker_nodes>

  6. Check status on all nodes while ANALYZE TABLE is running on each chunk sequentially. This process can take many days depending on the size of the data (3 days in this instance)

    shmux -Bm -S all -c "docker exec maria mysql -u root -pchangeme -e 'SHOW PROCESSLIST;'" <worker_nodes>

  7. When the process finishes, permissions on the data directory must be restored back to the qserv container user. In this case the UID of the qserv account was 1000, and may be different for other setups. Check for the qserv UID on any host machine and use that in the following command

    shmux -Bm -S all -c "docker exec maria chown -R 1000:1000 /var/lib/mysql/" <worker_nodes>

  8. Before restarting the Qserv container, stop the mariadb containers and create a new or move the default mysql transaction log or it may cause issues with startup

    shmux -Bm -S all -c "sudo -u qserv mv /qserv/data/mysql/tc.log /qserv/tc_backup.log" <worker_nodes>

  9. After the previous steps, restarting all Qserv containers as normal with the newly updated data directory containing statistics should solve the issue with JOIN queries as discussed in this document

  • No labels