0

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:

5
  • Please consider reading this advice
    – mustaccio
    Jul 29, 2023 at 20:23
  • Thanks! I added all the results in pastebins.
    – herhor67
    Jul 29, 2023 at 21:56
  • There's a lot to digest here. Typically performance tuning is best starting with an example problematic query and peeling the layers back that way. Sometimes it's as simple as some query or index tuning, other times larger architectural problems are exposable that way. With that being said, do you have a couple of slow queries and their EXPLAIN ANAYLZE you could provide so we can understand what your root problems are? Also I'm a little confused on your database size. What's the row count for the largest table? How many rows are inserted or updated over a given time period?
    – J.D.
    Jul 30, 2023 at 1:48
  • @J.D. 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.
    – herhor67
    Jul 30, 2023 at 15:15
  • 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...
    – herhor67
    Jul 30, 2023 at 15:17

1 Answer 1

0
  • 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?
15
  • ~800 vehicles * 5 regions gives over 4k tables. All of them use the same 2nd definition. In my program, buffers store rows of data to be inserted. When a buffer is full, it is moved to the queue and then sent to DB. Sorry, maybe "tops" is a wrong word, I meant leaderboards by any statistic. I meant to ask whether upsert inserting a row is faster than upsert updating a row because of PK collision.
    – herhor67
    Jul 30, 2023 at 10:51
  • Again, sorry for the confusion. I haven't been running the updater now because it downloads data faster than the DB can save it. Therefore my primary goal is to speed up inserts (almost) as much as possible (of course still having some FKs or indexes, but really selects are much less important).
    – herhor67
    Jul 30, 2023 at 10:54
  • But very well, I will run it for a 24h and come back with the logs again.
    – herhor67
    Jul 30, 2023 at 11:47
  • Alright, I will try to merge the tables, add that index and check how it performs. One thing I'm not sure, is whether the region column is needed at all. As I wrote in the original post, the IDs do not collide between regions, so a region can be deduced from the range the ID lies in. (1st region: 0-500mln, 2nd region: 500mln-1bln, 3rd region: 1bln-2bln, etc). As for your last question, no, there is no risk of any kind of race condition/order/etc.
    – herhor67
    Jul 30, 2023 at 19:02
  • I can agree (it just came to me) that having all vehicles in one table should reduce fragmentation, because they will always be downloaded in one call and then inserted together...
    – herhor67
    Jul 30, 2023 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.