This is not a request regarding a specific scripting issue, rather a call for peoples advice and experience. If there is a better forum for this, please let me know.
I have written an Applescript application that makes numerous calls to a MySQL database - the contents and configuration of the app is governed by a few database tables in 1 database, and colour information (CMYK values etc) is got from multiple tables in another database.
The databases are currently hosted on a virtual server in our building (in the UK) and the application works great. However, the application is now being used by other employees, based both in the US and Asia. They are seeing considerable slowdown (8 seconds vs 0.15 seconds) which is kind of understandable but still I am looking for a way around this to speed things up and to get a better architecture in place.
I have setup a Relational DB instance on Amazon Web Services (AWS) and dumped the colour library into it for testing purposes. Actually I have 2 at the moment - 1 in the US Zone and 1 in Ireland. Times are - EU (most local) increases to 2 seconds for 10 queries (just a sample) and US (furthest away) is about 6 seconds. So it seems apparent I can setup 1 more DB in Asia and point the user to the most local one with failover to another one if need be.
Is this how it should be done? As colours and settings are added to 1 database (lets say the EU one) is it possible to configure automated or as-required replication across zones. It seems from the AWS console you have to manually create read-replicas.
Basically, how do I get maximum MySQL performance from anywhere in the world!
I’m not sure if this is actually the answered you are looking for but my advice would be using an webservice like XML-RPC. I have written Scripting Additions for AppleScript as an interface between AppleScript and MySQL to execute as many queries per seconds as possible. And I was able to run hundreds of queries per second. But that didn’t all work out because the latency to other continents are too big to make it ever work fast when the application needs to run multiple queries.
The trick was using XML-RPC. I send one envelope to the server and the server will execute many queries locally. Bundle all the data needed and send it back as a whole to the XML-RPC client. My performance went from 15 seconds to less than a second even for users using the software on other continents. That latter totally depends on the line of both parties, my office is directly connected to an Internet exchange point so it’s always fast. The trick was just to keep executing the queries locally and using Apache as my communicator. Also AppleScript’s more than 10 year built-in XML-RPC support works fine with strings, dates, integers, real and boolean types. I couldn’t get the base64/data type to work and I think it’s not supported. I even use AppleScriptObjC in my Objective-C projects to build an easy XML-RPC server client because Cocoa has dropped support a few years back.
Thanks for the suggestion and I’ll definitely look into that.
Are there any sources of information that you can recommend? Any gotchas or particular things you have learnt along the way? The databases are quite linear so it should be fairly straightforward.
This is the only documentation I used to write the XML-RPC in PHP. There is also an XML-RPC support built-in PHP as there are plenty of free downloadable PHP written XML-RPC server. There are also tomcat and ruby on rails XML-RPC servers available if you prefer other server side programming languages. I have written an lightweight (first version) XML-RPC server using only PHP and simple xml and MySQL in only a few hundred lines of codes.
In fact I haven’t found any gotchas. Returning lists containing 1000s of items is no problem to parse for AppleScript. In fact working with transactions in MySQL can be easier done writing a single XML-RPC method. Also, unlike SOAP, there aren’t multiple versions of XML-RPC. If you write your own server an keep strict to the documentation about XML-RPC you can use the XML-RPC in AppleScript but also on your webpage (JavaScript). After 8 years of massively using XML-RPC I use it in C# (Silverlight), in the browser, in C using libxml2, AppleScript and VisualStudio.
Some critics complain about the missing abstraction layer of XML-RPC. With SOAP there is an WSDL which is the abstraction layer of the webservice. When developing in VisualStudio there is support to directly turn SOAP results into objects. Yes, this part is missing in XML-RPC, once you have received your data as AppleScript you still need code to write to interpreted it. But since records are pretty much usable as objects I’m fine with using records (named structs in XML-RPC) as my parameters.
But if I had to choose one of the biggest drawbacks of using XML-RPC in AppleScript is that debugging the XML-RPC server is quite hard. When the returned data cannot be be parsed by the core services web client it will trow an transport error -917 and that’s it. No idea what went wrong. So I have bought the proxy application named Charles and run the XML-RPC http requests through this proxy so I can easily monitor what’s actual inside the XML-RPC envelopes. Also in a seperate database every call that will be made to the server will be stored in an separate table and removed when a few hours old. This way when someone calls me on the phone and saying that his client doesn’t work I can look back into the database what actually went wrong.
So the gotcha is actually that there is no debugger, no logging and no default authorization whatsoever. You have to build it all yourself or find a free XML-RPC having all these features. It is something you will need while developing.
What I have used in PHP 5.3 is only the MySQL module and simplexml module. It’s not difficult to make, it’s just a website but instead of HTML code it spits out XML code.