|
Hi I'm trying to optimize some reports on SQL Anywhere 12.0.1.3324. My test system is relatively strong for SQL Anywhere - 24 CPU cores with 24GB RAM. One of the main problem is that SQL Anywhere doesn't utilize the hardware resources or does it in an inconsistent way. I have found that the same query may use parallelism (Parallel Table Scan, for example) on one execution and doesn't do it on the next one. Note that I'm the only user in the system, there are no concurrent queries and the data is not changed at all. So my questions are:
I have more questions about SQLA intra-query parallelism, but I don't want to ask too many questions in a single post. I can upload query plans if required. Thanks in advance Leonid Gvirtz http://www.gvirtz-consulting.com |
|
Tried to upload the query plans - but received a message that upload is available only for users with at least 100 reputation points. I see that I have only 51 :(. |
|
Regarding 3.: The optimizer considers the current workload of the database server and will not use parallel execution if the workload is high. So the usage of a parallel strategy for a query is non deterministic. 1
I agree with you that intra-query parallelism is non-deterministic, as it should be. The question is how much it is non-deterministic and at which point non-deterministic becomes unstable. On my test systems, I'm the only user and the only connection to the database, the test servers (a strong system mentioned above and my laptop) are dedicated for SQL Anywhere, so load from other processes is quite stable (zero). Also, I perform all my tests for a number of times, so the effect of physical reads etc. should not play here. So far, I have found that I can never know for sure whether parallel strategy will be used by the optimizer or not, even though all relevant factors, at least those known to me, remain constant. One of the last problematic scenarios: I have two pairs of almost identical tables. The structure (columns, indexes, etc.) is exactly the same and the data is only slightly different. Actually, the data represents measurements of some kind from different days. The same query worked in parallel for one pair of tables and serially for the second one. This behavior was quite consistent. I didn't succeed to understand from the query plans why there is such a difference in processing of so similar tables. The difference in response time is quite significant. Then, SQLA server was restarted, with no relation to my tests. Suddenly, I discovered that the query that used to run in parallel now runs serially - and quite consistently! So, I'm looking for a way to understand why. I'm going to try to attach query plans shortly. Of course, I understand that SQLA is probably not the best product for a data warehouse and Sybase IQ will most probably do this job much better. But, that's what my customer has and they want try produce the best from SQLA before considering other technologies. They have very positive experience with SQLA until now, by the way. Thanks for your help
(28 Jan '12, 02:45)
Leonid Gvirtz
2
The optimizer's cost model takes into account a variety of factors that include:
to determine the best cost-based strategy for any parallelizable query. For the three query plans you included, all of the plans differ in the amount of table and/or index data that is in cache. With the plan "join_7_before_restart" all index and table pages are all nearly 100% resident in the buffer pool at optimization time; the availability of idle CPU cores, and available worker threads (from the current pool of 50) leads to the choosing of a parallel plan. The two other plans have significant portions of the data (either table or index) not resident in the buffer pool. This causes the optimizer to try to determine the most cost-efficient strategy involving the reads of so much data; in the case of join_6, it's an index scan of the SU table, whereas in join_7_after_restart it's a sequential scan. You may see quite different behaviour if you calibrate your disk subsystem for parallel reads, which should improve the optimizer's estimate of parallel read performance for those queries that involve large tables that are not fully resident in the buffer pool.
(31 Jan '12, 16:20)
Glenn Paulley
|
|
I'm by no means a plan viewer expert, so treat this as just a few wild guesses (until the real experts like Glenn will tell the truth):
Sorry, if this does sound more like a lot of questions than like an answer... EDIT: For the 2nd point, there's a strong hint in the 12.0.1 docs - look at the boldly emphasized part:
|
|
Just another observation: All query estimates expect much more rows in the result set (3.7e+06) than there are actually (simply 1). This might prevent intra-query parallelism if the following doc quote holds here:
The fact that your query does use a particular group by TYPE_NAME defined via a CASE expression might make it more difficult for the optimizer to pre-calculate the result sets's cardinality - apparently, group by a case expression with two branches can only return two rows at maximum... As TYPE_NAME is simply dependent on one column of one table, how would the query perform if you turn the definition of TYPE_NAME in a derived table (based on S) and join that derived table with SU? SELECT CASE WHEN S.SGSN IN ( -371269922, -555819298 ) THEN 'Home' ELSE 'Roaming' END TYPE_NAME, SUM(SU.OCTETS_IN), SUM(SU.OCTETS_OUT), SUM(SU.OCTETS_IN + SU.OCTETS_OUT) FROM SDR_USAGE_STAT_HRS_1_7 SU, SDR_STAT_HRS_1_7 S WHERE SU.START_TIME BETWEEN '2012-01-06 15:00:00.000' and '2012-01-07 03:00:00.000' AND S.SUBSCRIBER_ID = SU.SUBSCRIBER_ID AND S.SESSION_KEY = SU.SESSION_KEY AND S.SUBSESSION_KEY = SU.SUBSESSION_KEY AND SU.DEVICE_ID = 4 GROUP BY TYPE_NAME |
|
I tried a number of things proposed by Volker and Glenn and had some progress, but unfortunately the situation didn't improve too much. First, I tried the calibration by ALTER DATABASE CALIBRATE PARALLEL READ. It didn't make any measurable difference to the query plan, so I decided to revert it back by ALTER DATABASE RESTORE DEFAULT CALIBRATION. Then, I tried to figure out how to make the optimizer estimate less disk reads for the plan. After some experiments, I came to solution that can be seen in the attached join_6_parallel.saplan, I just moved the CASE construction to an outer query and received much more parallel execution plan, even HASH GROUP BY is parallel now. But, this is not the end of the story. Then, I tried to increase the range condition in the query and take the whole day instead of just 3 hours. The query plan looked pretty much the same (parallel), but when I run the query I discovered that query runs in parallel only first 10 seconds or so and then becomes pretty much serial. Reviewing of the detailed plan (see join_6_semi_parallel_whole_day.saplan) reveals that only the most-left branch under the Exchange node actually did the job and others didn't bring any rows. I wonder what can be the reason of that. Does it mean that the optimizer changed its mind during the actual processing of the query? Is it normal/expected? In the meanwhile, I'm considering completely different approach - DIY (Do It Yourself) parallelism. If the query can be easily split into a number of parts that return relatively small result sets each, then it is possible to run the resulted sub-queries in parallel in separate connections and then combine the results. If the original query is wrapped in a stored procedure, as in my case, it is even possible to implement this approach transparently to the calling application by creating a set of events inside the procedure, triggering them (events will work asynchronously) and then combine the results. I tried DIY parallelism for a query like mentioned in join_6_semi_parallel_whole_day.saplan, but spanning 3 days - and the results are quite impressive as compared to intra-query parallelism. I have not checked the new plans - however, is the relevant data "cached" during your new tests? Glenn seems to have confirmed my suspicion that the massive need to disk reads prevents parallel operation - so have you tried to "pre-cache" the relevant data? Or is this unrealistic, as the query would usually access data that is not cached?
(01 Feb '12, 07:05)
Volker Barth
The behavior is consistent, I run the queries a number of times. The rate of caching of the data is good, the actual number of disk reads is much fewer than the estimated one and not so high in absolute values. In a real life we cannot expect that the data will be perfectly cached. This is true even for most of OLTP systems, but here I'm dealing with a kind of data warehouse application.
(01 Feb '12, 08:06)
Leonid Gvirtz
Replies hidden
Well, then I don't have any further suggestions - but the real experts may have...
(01 Feb '12, 08:19)
Volker Barth
|
HashGroupBy can be done in parallel. Although, the optimizer will likely weigh benefits to determine if the overhead of coordinating a parallel effort is greater than the lesser execution time of a serial effort.
A brief introduction to parallelism is available at: http://dcx.sybase.com/index.html#1201/en/dbusage/parallelism.html
O/W, you might be able to post a graphical plan and see if someone can explain some of the optimizer's decisions.
Some further hints:
Thanks to Volker, now I'm able to upload the query plans.link text
join_6.saplan - refers to the first pair of tables. The execution is serial both before and after the restart of SQLA.
join_7_after_restart.saplan - the (partly) parallel execution plan for the second pair of tables before the restart of SQLA.
join_7_after_restart.saplan - the same query after the restart - the execution became fully serial and much slower.
FWIW, the "thanks" should go to the forum community - your newly gained reputation points come from several users, of which I might be one ... I just wanted to inform you about that now overcome limitation:)
I'm new to this kind of forums, still learning :)