For estimating the computational load, approximate cost figures for different local as well as remote operations are needed. The approximate cost of the local read, insert and update operations will be referred to as Rl, Il and Ul respectively1.
For remote operations it has been observed that when a number of SQL state- ments are executed operating on a same table (which fits in main memory), the first SQL statement that is executed after opening the connection to the MySQL server is more expensive than subsequent statements operating on the same table. Therefore, when a sequence of remote operations is performed on the same table
1Note that the delete operation is not explicitly discussed in this chapter, the methods presented
8.2. Significant Parameters 131
(regardless of type), the first of these operations is marked as a “high” cost oper- ation and the subsequent operations as “low” cost. High cost remote operations will be referred to asRrh(for read, remote high cost),Irh(for insert, remote high
cost) andUrh. The low cost remote operations are referred to asRrl,IrlandUrl.
Because the performance model is to be used for a class of applications that typ- ically open a connection, perform a number of queries and close the connection, the high cost operations cannot be amortized over subsequent operations.
There is one exception to the above described rules. If two similar operations on different tables are performed subsequently, then the second of these opera- tions is to be counted as low cost. For example, if a select on tableAis immedi- ately followed by a select on tableB, then the select on tableB is counted as low cost, while according to the general rule it should have been counted as high cost. This exception is needed because there is empirical evidence that the cost for the second select is significantly lower than for the first select, even though the select is performed on a different table.
There is empirical evidence as to why this exception is required. We have timed the average execution time of the separate queries in theStoreModeratorLog benchmark in theRemoteconfiguration (see Section 8.3). The results, in microsec- onds, are in Table 8.1. For reads and inserts, the top 3 and bottom 3 results out of 28 measurements were eliminated and the average is taken. For update oper- ations, the top and bottom 2 results are eliminated out of 16 total measurements. Behind each result, the minimum and maximum measurements (after elimination of the top and bottom) are given within brackets. We observe from the table that the cost for the second select are significantly lower than for the first select, even though the select is performed on a different table.
Average Execution Time
Query Core 2 Core i7
selectA 171 [165, 177] 149 [144, 160] selectB 82 [78, 86] 77 [75, 79] updateA 99 [98, 100] 93 [91, 97] updateB 93 [87, 100] 80 [78, 81] selectB 66 [62, 69] 65 [63, 68] selectA 65 [63, 68] 64 [62, 62] insertC 79 [71, 89] 72 [66, 79]
Table 8.1: Execution time in microseconds of the separate queries in theStoreMod-
eratorLogbenchmark in theRemoteconfiguration.
If the insertion of such exceptions were to be avoided, more parameters have to be introduced. Apart from distinguishing only high and low cost operations based on whether the table has been used in a query before in the active connec- tion, distinctions can also be made in whether it is the very first query in the active connection, what kind of index is used for the table that is being queried (normal or primary), etc. An important consequence of having too many parameters in a model is that all possible predictions can be obtained simply by tweaking with all parameters. This renders the model useless. To avoid having too many parame- ters we have deliberately chosen to not take further effects into account.
Finally, next to the parameters for the approximate cost of the different opera- tions, there is one parameter for the “base time”. The base time includes the time required to execute the benchmark code which is submitting the queries, as well as the time required to setup the connection to the remote database server. This time is always measured separately for each respective benchmark. So, if pre- dictions are to be made for a certain benchmark, its base time must be measured beforehand.
Note that for benchmarks only performing local queries, the connection setup time is subtracted from the base time. The connection setup time can be deter- mined by taking the average difference of the page generation time of experiments only performing local queries that do set up a connection to a remote database server and of experiments that do not setup a connection. It is also possible to de- fine the base time as solely the execution time of the benchmark code. However, given the fact that we had more configurations which include both execution time and connection setup time, we have chosen to define the base time as the accumu- lation of these times.
8.3
Experimental Setup
Four components (PHP scripts) from the RUBBoS[74] benchmark will be used for demonstrating the trade-off analysis. By exploiting the different read/write characteristics of these four components the significant parameters will be deter- mined with one of the components and the performance model will be validated against the remaining three components. The RUBBoS benchmark was developed by a collaboration between RICE University and INRIA and models a typical bul- letin board system or news website with possibility to post comments. The PHP- version of RUBBoS has been used and this code base was translated to C++ code using the HipHop for PHP project [42]. The C++ code base facilitates the merger of the application code processing the data and the data retrieval code. This is done by embedding a generic local cache, based on flat C arrays, in the applica- tion code. This is of course a gross oversimplification of the actual implementa- tions of locally cached databases, but it ensures that we do not penalize particular implementation choices made for these locally cached databases. The end result is compiled to a native executable.
The resulting executables have been benchmarked on two generic Linux sys- tems. The first system is based on an Intel Core 2 Quad CPU (Q9450) clocked at 2.66 GHz with 4 GB of RAM. The software installation consists out of Ubuntu 10.04.3 LTS (64-bit), which comes with MySQL 5.1.41.
The second system is based on an Intel Core i7 CPU 2820QM clocked at 2.30 GHz with 8 GB of RAM. This machine was running Ubuntu 11.10 (64-bit), which comes with MySQL 5.1.61. Similar configuration files were used as those for the first system. In order to obtain consistent results the system was configured to dis- allow the system from entering a sleep mode beyond C1 and the clock frequency was locked at 2.30 GHz.
To be able to carefully analyze the trade-offs involved in offloading computa- tional load of the main server for different query mixes, the page generation times
8.3. Experimental Setup 133
were collected of web pages generated by performing different query mixes. The page generation time is defined as the difference between the time the first line of the (translated) PHP code started execution until the time the last line of the PHP code is executed. This time does not include any initial startup cost for serving the request for the web page. The web pages that were selected from RUBBoS are:
• StoreModeratorLog, which performs: two selects, conditionally two updates,
two selects and one insert.
• RegisterUser, which performs: one select, one insert and one select.
• StoreComment, which performs: one select, one insert and one update.
• StoreStory, which performs: one select, one insert.
With the four mentioned components, experiments have been performed in different configurations. The results of these experiments are used in Sections 8.4 and 8.5 to create and to validate the performance model. The configurations range from performing all queries “remotely” in the MySQL server to perform- ing all queries “locally” in a generic data store. Note that while query execution in MySQL is referred to as “remote” execution, the MySQL server is running on the same system that performs the local operations. With “remote”, it is specified that the request has to go out of process and a connection has to be set up with a MySQL server. The exact configurations are as follows:
• Remote, all queries are executed through MySQL.
• L+R, all queries are executed locally and all write queries (insert, update and
delete) are also executed through MySQL.
• L+C, all queries are executed locally and a connection is setup to the MySQL
server (but no query is performed through MySQL). This result is used to approximate the overhead of setting up a connection to the MySQL DBMS.
• Local, all queries are only executed locally.
• Base, no query is executed, however a connection is setup to the MySQL
server. This configuration is used to determine the base overhead of a bench- mark by comparing the result with the result forL+C.
For the configurations in which queries are performed locally, such asL+Rand Local, the generated C++ code was modified by embedding a generic local cache. This enables a clear estimation of the minimum computational cost of local data- base operations. Remote updates are considered to be part of the updates which are performed locally. That is, the local program will not continue until the up- dated data has been committed in the main DBMS.
Specific implementations of local data stores will differ from this generic im- plementation. For example, instead of evaluating a query which processes a write action locally, an implementation can choose to simply forward the query to the main DBMS and wait for an update to be propagated. Other implementations of local data stores, such as the Oracle In-Memory Database Cache product [76],
are capable of both processing the remote updates as part of the local transaction (synchronously) and processing the remote updates asynchronously outside of the transaction. The latter is said to yield significant performance benefits. How- ever, whether synchronous or asynchronous updates should be used, depends on the application and its requirements.