AM18 ASA INTERNALS: DATA MANAGEMENT

1. AM18ASA INTERNALS: DATA MANAGEMENT GLENN PAULLEY, DEVELOPMENT MANAGER paulley@ianywhere.com AUGUST 2005

2. Goals of this presentation • Overview of data management and query processing in Adaptive Server Anywhere 9.0.2 • Concentrate on performance issues and problem areas • Provide an overview of SQL Anywhere 9.0 technology • Highlight planned features for the Jasper release • Agenda • Section One: SQL language support, data management • Section Two: query execution and optimization

3. Design goals of SQL Anywhere Studio • Ease of administration • Good out-of-the-box performance • “Embeddability” features  self-tuning • Cross-platform support • Interoperability

4. Motivation for the ASA 9.0 release • Exploit the new architecture of 8.0 and add support for additional language features, including • GROUP BY ROLLUP • RECURSIVE UNION • Window functions and other OLAP support • XML • Table Functions • INTERSECT and EXCEPT • ORDER BY, SELECT TOP N in any query block, including views • Improve performance

5. Highlights of the ASA 9.0 releases • HTTP server • ASA Index Consultant • Improved performance, scalability • better scalability in OLTP environments • Query processing improvements • optimization refinements – particularly with the server’s cost model • histograms modified according to update DML statements • alternate, efficient execution methods for complex queries • SNMP support • 9.0.1 EBF build 1828, Windows platforms only • Formally part of the 9.0.2 release

6. Performance, performance, performance

7. Contents • Language Support • New SQL constructs supported with 9.0.1 • Data Management in 9.0.1 • Database organization • Table storage organization • Index storage organization • Physical database design tips • Jasper features

8. New SQL language support in 9.0.1 • Table functions (SELECT over a stored procedure) • ORDER BY clause now supported in all SELECT blocks • Necessary to support SELECT TOP n in derived tables, views, and subqueries with correct semantics • RECURSIVE UNION (bill-of-materials) queries • INTERSECT and EXCEPT query expressions • LATERAL keyword for derived tables • Now necessary for derived tables or table expressions containing outer references • WITH clause (common table expressions) • Essentially in-lined view definitions

9. New SQL language support in 9.0.1 • SELECT TOP n START AT m • Equivalent functionality to that in MySQL, Postgres • n and m can be variables or host variables • WITH INDEX hint in FROM clause • Named CHECK, PK, FK, UNIQUE constraints • Constraint violation message refers to the constraint name • New catalog tables: • SYSCONSTRAINT contains information about all constraints, even referential integrity constraints • SYSCHECK contains the body of the CHECK constraint; now permit multiple CHECK constraints on the same column(s) • Specific CHECK constraint that is violated appears in error • Not available in older database formats, even if DBUPGRAD is used

10. New SQL language support in 9.0.1 • OLAP support • VARIANCE, STD_DEV aggregate functions • ORDER BY clause for LIST aggregate function • GROUP BY • ROLLUP, CUBE, GROUPING SETS • Binary set functions (linear regression, co-variance, etc.) • Rank functions • Windowed aggregate functions • Construct “moving average” results in a single SQL statement • Support for multiple DISTINCT aggregate functions in a single SELECT block • Necessitates the use of Hash Group By

11. New SQL language support in 9.0.1 • Support for SET statement in Transact-SQL dialect stored procedures • Implemented for MS SQL Server compatibility • EXECUTE IMMEDIATE extensions • Procedures can now use EXECUTE IMMEDIATE to execute dynamically-constructed queries which return a result set • WITH ESCAPES ON | OFF • WITH QUOTES ON | OFF • Variable assignment permitted in UPDATE statements (8.0.1) • SELECT INTO base-table

12. New SQL language support in 9.0.1 • FOR XML AUTO, FOR XML RAW, FOR XML EXPLICIT, OPENXML procedure (supports XPATH queries over XML column values) • SQLX functionality: xmlelement(), xmlforest(), xmlgen(), xmlconcat(), and xmlagg() • EXPRTYPE() function – outputs the type of the expression argument • Useful when defining computed columns • LOCATE() can handle negative offsets • INSERT WITH AUTO NAME (8.0.2)

13. Table functions • Result set description determined from the catalog; result set must match exactly • Otherwise SQLSTATE ‘WP012’ • Workaround: use the WITH clause to annotate the procedure reference in the FROM clause: SELECT * FROM PROC() WITH( X Integer, Y char(17) ) SELECT * FROM SYS.SYSTABLE as st, sa_table_fragmentation() as tbfrg WHERE st.table_name = tbfrg.tablename

14. Table functions • Procedure may return only one result set • Statistics regarding cost, result set cardinality of the procedure are captured at run time; used for subsequent requests • Statistics are stored in SYS.SYSPROCEDURE • Minimally requires DBUPGRAD of older databases to 9.0.0

15. Recursive UNION • SQL-2003 implementation of recursive (bill-of-materials) queries • Only DB2 also offers RECURSIVE UNION support; Oracle implements a ‘cycle’ clause • Uses specialized join operators: recursive hash inner and outer joins • will utilize a nested-loop strategy if inputs are small; done adaptively at run-time during query execution WITH RECURSIVE r (level, emp_id, manager_id) as ( SELECT 1, emp_id, manager_id FROM employee WHERE emp_id = manager_id UNION ALL SELECT level+1, e.emp_id, e.manager_id FROM employee e JOIN r ON (e.manager_id = r.emp_id) WHERE e.emp_id <> e.manager_id and level < 3) SELECT * FROM r

16. Recursive UNION: restrictions • Query expression must be UNION ALL • Recursive reference must be in a query block that does not contain DISTINCT, aggregation, or an ORDER BY clause • Recursive reference in a LEFT OUTER JOIN is permitted • Schema of WITH clause must match recursive query • Implicit type conversions involving truncation can yield undesired results; SQLSTATE 42WA2 returned if server detects a type mismatch • Use CAST to ensure compatible types • Infinite queries are possible; server kills the query after N recursions • controlled by the new connection option MAX_RECURSIVE_ITERATIONS (default 100)

17. INTERSECT and EXCEPT • Implement set/bag difference and set/bag intersection • Both ALL and DISTINCT variants are supported; DISTINCT performed by default • Form query expressions in the same fashion as UNION • NULL treated as a special value in each domain, hence NULLs are equivalent to each other • Useful when formulating queries that require counting of identical rows • See the help for order-of-precedence amongst the set operators

18. EXCEPT and INTERSECT ALL • Rewrite to transform ALL to DISTINCT done automatically by the optimizer • Both EXCEPT and INTERSECT can be computed through either a merge or hashing technique • Also supports an (expensive) nested-loop strategy in case a cache shortage is encountered • With ALL variants: • implicitly performs aggregation to count the number of duplicate rows in each input • A new query execution operator, ROW REPLICATE, generates the required copies of each row SELECT description FROM product EXCEPT ALL SELECT description FROM product as p2 WHERE quantity < 15

19. GROUP BY ROLLUP • Computes aggregates as usual, but result set contains multiple sets of groups • Logically, grouping is performed N+1 times for N grouping expressions • Essentially implements the functionality of COBOL Report Writer in a single SQL request SELECT state, zip, count(*), grouping(zip), grouping(state) FROM customer GROUP BY ROLLUP (state, zip)

20. GROUP BY CUBE • Computes aggregates as usual, but result set contains the power set of the N grouping expressions • Expensive to execute for large N • Result can be restricted through the specification of GROUPING SETS SELECT state, zip, count(*), grouping(zip), grouping(state) FROM customer GROUP BY CUBE (state, zip) SELECT state, zip, count(*), grouping(zip), grouping(state) FROM customer GROUP BY GROUPING SETS ( (state, zip), state, zip, () )

21. WINDOW functions • Part of SQL OLAP extensions • Computes aggregates (except LIST) over a window of rows • Provides an ANSI-compliant way to number the rows of a result set • ROW_NUMBER() rather than NUMBER(*) • Useful to: • Compute cumulative aggregates, or “moving averages” • Eliminate the need for correlated subqueries involving aggregation

22. WINDOW functions • List employees, by department, in four US states by their start dates, along with their cumulative salaries: SELECT dept_id, emp_lname, start_date, salary, SUM(salary) OVER (PARTITION BY dept_id ORDER BY start_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS “Sum_Salary" FROM employee WHERE state IN ('CA', 'UT', 'NY', 'AZ') AND dept_id IN ('100', '200') ORDER BY dept_id, start_date;

23. WINDOW functions • List all orders (with part information) where the part quantity cannot cover the maximum single order for that part: SELECT o.id, o.order_date, p.* FROM sales_order o, sales_order_items s, product p WHERE o.id = s.id and s.prod_id = p.id and p.quantity < (SELECT max(s2.quantity) FROM sales_order_items s2 WHERE s2.prod_id = p.id) ORDER BY p.id, o.id SELECT order_qty.id, o.order_date, p.*, max_q FROM ( SELECT s.id, s.prod_id, MAX(s.quantity) OVER (partition BY s.prod_id order by s.prod_id) AS max_q FROM sales_order_items s) as order_qty, product p, sales_order o WHERE p.id = prod_id and o.id = order_qty.id and p.quantity < max_q ORDER BY p.id, o.id

24. WINDOW functions • Find the salespeople with the best sales (total amount) for each product, including ties: SELECT s.prod_id, o.sales_rep, SUM(s.quantity) as total_quantity, SUM(s.quantity * p.unit_price) as total_sales FROM sales_order o KEY JOIN sales_order_items s KEY JOIN product p GROUP BY s.prod_id, o.sales_rep HAVING total_sales = (SELECT FIRST SUM(s2.quantity * p2.unit_price) as sum_sales FROM sales_order o2 KEY JOIN sales_order_items s2 KEY JOIN product p2 WHERE s2.prod_id = s.prod_id GROUP BY o2.sales_rep ORDER BY sum_sales DESC ) ORDER BY s.prod_id SELECT v.prod_id, v.sales_rep, v.total_quantity, v.total_sales FROM ( SELECT o.sales_rep, s.prod_id, SUM(s.quantity) as total_quantity, SUM(s.quantity * p.unit_price) as total_sales, RANK() OVER (PARTITION BY s.prod_id ORDER BY SUM(s.quantity * p.unit_price) DESC) as sales_ranking FROM sales_order o KEY JOIN sales_order_items s KEY JOIN product p GROUP BY o.sales_rep, s.prod_id ) as v WHERE sales_ranking = 1 ORDER by v.prod_id

25. Data Management in 9.0.2

26. Moving to ASA 9.0.2 • If database is 8.0.2, unload/reload to 9.0 is largely unnecessary • DBUPGRAD to 9.0 required for some catalog schema changes, in particular for the Index Consultant • There should be no consequences of using DBUPGRAD with respect to performance • However: • only 9.0 format databases support named constraints • only 9.0 format databases support cache warming • only 9.0.1 databases support page checksums • 8.0.2 databases do not support index statistics collection by default • Can be turned on when creating the database via CREATE DATABASE (but not dbinit)

27. Moving to ASA 9.0.2 • Otherwise, unload/reload from 8.0.1 or 8.0.0 recommended • Clustered index support • Better statistics management • Improved histogram organization, statistics collection • Index statistics kept persistent in the database file • Improved histograms • Cache warming on startup • Checksums on database pages • PCTFREE option for base and temporary tables

28. Moving to SQL Anywhere “Jasper” • The Jasper release of the SQL Anywhere server will not support older database formats • Jasper will ship with a migration tool to convert an existing database into a Jasper-format database

29. Database organization • A database consists of up to 13 “dbspaces” • Maximum size of each dbspace is limited by the underlying operating system • Maximum database size is also determined by page size • Limit for any dbspace is 2**28 (256 million) pages • Each dbspace, the temporary file, and the transaction log is a simple OS file • Ease of administration, backup • Temporary file is used for temporary tables • A dbspace file grows in 256K extents (512K if 16K pages, 1Mb if 32K pages) • Database files can be copied to/from different endian machines • Can copy database from Wintel to big-endian UNIX systems and back again • Server automatically does data conversion where necessary

30. Database organization • A database file contains: • table pages • index pages • free pages • rollback log pages • checkpoint log pages • Each dbspace for a database must use the same page size

31. Physical organization: tables • Each table uses an independent set of table pages • Each table allocates at least one page, even if the table is empty • Server maintains bit-maps for table pages • Supports clustering of table pages in the same portion of the database file • Facilitates large-block I/O – SQL Anywhere reads 64K at a time when doing sequential scans • Result: considerably faster sequential scan performance

32. Physical organization: tables • New in 8.0.2: ‘scattered read’ support on Windows 2000 and Windows XP • Another mainframe technology being reinvented on PC/UNIX servers • aka “locate-mode I/O” • Improves performance, reduces memory requirements • Coming to other platforms as vendors implement it • Tables cannot span dbspaces • Each secondary index on a table can be stored in a separate dbspace • Recommended if multiple spindles are available (not necessary for RAID devices) • Partition dbspaces on separate devices whenever possible • Brings more disk arms to bear, reducing seek latency

33. Physical organization: tables • Rows are inserted into pages at a point where, if at all possible, the entire row can be stored contiguously • Caveat: row segments are at most 4K; second or subsequent row segments can appear on different pages • Columns are packed tightly together; only unpadded values are stored on disk • Primary key columns are always at the beginning of each row, in sequence • Server may rewrite all rows if PK added or modified • Rows can be of (almost) unlimited size; are split across pages where necessary • Maximum length of any column is 2Gb • Maximum number of rows per page is 255

34. Physical organization: tables • Rows are not guaranteed to be placed in pages corresponding to their insertion order • By default, ASA uses a first-fit algorithm for page selection • To guarantee ordering of a result set, specify an ORDER BY clause • Space is not reserved for columns that are null • BLOB values are stored in a separate “arena” of pages • First 255 bytes are stored together with the row • Access to the rest of the BLOB value will almost certainly require a SEEK • Implications for choice of page size • Once inserted, a row identifier is immutable • An updated row must be split if its new length does not allow it to fit on the page

35. Physical organization: tables • Table pages are allocated in 8 page clusters; cluster allocation depends on page size • 2K: grow 4 clusters at a time • 4K: grow 2 clusters at a time • All other page sizes: one cluster at a time • ASA will re-use database pages for additional inserts if entire pages are freed • Defaults: for 1K pages, free space is 100 bytes; all other page sizes is 200 bytes • DBA can specify freespace percentage to accommodate future table UPDATEs using PCTFREE • PCTFREE characteristic stored in new catalog table SYSATTRIBUTE (and corresponding table SYSATTRIBUTENAME) • Can be specified for temporary tables

36. Page sizes • Page sizes supported are 1K, 2K, 4K, 8K, 16K, 32K • 2K page size minimum on all UNIX platforms • Default changed to 2K in the 6.0.3 release • A server can support several databases concurrently • Buffer pool page size will be the largest database page size specified on the command line • Consider tradeoffs with your choice of page size • 4K recommended; occasionally 8K may offer improved performance • Default will change to 4K with Jasper release • Do not use 16K or 32K pages unless you have a specialty application • In typical environments, large page sizes cause inefficient use of cache

37. Choice of page size does matter • Larger rows usually require larger pages (requires fewer split rows) • Random retrieval performance is dependent on the application • Larger pages can pollute the cache with unnecessary data • Often require larger buffer pools to accommodate the application’s working set • Smaller pages are more cache efficient, but • Smaller pages reduce index fanout, and can increase index depth

38. Choice of page size does matter • Don’t ignore index maintenance costs when considering page size (larger page sizes can mean increased cache pressure) • Test your application with different alternatives • Your mileage may vary • A 4K page size is a typical choice for many applications • My recommendation: use 4K pages unless thorough testing proves that a different page size offers better performance/scalability • See data storage whitepaper • Available at www.ianywhere.com/developer • Recently updated for 9.0.0

39. Physical organization: indexes • ASA 9.0 supports two different types of indexes: • Hash-based • Key is a one-way order-preserving encoding of at most nine bytes of the data values • Hash-based indexes are still used when the key length does not satisfy the limits for compressed indexes • Compressed • Contains Patricia tries in the index’s internal nodes • Used for keys > 10 bytes and less than • 122 bytes with 1K pages • 248 bytes for all other page sizes • Substantially improved performance with larger keys

40. Physical index organization: hash-based indexes • Values in an index are “hashed” into a key of at most 10 bytes using an order-preserving encoding function • WITH HASH SIZE is deprecated • Each indexed column encoded separately, with a one-byte length • A 10-byte hash value can hold two 32-bit integer values (including two length bytes) • Hash values in an index are stored separately from the index entry itself • The hash value for an identical secondary key is shared for each index entry (row) in that index page • This improves fanout when data distribution is skewed

41. Physical index organization: Compressed indexes • Internal nodes in the index contain a Patricia trie • PATRICIA: Practical Algorithm to Retrieve Information Coded in Alphanumeric (D. R. Morrison, J. ACM Vol. 15, 1968) • Combines a binary trie with an optimization to skip over bit comparisons that would result from one-way branching • Result: automatic compression of string data • Excellent fanout of internal nodes • Common substrings of key values have a negligible impact on space requirements and performance • Superb performance improvements in many cases, especially with composite primary and foreign keys

42. Clustered index support • First offered with the 8.0.2 release • At most one clustered index per table (may be a temporary table) • May be secondary index, PK, FK, UNIQUE constraint • Optimizer assumes PK indexes are clustered unless a different clustering index exists • Engine will not attempt to maintain clustering on PK indexes unless they are declared CLUSTERED • May be hash or compressed index • Clustering characteristic stored in SYSATTRIBUTE catalog table • CLUSTERED keyword can be used in both CREATE INDEX and CREATE/ALTER TABLE statements • However, ALTER does not reorganize the table; use REORGANIZE TABLE

43. Clustered index support • On INSERT/LOAD TABLE, server attempts to keep rows physically adjacent in base table pages • Specification of PCTFREE on LOAD can be critical • Adjacency is NOT guaranteed; ORDER BY still requires a physical sort or indexed retrieval • Can significantly improve performance • Optimizer costs clustered index access differently • Consider their use with queries that involve range predicates • Often useful with DATE or TIMESTAMP columns • Use REORGANIZE TABLE or UNLOAD/RELOAD if clustering degrades over time • ALTER INDEX statement can rename an index or change its clustering attribute

44. Physical index organization: fanout and page size • Fanout refers to the number of index entries on a page • Lower fanout means greater index depth, and hence more costly random retrieval • Fanout is affected by • Page size • Hash value size/trie compression • Distribution of key values • Index maintenance • Fanout can degrade over time • sa_index_density() procedure

45. Indexes and query processing • ASA does not store actual data values in the index • implies each base row must be retrieved to • Fetch the values of any attributes, or • To compare keys longer than the maximum hash value size • Indexes are automatically created to enforce referential integrity • Primary keys, foreign keys, unique constraints • All related indexes must be the same type (hash or compressed) • Maximum number of indexes is dependent on page size • <= 4K: 2048 indexes • 8K: 1024 indexes • 16K: 512 indexes • 32K: 256 indexes

46. Indexes and query processing • Each indexed column can be ascending or descending • Index is scanned backwards if the application scrolls in the opposite direction, or an ORDER BY clause specifies the reverse sequence • Support for merge and hash joins means that ASA will often use sequential scans, rather than indexed retrieval

47. REORGANIZE Statement – base tables • REORGANIZE TABLE tablename • Defragments rows on-the-fly by removing/inserting groups of rows in clustered index (or PK) order • Exclusive lock held on the table while a group is processed; commits occur periodically to enable other applications to run, checkpoints are suspended while the group is being processed • Performs implicit COMMITs during operation • Rows will be in clustered sequence when operation is complete (except possibly concurrent UPDATES) • Use new procedure sa_table_fragmentation() to discover tables that warrant reorganization

48. REORGANIZE Statement - indexes • REORGANIZE TABLE tablename [ index specification ] • INDEX indexname • FOREIGN KEY indexname • PRIMARY KEY • Exclusive lock is held throughout • CHECKPOINTs are suspended • Reclaims space lost to update activity • Re-balances the index, especially important after many DELETE operations • Use the new procedure sa_index_density() to identify indexes that require reorganization

49. Data management improvements in 9.0.1 • Better scalability – new lock-free cache manager • Substantially better performance across the board • Support for page checksums • New option for dbinit and CREATE DATABASE statement • Supported by dbvalid utility, and a new statement VALIDATE CHECKSUM • Overhead: largely depends on CPU speed. Examples: • 2.8 milliseconds per I/O for 32K pages • 0.7 milliseconds per I/O for 8K pages • Improvements to dynamic cache sizing • Sampling rate changes with database growth or the starting of a new database on the same server

50. Data management improvements in 9.0.1 • Database cache warming feature • Two operational phases, collection and reload • During collection, page IDs are saved in the database as they are accessed at startup • During reload, collected page IDs are read into cache as background processing • Checks and balances used to prevent swamping the server with I/O during server startup • Need to test performance before deploying • Cache warming is *enabled* by default