THEOS MySQL Client

A few years ago, we were having severe stability issues with our heavily utilized THEOS server.  I determined one of the primary causes for the lack of stability were large reports that users would generate.  These reports would very I/O intensive. Because Corona Doesn’t have DMA , the disk I/O consumes a large portion of the CPU causing starvation to occur for the other users. Eventually the entire system would lock up and require a reboot.

The thought crossed my mind, what if I could have all of these reports generated on another server and reserve the main server for data entry and quick lookup tasks. I tried setting up a dedicated Corona report server, but soon abandoned the idea as I could never get the integration to work the way I wanted it to.

THEOS Software Corporation has a product knows as Theos Data Base (TDB). This product allows SQL access to the underlying ISAM files stored on the THEOS server. Unfortunately, I was never able to get it to work correctly and the performance penalty for accessing the data from the SQL layer instead of direct file access was significant.

I then thought about exporting the data from within Corona to a third party database which would allow the Corona server to send a simple query to the external database server, retrieve the results, and format the report with the data that was returned. I decided to experiment with MySQL because there are no startup costs, the source code is available and I am already somewhat familiar with it from some PHP programs that I have developed.

The first version of the THEOS MySQL Client was a Windows application written in C#. I used a packet sniffer to analyze the packet layout of the TDB protocol. The C# application acted as a protocol wrapper. My MultiUser BASIC applications residing in Corona would use the TDB API (/Programs/Basic/Include/TDB.Basic) and send calls to the C# application. The C# application would translate the TDB calls to the corresponding MySQL calls and use the MySQL .NET Connector to connect to the database and issue queries.

After about 6 months in production, I decided to scrap this approach and write a direct MySQL driver for Corona. The primary motiivation for this change was due to the fact that the TDB API returned all of the result sets in files. This meant that the speed of the query was still limited by the speed of the Disk I/O on the THEOS server. In addition, I felt that the TDP API was too verbose. A simple query like SELECT * FROM products WHERE item_number = 12345 required about 20 lines of code to open the connection, send the query and process the result set. The final reason for the switch was to abandon the need to connect to a Windows machine running the C# application in order to then connect to a MySQL database hosted in linux.

I found that MySQL had published the client protocol. This made writing a native THEOS MySQL driver very easy. I didn’t have to reverse engineer or packet sniff anything.

The second version of the THEOS MySQL Client connects directly to a MySQL server using a BASIC function mysql% = fn.mysql.connect%(dsn$), issues a query with result% = fn.mysql.query%(mysql%, query$), retrieves a result set with CALL mysql.fetch.row(result%, DIM columns$(10)). There is no need for the intermediary disk file that TDB uses. The result sets are stored in RAM. Of course, if you need the data in a file, I added a few calls to automatically accomplish this, such as CALL mysql.result.to.csv(result%, csv.file.name$) and CALL mysql.result.to.isam(result%, isam.file.name$, key.fields%).

Adding this driver has helped decrease the utilization on the current THEOS server by allowing the data retrieval process for large reports to be generated on a second server. I ran numerous speed tests and found that I could issue a query to the MySQL server, retrieve the result set from the network and store it in RAM before THEOS could read the same dataset from the built-in ISAM files. The only problem with my RAM based approach is if the dataset is too large for main memory, as Corona doesn’t have any type of virtual memory manager. Since RAM is cheap, I considered this a negligible disadvantage.