Skip to Content
0

ASE Date range partition

Oct 17, 2016 at 05:15 PM

155

avatar image

We have a table that has between 40 and 60 million rows, and holds 2 to 3 years of data. The most recent 90 days of data accounts for over 80% of the queries. Access to this table is very slow and we're looking at partitioning the table to improve performance. This could even include keeping the last 90 days of data on a high speed device.

Is there any way to define a partition and/or local index to work on a dynamic value, such as "the last 90 days"? How do other users manage this problem? Do we need to manually alter the table to keep the most recent 90 days of data on the fast device? Will ASE automatically transfer data between devices when the partition is altered?

I would expect that this isn't a new problem, but I can't find any specific discussions about it.

10 |10000 characters needed characters left characters exceeded

Hi all,

Thanks for the replies. I have a bit of learning to do on partitions, and I'll try to be more knowledgeable in the future.

For now, the consensus is to go with 1 year partitions, but I think 6 months would be more efficient. I'm sure we'll try different sizes.

I had already thought of the suggestions Mark made about scripting the data movement to keep the most recent 90 or 120 days in a known partition, but that will be far down the road, if at all.

As to why performance is such an issue, that really needs to be examined more thoroughly. Maybe even bring in someone to do a health check. Potential causes include recent migration from physical SUN hardware running Solaris to Redhat VMs, and upgrading from ASE 12.5 to 15.7. I don't know why they settled on 15.7, since it's EOL date had already been published and 16.0 had been out for over a year.

0

For the upgrade from 12.5 to 15.anything you're talking about a complete rewrite of the optimizer ... with some good aspects and some bad aspects re: performance (a google search should give you plenty to read over the weekend! :-)

Getting someone to do a (performance) health check isn't a bad idea. There are a few of us floating around with 'health check' experience, and of course there's SAP's prof svcs folks. [Just make sure you thoroughly interview the actual person who'll be performing the checks; lots of companies and individuals out here who think 'health check' consists of large quantities of googling and a final recommendation to buy faster hardware/disks.]

------------

Semantic partitions, if you want to pay for the additional licensing, may also provide [huge] improvements in DBA maintenance activities, eg, defragging just those partitions that need it. With a decent partitioning scheme and some SQL scripting I've seen the weekend maintenance window for some clients drop from 24-36 hours down to 1-3 hrs ... all due to only running maintenance on the partitions that needed 'help'

------------

As for upgrading to 15.7 vs 16.0 ... the last time I looked (about 4 months ago), both versions are scheduled for support up through 12/31/2020 (ie, 15.7 is *NOT* EOL!!).

While 15.7 isn't receiving many new features/updates, this also means 15.7 tends to have fewer bug fixes, w/ net result being that 15.7 tends to be more stable than 16.0.

Most clients I work with don't have the (wo)manpower to test and re-test their applications every quarter while waiting for the latest batch of 16.0 bugs to be fixed; said clients just want to get off older, EOL'd versions of ASE with minimal fuss and hassle. I (and a couple other independent consultants I know) have been directing clients towards 15.7 (for stability reasons) if they don't have a burning need/desire for 16.0-only features.

0

Mark. Fair enough about my misuse of EOL for 15.7, but I had seen the 2020 date and figured it meant that was when support ended. Since the DB upgrade was a major migration for the company (performed last year before I came on board), I figured they would have not wanted to go through it again in 4-5 years (after running the last database for well over a decade). But the bleeding edge argument is also valid and this company is extremely risk adverse.

The company brought in a supposed database expert who hasn't worked out well. I think his days are numbered. I know enough about a performance health check to skim the surface, but we really need a guru. A lot of the performance issues are likely going to be found in the 10-20 year shell scripts and stored procs (some of which are >3000 lines), but the tables themselves are large and slow.

I know some really knowledgeable ASE experts from my days in the Sybase PS group, and maybe I can convince powers that be to stop messing around and bring one of them in to fix it.

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Mark A Parsons Oct 17, 2016 at 06:01 PM
1

I'm unaware of any way to create a dynamic partition (eg, rolling 90 days of data).

Depending on how granular you want to get with your data you could partition your data on a quarterly basis (as Mike's mentioned), or smaller (eg, 30 days, 2 weeks, etc).

You could then have a regularly scheduled batch job create new/future partitions in advance to make sure you never have an insert fail due to a missing partition.

If running ASE 15.7 ESD#2 (or better) you could have a regularly scheduled batch job that issues the appropriate alter table ... merge/move partition command to move 'old' partitions (data > 90 days old) onto segments residing on slower devices.

If you're running ASE 15.7 SP130 (or better; not sure about min ASE 16.0 version) you have the ability to create a partial/local index on partitions (eg, create a much needed index on your new partitions; remove the index when you merge/move the partition onto a slower device).

-----------------

With ASE 16.0 SP02 (or better) there is now a feature called Data Store Access Management (DSAM) that allows for the management/movement of data between different classes of devices based on data access patterns. You may want to review this feature to see if there might be a way to automate the movement of 'old' data onto slower devices based on the age of the data ... ? DSAM has it's own manual: DSAM manual

Share
10 |10000 characters needed characters left characters exceeded
Mike Willett Oct 17, 2016 at 05:27 PM
0

No you can't create a partition based on the last 90 days.

Personally, I'd set up partitions based on quarter of years

ie 2015Q1, 2015Q2, 2015Q3, 2015Q4, 2016Q1, 2016Q2, 2016Q3, 2016Q4

- that would mean that you'd only need to access 2 partitions for the last 90 days.

You could set up a partition per month - but that would mean accessing 3 or 4 partitions for the last 90 days

However, the question is why are you queries slow getting the last 90 days. You could just recluster your table around the date to ensure contiguousness over the last 90 days ?

Personally, I partition date on a year basis because getting large data volumes off the SAN is very slow. Partitioning by year keeps the current year in cache and old years on SAN.

And always a good idea to pre-create partitions into the future.

Share
10 |10000 characters needed characters left characters exceeded