How to enhance (the speed of) my database

Question

First of all, I need to say that I have not much formal training for SQL, especially the backend, so anything I tested was a trial and error of advice found on the internet.

I have a DB which stores some selected statistics of players from an online game - World of Tanks. A program I made runs calls to the external API, parses received data, and writes into the DB. In my program, I used to construct queries as strings to deal with varying length of returned data per API call, which worked decently. Then I switched to prepared statements, but that required me to send everything row by row, which was terrible. Finally, I implemented buffers for the data, which coupled with batch prepared statements is probably the fastest version. When a buffer is full, it gets pushed to a queue and a worker thread executes the query.

The game is split into several regions (5 to be precise). Each region has separate servers, players, API, etc. Each account has some general data (account ID, nickname, creation time, games, wins, last update time, etc) and per-vehicle stats (games, winrate, average damage).

I have one table storing general data of players per region. First, I stored all vehicle stats in one table per region, but the performance soon got abysmal, so I divided the data into separate tables per vehicle per server. This has an obvious disadvantage of needing to create tables dynamically, and requiring separate prepared statements for each table.

CREATE TABLE `accounts_<region>` (
  `accid` int unsigned NOT NULL,
  `nick` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '???',
  `tcreated` bigint NOT NULL DEFAULT '0',
  `tlastbttl` bigint NOT NULL DEFAULT '0',
  `battles` mediumint unsigned NOT NULL DEFAULT '0',
  `wins` mediumint unsigned NOT NULL DEFAULT '0',
  `t_updt` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`accid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `tank_<tankid>_<region>` (
  `accid` int unsigned NOT NULL,
  `battles` int unsigned NOT NULL DEFAULT '0',
  `winrate` float NOT NULL DEFAULT '0',
  `dpb` float NOT NULL DEFAULT '0',
  PRIMARY KEY (`accid`),
  CONSTRAINT `tank_<tankid>_<region>_fk` FOREIGN KEY (`accid`) REFERENCES `accounts_<region>` (`accid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Unfortunately, when I want to generate some leaderboards which include players from all servers, or all vehicles, I have to first select data from each table into a temporary table and then select again. Additionally, some rare vehicles have barely any rows, some are available not on all regions, and some are played by almost everyone, so the tables differ a lot by sizes.

Is there any way to join all the vehicles tables together (or even all regions together, because IDs will not collide) without hurting the performance even more? I thought about partitioning, but after reading more, I'm not sure if that is what I need...

The most pressing problem is the insert speed. Incoming data rate is around 5k rows per second. After batching in groups of 100, it comes to around 50 queries per second, while the DB is currently able to write around 20 qps. Largest account table is ~50mil rows, total is ~150mil rows. 16GB in 5 files. Largest vehicle table is ~7mil rows. 90GB in ~4400 files.

I think that the sudden decrease in performance I noticed, is because when inserting into an "empty" table, the DB can just write the rows in one place. However, now that the DB has to actually update the rows, and they are scattered across the table/file, it takes much longer to run that single query... Does this make any sense? Maybe I could insert to temporary table and then move all the data to the main table?

Are there any settings that I can change to somehow make the performance better? Any indexes? Other that what I wrote above, I also tried disabling FK constraints, but I could not notice any difference. I tested various values for some settings like pool_size, redo_log, thread_concurrency etc. Can some of the "dangerous" settings be applied to that DB only, not entire MySQL server? In case of this database I don't really care if something goes missing or a false value occurs, as long as it doesn't irreversibly break the entire table... It will get downloaded and corrected after some time anyway...

How do big companies handle databases like these? My DB is only a small fraction of the original one, and still it has to perform at unbelievably higher rates. Is my hardware just too bad? Are UPDATEs so much slower than just INSERTs?

Hardware:

Some of the specs as shown by inxi: https://pastebin.com/raw/MfcExvYU

Some info from hdparm:

/dev/sda:
 Timing cached reads:   20346 MB in  1.98 seconds = 10275.64 MB/sec
 Timing buffered disk reads: 1256 MB in  3.00 seconds = 418.14 MB/sec
/dev/sdb:
 Timing cached reads:   29782 MB in  1.97 seconds = 15092.17 MB/sec
 Timing buffered disk reads: 1386 MB in  3.00 seconds = 461.46 MB/sec

sdb is a system disk, sda is currently only used for MySQL data directory. (yeah I know, the letters once got swapped for some reason)

MySQL config files:

Changes in the default mysqld.cnf:

• general log disabled
• slow log disabled
• bin log disabled

Additional settings:

table_open_cache = 10000
table_definition_cache = 10000

#innodb_thread_concurrency = 4
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 2G
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_io_capacity = 1000
innodb_io_capacity_max = 4000
#innodb_redo_log_capacity = 4194304
innodb_doublewrite = 0

transaction-isolation = READ-COMMITTED

connect_timeout=28800
interactive_timeout=28800
wait_timeout=28800

Edit: As suggested by @mustaccio:

• Status: https://pastebin.com/raw/1FhqJaPa
• Variables: https://pastebin.com/raw/q3P15JeQ
• Processlist: https://pastebin.com/raw/t5NUnJfh
• InnoDB monitor: https://pastebin.com/raw/U2u0hxKL
• InnoDB metrics: https://pastebin.com/raw/38gQKCek
• MySQL tuner: https://pastebin.com/raw/DbmTDpuU

Answer 1

• One things says 2GB of buffer_pool, another says 4GB. Which is it.
• Unless you have a lot of RAM being used by the app, increase `innodb_buffer_pool_size to 70% of RAM. Usually, this is the only tunable that a user should modify.
• 4467 tables?? Why so many?
• Lower long_query_time to 1 so we catch more of the slow queries and turn on the log. SlowLog
• First we need to see the slow queries, then the CREATE TABLEs. You listed two table definitions; maybe none of the other 4467 is relevant?
• What is the "data queue"? A table? The contents of a table? Do you have "worker threads" pulling things out of the queue?
• Only 79 SELECTs (cf Com_select) in nearly 2 days? That seems extremely low. Only 6 INSERTs per minute?? Was the SHOW GLOBAL STATUS when running as MySQL's root? Or maybe some user without access to all the activity?
• "one table per region" -- Usually, it is bad schema design to have "one table per xx". Instead, have an extra column to specify the 'region' (or whatever) and have suitable indexes to speed up locating the desired row(s). Sounds like my comment here will help with "join all the vehicles tables together"
• "want to generate some tops"?? -- manufacturing some toys for spinning?
• I must see the queries to advise on INDEXes (including the PRIMARY KEYs).
• "Are UPDATEs so much slower than just INSERTs" -- Since they server different purposes, comparing their speed does not make sense. "Upsert" combines them; it should be used when appropriate.

More

• "~800 vehicles * 5 regions gives over 4k tables ... tank_<tankid>_<region>" --> Change to one table with 2 extra columns. This lets you do batch updates using UPSERT. That could run several times as fast. It won't work with multiple tables like you currently have. Please start a new question about "batch UPSERT", so I can spend more than one paragraph on it.
• The combined tanks table would have a 3-column PRIMARY KEY(tank_id, region, accid) I don't have enough info yet to say the optimal order for the columns in the PK.
• "Tops" (leaderboard) stuff -- This is likely to run very slowly. Again, isolate the problem and start a new Question.
• Is there an order to the inserts/upserts/updates. For example, might you get UPDATE t SET y=7 WHERE id=123; and UPDATE t SET y=8 WHERE id=123; and need to be sure to run them in the correct order?