Performance issues post-upgrade from 7.6 to 7.7

[Former Member]

Hi MaxDB experts,

We have a problem with following query post upgrade from MaxDB version 7.6.03.15 to 7.7.06.09. The total number of rows in the table are 21546647, the rows in the selected time range are 70341.

Here's how the query looks -

select md.managedDeviceDns as ManagedDevice,
        avg(wmem.mem_total_kb)/1024 as TotalMemory 
                from Table1 wmem, Table2 mo, Table3 co, Table4 md
                        where wmem.measuredobjectid = mo.measuredobjectid
                        and mo.collectionid = co.collectionid and co.manageddeviceid = md.manageddeviceid
                        and md.managedDeviceDns in (List of Dns Names goes in here) 
                        and wmem.sampletime between '2009-08-18 00:00:00' and '2009-08-18 23:59:59'
                group by md.manageddeviceDns
                order by UsedPct desc

The explain command produced a cost of 7466 with 7.6. The query runs in just over 2 minute with 7.6. Then we upgraded to MaxDB 7.7, upgrade completed smoothly.

After the upgrade we re-ran the "explain" command with the same query. Post upgrade the cost value jumped multi-fold to 2091129. There was no data change in the underlying table between the 2 runs (7.6 and 7.7). We then ran the query against the now upgraded 7.7 database and waited for it to finish - it had not completed execution after 30 minutes at which point we interrupted the query.

We got same behavior with other similar queries that ran on other tables. I would like to request help for the above described scenario.

- What are we missing? Why the behavior after the upgrade is not comparable to 7.6?

- Do we need to set any database options after the upgrade finishes?

- Do we need to run any scripts/procedures post upgrade so that the optimizer behaves when it is given the query to run?

- Would the behavior be different with the same query, if this was a clean install of 7.7 and if the database had grown organically as 7.7?

Any help/ideas/things to try would be appreciated. Please let us know if you need additional details. Thanks for your time.

Sameer Apte

Edited by: samapt0100 on Aug 21, 2009 2:07 PM

[markus_doehr2]

Did you run an update statistics after the upgrade?

You have the possibility of posting the two explains, one from 7.6 and the other from 7.7?

Markus

[Former Member]

Hi Markus,

Thank you for your quick response. I 've now run the "Update Statistics" post upgrade. It took about 1 hour 7 minutes to complete and did not change the behavior in that the queries still run slowly with MaxDB 7.7.

Here's the output of the explain command for 7.6 (please excuse the indentation) :

WMEM RANGE CONDITION OR KEY 450296

SAMPLETIME (USED KEY COLUMN)

MO IDX_MOIDUPDATE JOIN VIA RANGE OF MULTIPLE INDEXED COL. 880

MEASUREDOBJECTID (USED INDEX COLUMN)

CO COLLECTIONID JOIN VIA KEY COLUMN 563

TABLE HASHED

MD MANAGEDDEVICEID JOIN VIA KEY COLUMN 4

TABLE HASHED

NO TEMPORARY RESULTS CREATED

INTERNAL TEMPORARY RESULT TABLE SCAN 1

RESULT IS COPIED, COSTVALUE IS 7466

Here's the output of the explain command for the same query when run against 7.7:

MO IDX_COLLECTIONIDOBJECTNAME INDEX SCAN 863

ONLY INDEX ACCESSED

CO COLLECTIONID JOIN VIA KEY COLUMN 563

TABLE HASHED

MD MANAGEDDEVICEID JOIN VIA KEY COLUMN 4

TABLE HASHED

WMEM XMO_D_WINDOWSMEMORY JOIN VIA INDEXED COLUMN 450296

ADDNL. QUALIFICATION ON INDEX

MEASUREDOBJECTID (USED INDEX COLUMN)

NO TEMPORARY RESULTS CREATED

INTERNAL TEMPORARY RESULT TABLE SCAN 1

RESULT IS COPIED, COSTVALUE IS 2091129

The explain output did not change after running the "Update Statistics". Please let us know what else we can try. Thanks for your time.

Sameer

[Former Member]

Removing the duplicate posting.

Edited by: samapt0100 on Aug 26, 2009 6:52 PM

[markus_doehr2]

Check if all optimizer parameters of your database are set according to

https://wiki.sdn.sap.com/wiki/display/MaxDB/MaxDBOptimizerParametersVersion7.7

Markus

[Former Member]

Hi Markus,

I appreciate your quick response. Here're my findings.

I checked the values of Extended and Support parameters listed in the article (titled: MaxDB Optimizer Parameters Version 7.7) you forwarded to me . I checked the parameter values by connecting to the 7.7 DB thro' WebDBM URL at port 7230. I was able to find all variables from the "Extended" and "Support" section of the article with the exception of the "Extended" variable - "UpdateStatSampleAlgorithm". It simply wasn't defined under "Extended", "Support" or "General" parameter tab.

Should I newly define this parameter? How would I go about doing this? Please let me know. Thanks.

Sameer

Edited by: samapt0100 on Aug 27, 2009 9:59 PM

[Former Member]

Just wanted to add to the information I earlier provided. I decided to get the values of the Parameters using DBMCLI as opposed to from Web DBM. As I said in the earlier post I could not find the value for Parameter "UpdateStatSampleAlgorithm" from the Web DBM, but could find the value from dbmcli by doing the following: "param_getfull UpdateStatSampleAlgorithm" after being connected to the database. The active value for the parameter does exist and is "1".

So it appears everything looks OK on the parameters front. Please advise what else could be done. Thanks for your time.

Sameer

Edited by: samapt0100 on Aug 27, 2009 11:18 PM