QUERIES UNDER 4D SERVER V12 64-BIT

Increasing cache size to speed up queries

Usually, scalability is not about speed. Being able to allocate more memory does not mean that an application will run faster, but that it will support a heavier load. However, there are situations where an operation will run faster just because 4D Server could use more RAM. A typical example is the cache size. The 32-bit version can allocate a maximum 2.3 GB of cache. (The remainder is used for the engine: handling connections, processes, users, code, etc.) There are no limits with the 64-bit version, which means it is now possible to fill the cache with all the data (and indexes). This may makes the application faster: With the cache filled with all the data, 4D Server does not need to purge it (i.e. free up space) to load records requested by users; those records are already in the cache, and there will be no need of swap to disk.

 

To demonstrate this feature, we compared the time it takes to run two sequential queries on the same table.

 

Protocol

We used a simple structure: one table, one field. The table holds 7,500,000 records. In each record, the field has a length of 50-100 chars. The data file has a size (on disk) of 3.2 GB. The field is not indexed: Queries will always be sequential.

 

In both cases, we launch two queries. The first query is used to fill the cache with the data. Then we run the same query a second time. This second query should run faster, because at least some of the records were loaded in the cache during the first step.

 

In the 32-bit version, the cache is set to its max of 2.33 GB. In the 64-bit version, it is set to 4 GB, which means that all the data will be held in the cache, while only part of it will be held in the 32-bit version. (Remember that the data file is 3.2 GB.)

 

On the server side, the machine is a Dell with an Intel Xeon, 2.67 GHz, 6 GB RAM, running Windows 7 64-bit. The 4D remote client runs on a machine running Mac OS X on an Intel Core 2 Duo processor.

 

Results

Results for the first query:

 

First sequential query (loading the cache), in seconds

This first query is slow, which is normal: On one hand, there are a lot of records. On the other hand, 4D must read data from disk, which is a slow operation (disk access is in milliseconds while RAM access is in microseconds). The 64-bit version is 25% faster in this configuration because it can load all the records in the cache while the 32-bitversion, at some point, must purge a part of the cache to load new data (See screen shots of the administration window below).

Then, once the cache is full (all records in the 64-bit version, only part of all records in the 32-bit version), the difference between 32- and 64-bit versions is huge: The 64-bit version is 26 times faster:

 

Second sequential query (seconds), the cache is already filled

 

This perfectly highlights what was said above: Once all the data is in the cache, 4D does not need to purge it to make room for new data. There is no need to swap memory to disk. Screen shots of the Administration Window show this.

 

With the 32-bit version and a cache of 1.96 GB, we can visually see that there was purging/loading necessary; we have a seesaw graph:
 

 

With the 64-bit version and a 4 GB cache, all the data was loaded during the first query and is immediately available for the second: Access is very fast because there is no need for purging/swapping to disk:

 

 

Thus, just moving from 32- to 64-bit can increase the speed of an application, without changing a single line of code. Just the cache settings. This is scalability.

 

All the data in the cache?

If you plan to move to the 64-bit version of 4D Server to fill the cache with all of your data data, you should take into account:

• The size of the data file (.4DD)
• The size of the indexes file (.4DIndx)
• The size of the structure (.4DB/.4DC and .4DIndy)
• And the size of the components if any

 

There is no simple, easy formula to evaluate the exact size you need to allocate to the cache in order to put all of your data in it. For example, if the data file contains a lot of free space (i.e. lots of deleted records), you will need a smaller cache. Another example is just the fact that the database runs, lives, and users modify the data every second: Your needs could increase regularly.

 

That said, you can use the following calculation:

• Add the size of the data file and the index file
• Add the size of the structure and components. Usually, structures are not that big, but if it is, you should take it into account.
• Multiply the result by 1.5

 

For example, if an application with a small structure, no components, and size of data + indexes = 4 GB, you should set the cache size to 6 GB.