1

I have a server installed in a fixed container with limited bandwidth and CPU.

On this server, I have a website that makes a lot of requests to its database.

The web app's MySQL database is only one table.

The client app makes one request every 5 seconds via AJAX and the AJAX opens the MySQL database. This is unsustainable in the fixed container after a few hundred users on the site for more than 10 minutes simultaneously.

query via AJAX

In order to minimise the load on the fixed container, I want to divert these requests from

1. client queries the app's MySQL database, which is in the server with limited resources

to

2. client queries another server without a fixed limit of requests per minute

the benefit would be that I don't have to move the entire web app to the unlimited container, instead, it could considerably less costly, make many more AJAX requests on another elastic or more capable server.

Is there an easier internal method?

  • MySQL load balancers
  • caching systems
  • MySQL slaves replication
Mau
  • 113
  • 5
  • 1
    Is there any reason you need to keep MySQL in the container, or can you make the other MySQL server your master? If you can have the other server master you just do an export from the current database, import into the new server, then change your connect parameters. – Tim Mar 05 '19 at 20:22
  • 1
    @Mau, Did you mean to say 'The app makes one request - for each connected user - every 5 seconds via AJAX, the AJAX opens the MySQL database.' ? If so, fix it in your original sentence, please. Please post TEXT results of SHOW GLOBAL STATUS; and SHOW GLOBAL VARIABLES; from the 'server' that can not keep up with the activity. – Wilson Hauck Mar 10 '19 at 19:07
  • 1
    Hundreds of users hitting a 1-row table once every 5 seconds. Is that all? I suggest that your problem is elsewhere. Please provide more details about the query and the table. – Rick James Mar 11 '19 at 05:40
  • I have added more details as requested. @WilsonHauck – Mau Mar 13 '19 at 10:53
  • @Tim I think you are on the right path, I think I only need a JSON file to be on the big server and the entire app to be on the small one. – Mau Mar 13 '19 at 10:54
  • @RickJames I have added more info as requested. – Mau Mar 13 '19 at 10:54
  • 1
    @Mau - The simple number of "queries per second", regardless of number of users or AJAX vs other, etc, is what I am looking for. If under 100, then there should be no problem. If over 1000, we need to get into the details. – Rick James Mar 13 '19 at 15:45

1 Answers1

2

You pretty much listed your options. If your data is pretty static and can be effectively reused for multiple queries by multiple clients you shoild look at some form of caching.

Some options are:

  1. memcache daemon installed in your website container. The app will consult the memcache first to see if the required data are there and only if not will query MySQL. And then store it to the cache with some expiration time.

  2. AWS ElastiCache - also memcache but managed by AWS.

  3. MySQL Cached Query - you still make requests to the database but the DB caches previous results and returns them much faster.

  4. Try to optimise your database schema and add the right indexes. That will speed up the processing and lower the DB load.

  5. If your website is too popular and the used instance sizes can’t keep up maybe you need to choose bigger ones. Look at CloudWatch monitoring and check the system load.

Hope that helps :)

MLu
  • 24,849
  • 5
  • 59
  • 86
  • isn't having a JSON file on a big server (AWS) better than having to deal with cache in the small server (MySQL)? – Mau Mar 13 '19 at 10:57
  • @Mau I wouldn’t consider JSON file a replacement for cache or database. One of the big disadvantages is that it needs to be completely loaded to memory in each process that wants to use it. If the JSON is large enough and you’ve got enough processes you may run out of memory. – MLu Mar 13 '19 at 17:58
  • 1
    @Mau **memcache** is the answer for your usecase, don't reinvent the wheel ;) – MLu Mar 14 '19 at 01:57
  • my JSON is {"show":true,"html":"......html code.....Download the Workbook..........","type":"HTML"}, i am happy to install memcache, although i never used it before – Mau Mar 19 '19 at 07:07
  • i installed memcached on Apache 2.4, i could not make it work. I am considering asking another question with more details about the AJAX query. But if you know how I could cache this JSON (see above) using memcached it would be great! – Mau Mar 22 '19 at 03:12