Skip to Content
avatar image
Former Member

Query slow performance sys_databases.m_backup_catalog

We are in the process of deploying Hana and have been playing around with a couple of VM's just to get familiar with HANA before we get our real hardware in a few weeks. I realize that the VM does not meet specs etc but I am not sure this is related to that.

The VM I am working on is a multi-tenant install with 3 tenants. Version 1.00.110 on Suse Linux 3.0.101. The tenants are essentially empty other than the few tables we have created.

Noticed something today that I was not expecting when running a query against sys_databases.m_backup_catalog. The actual query I ran is more complex than the one below but the one below shows the issue. It is taking 7 minutes to query sys_databases.m_backup_catalog.

If I query the tenant's m_backup_catalog directly it runs in < 1 second.

Is that expected behavior? I understand it would be slower because of the combined nature of the sys_databases

view but 7 minutes seems excessive. I have not spent a lot of time looking at the explain plan or anything yet

was just curious if this was to be expected. Part of what lead to this was that using the backup and recovery console

from SYSTEMDB to look at one of the tenants exhibits the same behavior where it takes many minutes for the gui to refresh.

The commands below were run consecutively and used the time command just to show the elapsed time.

more ./count1.sql

  select count(*) from sys_databases.m_backup_catalog

  where database_name = 'SCOTT';

time hdbsql -d systemdb -u system -p XXXXXXXXXXXXXX -I ./count1.sql -o /tmp/count1.out

  real    7m48.532s

  user    0m0.000s

  sys     0m0.008s

more /tmp/count1.out

  COUNT(*)

  895

more ./count2.sql

  select count(*) from m_backup_catalog;

time hdbsql -d scott -u system -p XXXXXXXXXXXXX -I ./count2.sql -o /tmp/count2.out

  real    0m0.055s

  user    0m0.004s

  sys     0m0.008s

more /tmp/count2.out

COUNT(*)

895

Repeating the first query results in slightly better time of 5 minutes but still a lot slower than the one directly in the tenant.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Jan 25, 2016 at 10:24 PM

    Hello Scott,

    7m is high value for HANA system.

    I think your hw is certified hw by SAP. Check your hw from below list first.

    Certified SAP HANA&amp;reg; Hardware Directory

    Then you can run hwcct python script for HW check like below picture to see the result.

    python HanaHwCheck.py

    If these two are okey then I would check performance tab of the HANA Studio for the execution of this sql script. I could open SQL trace also.

    Regards,

    Yuksel AKCINAR

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 26, 2016 at 07:05 AM

    The long runtime is weird and I don't have a good guess at hand that could explain it. Instead I would recommend you to check the thread activities of the time when the query runs. You can e.g. evaluate it historically using SQL: "HANA_Threads_ThreadSamples_FilterAndAggregation" available via SAP Note 1969700.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 11, 2016 at 10:28 PM

    Hi Scott,

    I have tested similar queries based on your example on our system that is SAP Certified and we have more than 15 tenants on it

    My queries fetch more than 10,000 rows and it takes 100 ms-150 ms so it is definitely not an issue in the way the data is being queried, some thing very unique with your setup

    Essentially SYS_DATABASES is a schema in the SYSTEMDB tenant so this query is definitely not trying to access the tenants underneath so it is not expected to take any more than a few 100 milli seconds

    Hope it helps

    Sunil

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Looks like this might actually turn out to be an issue with the version we were on. The VM's that were built we used an early version SPS 11.  Basis guys patched yesterday afternoon and so far no change in performance.

      I will have to get the patch details but will update this thread with that information if it solves the problem.

      Thanks for all the responses