Question Re: Query slow performance sys_databases.m_backup_catalog in Technology Q&A

Query slow performance sys_databases.m_backup_catalog

Former Member — Mon, 25 Jan 2016 21:34:18 GMT

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.

Re: Query slow performance sys_databases.m_backup_catalog

yakcinar — Mon, 25 Jan 2016 22:24:48 GMT

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&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

Re: Query slow performance sys_databases.m_backup_catalog

Former Member — Tue, 26 Jan 2016 07:05:51 GMT

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.

Re: Query slow performance sys_databases.m_backup_catalog

Former Member — Thu, 11 Feb 2016 22:28:08 GMT

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

Re: Query slow performance sys_databases.m_backup_catalog

Former Member — Fri, 12 Feb 2016 14:33:21 GMT

Thanks for this. We are getting our real hardware any day now so it will be interesting to see what happens.

After the original post we actually realized that after a restart of the system that particular query runs in milliseconds and gets progressively slower over time eventually reaching that 5-6 minutes time until we restart the Hana system again.

The basis team has said they are having other issues as well.

I will update this thread just for completeness after we get the real hardware and see what happens.

Thanks again.

Re: Query slow performance sys_databases.m_backup_catalog

Former Member — Fri, 12 Feb 2016 16:38:22 GMT

It took only 1.35 sec for me to fetch these details. Yes, I belive there should be some issue with the system. Hopefully, you will get the result in ms in your new hardware

Re: Query slow performance sys_databases.m_backup_catalog

Former Member — Fri, 12 Feb 2016 19:44:50 GMT

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