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
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?
Some of the specs as shown by
Some info from
/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
- general log disabled
- slow log disabled
- bin log disabled
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: