I wish to know what will be the best solution in term of low cost, better performance, and easy to maintain?
Here are my options:
1. install mysql on each client's PC. Everytime when there is an update of the data, the server will copy the data over to clients' PC. If local users wish to see the display, MySQL query will simply run off the data on the local DB from client side.
2. Upgrade the physical server as in adding one more extra CPU and memory. Most important is the Disk I/O. I may need to get SSD for server or I can get better harddrives with faster RPM. MySQL is running on Web server and web server is running on one of the VM (Virtual Machine). By upgrading the physical machine, it may increase the performance of the VM running web server.
3. Get a SAN storage and install just MySQL. I am not sure if this is the best solution because i haven't try SAN before. I've only use NAS for backup files. I will need some instruction of how to set this up.
4. Install MySQL on an entirely different physical server.
5. Are there other options besides #1 - #4?
If anyone has a good suggestion, please let me know. Thanks