I wrote an application that can use the server. This application collects information and sends it to the server. This is done every 10 seconds. The amount of data depends on the players playing, but allows you to store it on approximately 50 servers, each of which sends 100 data units (5000 in total every 10 seconds).
These pieces of data contain the SQL query (in the PreparedStatement syntax), the Object[] values for the SQL query, and the server identifier.
Now I want to process all the data. And this is what is not suitable for me. I have a MySQL server that has 5 tables. Three tables are constantly updated and cannot keep up with the flow of data. Tables are InnoDB tables, mainly because I can lock at the row level this way, and not at the table. Most queries are UPDATE queries anyway, almost any INSERT .
I tried to execute all the requests at once, which only led to terrible performance, as the communication server also had to wait for the completion of this.
I also tried placing all the queries in a large ConcurrentLinkedQueue and emptying this queue every few seconds. Performance was better, but it was too slow.
Then I tried the solution for each table, which was a little better. The path is too slow.
Currently, it uses the configuration for each server (creates a new thread for each server and performs all requests there). It is still too slow. He cannot keep up.
As you can see, I have tried many things. I also tried using addBatch() and then executeBatch() , which is also used in the current setup. Of course, I also looked here, google, etc. It has useful information, but basically it's just adding PreparedStatements and using BatchUpdates.
Any ideas on how to do this?
source share