|
I am trying to keep this short but I have to explain some details of my/our issues. Below there is a TLDR and the Questions! I am currently analysing a performance issue on a client system with Sybase SQL Anywhere 12/16.
In short this client upgraded from sa-12 to sa-16. Reads and writes have slowed down significantly. Best case 25% slower, worst (some complex view) 1000% or no result is found within a reasonable timeframe. I looked at the fragmentation tab in Sybase central (and the corresponding statement): Table A in sa-12 (Pagesize 16k): Table A in sa-16 (Pagesize 16k): Difference in rows and (normal) pages is due to the 6 months that passed between the two backups. I have been reading up on blobs and extension pages. As far as I now understand the long varchar column is considered a blob. This means that as soon as this column is about to contain more than 254 bytes of data, this data will (by default) be placed on an extension page. (In short: If an Update extends above PCTFREE, the page is full or an INSERT trys to insert more than 254 bytes -> row split and the data of that rows column will be placed on an extension page. I don’t understand how it is possible that I have almost no extension pages for this table in sa-12 and a quarter of a million in sa-16. I have tried to reorganize the table in sa-16 and after a checkpoint and refresh the numbers almost stayed the same. The extension pages even went up by about 10.000. Even if there was a big change within the long varchar colums (i.e. the client has added a lot more data) I have to assume that the data within the long varchar columns is here to stay. TLDR: How can I increase read performance for tables which have a long varchar column? For most rows this column is NULL. For rows that contain data, the average length of this column is about 10.000 characters. I can’t change the table’s structure due to external circumstances. My current Ideas: INLINE BLOB INDEX Realigning the Columns I would be very thankful for any input or ideas! Best regards ### UPDATE I’ll add information here that I forgot and was asked:
|
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 16 Jul '18, 07:28
question was seen: 1,641 times
last updated: 26 Jul '18, 05:43
Just two questions to help others with further details:
Is the schema of both databases for the according table identical ? Do you use the INLINE and PREFIX clauses for the according long varchar column, or are the defaults used, i.e. PREFIX 8 INLINE 256 for character data?
Have you compared the plans of some of the queries with large differences in v12 vs. v16? This is to make sure the worse performance is really due to blob storage and not to other issues - IMHO, v16 sometimes performes much worse because of "more intra-query parallelism" but I can't tell whether that applies here.
Just to re-enforce Volker's point - do a quick test on v16 with
before anything else.
Thanks for the quick reply!
The schema of both databases has not changed much. INLINE and PREFIX are both NULL, so it should be the 254/8 Default.
About the parallelism I noticed one thing. The servers are started with the -gns Option to output the multiprogramming level statistics. I did notice that on sa16, when running a longer query, the number of tasks plummets almost right away to the minimum. Also that the concurrency was often at 2 with algorithm=0 on sa-16. On sa-12 I always saw concurrency[1] or 0 and algorithm=2.
I will try to SET OPTION PUBLIC.MAX_QUERY_TASKS = '1'; once I get access to their system again.
BUT I don’t think that the Parallelism is the culprit here. I did one test. I ran an UPDATE Statement to trim the long varchar columns in both sa-12 and sa16. I reduced every long varchar column to 20 characters. (I changed the data not the type of the column). After that statement ran I checked performance again. Almost every query I tried in sa16 was close to the performance in sa12.
PS: There was one more oddity I forgot to mention: Every execution plan I checked I noticed that in SA-16 I get twice as many cache hits and reads in the subtree statistics.
It might be worth posting your server start-up parameters. We used to do lots of messing with threads etc in v11 (never used 12) but things went much better in v16 when we stripped all that out and left everything on auto.
Testinvironment SA12
-n placeholder -c 9G -ca 0 -cc -cr -cv -ti 1440 -gnl 50 -gnh 1610 -gns -os 10M c:\placeholder\placeholder.db
Testinvironment SA16
-n placeholder -c 9G -ca 0 -cc -cr -cv -ti 1440 -gnl 50 -gnh 1610 -gns -os 10M -x tcpip(ServerPort=2638) c:\placeholder\placeholder.db
Production Invironment SA16
-n placeholder -c 50G -ca 0 -cc -cr -cv+ -ti 1440 "c:\placeholder\placeholder.db" -gnl 40 -gnh 140 -gns -x TCPIP(ServerPort=2638) -o "c:\placeholder\placeholder.txt"
It's hard to diagnose dynamic performance problems by looking at
static statistics like number of pages and
manual snapshots of a few server-level dynamic performance statistics like thread count . . .
. . . better to use tools that continuously capture data at the server, connection and/or query level.
The best tools are the
third-party Foxhound database performance monitor for SQL Anywhere (runtime statistics at the server and connection level)
SQL Anywhere procedure profiler (time spent executing each procedure statement)
See also the Graphical Plan Reading List.
Some folks (not me) have good luck with the
SQL Anywhere Application Profiling / Database Tracing (time spent and plans used for each query)
SQL Anywhere Index Consultant (suggests indexes that might have helped speed up the workload you run)
Then there's this . . .
FWIW, when helping clients diagnose their performance problems, I always start with Foxhound... and more often than not, it's all that's needed :)
I never tried Foxhound, I have seen Screenshots and Blogs about it but I would have to requisition it, and that might take some doing.
About the "SQL Anywhere procedure profiler" With this I am familiar. I have successfully used this on multiple occasions. I did not try this this time because something as simple as "SELECT * FROM TableA JOIN TableB ON (...)" has a longer execution time on SA12 then on SA16. So I didn’t see the point in checking which procedures gets executed how many times and what lines “waste” the most execution time.
I did check the execution plans and they were virtually identical. Except for one thing: The CacheHits and Cache reads were always twice as high on SA16 then on the SA12.
The Index Consultant is something I did not try yet. This is definitely worth a try. I will also try the Monitor, though I have been using dbconsole a lot to check certain metrics but did not find anything unusual when comparing the two test environments.
Is the char-collation for both databases the same?
Which settings do I need to check? I have found NcharCharSet which is set to UTF-8 and NcharCollation is UCA
Well, you are relating to "long varchar" data, not "long nvarchar", so the relevant charset and collation are those for non-wide char...
What do the following properties reveal on both databases:
-- For CHAR data: select db_property('CharSet'), db_property('Collation'), db_extended_property('Collation', 'AccentSensitivity'), db_extended_property('Collation', 'CaseSensitivity'), db_extended_property('Collation', 'PunctuationSensitivity'), db_extended_property('Collation', 'Properties'), db_extended_property('Collation', 'Specification'); -- For the NCHAR collation: select db_property('NcharCharSet'), db_property('NCharCollation'), db_extended_property('NCharCollation', 'AccentSensitivity'), db_extended_property('NCharCollation', 'CaseSensitivity'), db_extended_property('NCharCollation', 'PunctuationSensitivity'), db_extended_property('NCharCollation', 'Properties'), db_extended_property('NCharCollation', 'Specification');The most important property is 'CharSet': Is that the same on both databases? (I.e. changing from a single byte code page like Windows-1252 to a multi-byte encoding like UTF-8 will increase your storage requirements - although usually modestly for common Western languages).