Figure 6.11 shows the prediction results from the trained classifier J4.8. The three profiles under the no-cache configuration are also tested. The snapshots used for the no-cache configuration are new collected snapshots, not the training snapshots. The y-axis is the percentage of “OLTP” predicted by the classifier over all the predicted results from the target snapshots of a certain workload profile.
0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% No Caching Query-Result Caching Table Caching O L T P P e rc e n ta g e
Browsing Shopping Ordering
Figure 6.11: OLTP Percentage of TPC-W Profiles
The first group shows how the trained classifier predict for the Browsing pro- file, the Shopping profile, and the Ordering profile when there is no cache applied. Among all the predicted results, the Browsing profile gets 0.1% of “OLTP”, while 17% for the Shopping profile and 100% for the Ordering profile. The second group is the predicted results under the query-result-cache. In this case, the Browsing profile gets 32% of “OLTP”, while 79% for the Shopping profile and 100% for the Ordering profile. The third group under the table-cache shows that all the profiles get 100% of “OLTP” among the predicted results. This clearly indicates that under the table-cache, the Browsing profile and the Shopping profile change to “OLTP”. This figure also shows that query-result-cache is also effective at pushing TPC-W
Browsing profile and Shopping profile towards “OLTP” workloads.
The results suggest that if table caching is deployed, a DBMS tuned for OLAP workloads similar to the TPC-W profiles should be adjusted to an OLTP-only DBMS. As well, if table caching is used, the benchmarking of E-commerce workload can just focus on the performance of the OLTP (the Ordering profile) transactions. As E-commerce Web sites provide better searching and data mining services, the proportion of cache-able queries may increase in future E-commerce workloads. Us- ing dynamic caching may bring more benefits to such systems.
6.4
Summary
This chapter shows how the dynamic caching mechanisms affect the classification of database workloads. The snapshot attributes of the TPC-W Browsing profile and the Ordering profile have quite distinct distribution groups if there is no dynamic caching mechanism. The table-cache and query-result-cache change the snapshot attribute distributions of the TPC-W profiles. The dynamic caching mechanisms decrease the values of Queries Proportion, Pages Read, Rows Selected, and Sorts, and increase the values of Index Ratio and Logging. The trained classifier predicts all the three TPC-W profiles as “OLTP” workloads under the table-cache.
The experimental results in this chapter indicates that dynamic caching mecha- nisms can turn an “OLAP” workload into an “OLTP” workload seen by a database server. If table caching is used in an E-commerce system, a DBMS tuned for OLAP workloads similar to the TPC-W Browsing and Shopping profiles should be adjusted to an OLTP-only DBMS.
Chapter 7
Conclusions and Future Work
7.1
Conclusions
The performance of the database server in an E-commerce system is crucial. Due to the wide use of dynamic caching, the workload seen by the database server is dramatically changed. Understanding these changes is important to the design, tuning and capacity planning of the database server.
This thesis studied the workload characteristics and classification of the database server in a benchmark E-commerce system. Two dynamic caching mechanisms are used: query-result-cache and table-cache. The main findings about the reduction effects of caching are: using dynamic cache can considerably reduce the response time of the database server when it is heavily loaded; using dynamic cache can considerably reduce the CPU usage of the database server when it is heavily loaded; using dynamic cache can substantially reduce the number of page references; using dynamic cache can reduce the number of disk I/Os.
The experimental results in this thesis also show that dynamic caching mecha- nisms affect the temporal locality and spatial locality that are exhibited in the TPC- W workload references. In most cases, the temporal locality becomes worse after using dynamic cache, but to a smaller degree than that reported in file servers and Web proxies. Interestingly, for workloads with few cacheable queries (i.e., the TPC- W Ordering workload), using table-cache increases temporal locality of database page references. This result is contrary to that in file servers and Web proxies. The
write temporal locality exhibited in the TPC-W workload references is poor in both systems with and without dynamic cache. The TPC-W workload references exhibit moderate spatial locality, which can be further reduced by use of dynamic cache.
In this thesis, it is found that the snapshot attributes of the TPC-W Browsing profile and the Ordering profile have quite distinct distribution groups if there is no dynamic caching mechanism. Moreover, Dynamic caching mechanisms typically blur the distinctions of the snapshot attributes among the TPC-W profiles. Dy- namic caching mechanisms can dramatically pushes the TPC-W Browsing profile and Shopping profile towards OLTP workloads. This suggests that if caching is deployed, a DBMS tuned for OLAP workloads similar to TPC-W Browsing profile or Shopping should be adjusted to an OLTP-oriented DBMS.
Another finding of this thesis is that the buffer pool replacement algorithms of LRU, ARC, and LIRS do not show much performance difference in both systems with and without dynamic cache. It is also found that the table-cache does a better job than the query-result cache upon both reducing the database server load and pushing the TPC-W Browsing profile and Shopping profile towards OLTP work- loads. The table-cache is more effective than the query-result cache at reducing the response time, database server CPU utilization, the number of page references, and the number of disk I/Os.