MySQL Partitioning in a real-world example

Question

I want to try MySQL Partitioning in a football fantasy game where users are distributed in leagues, and each league has a market where users can sell or buy players. I'm experiencing some deadlocks in this table when a lot of users play at the same time (there are about 50K leagues at the time of writing, each one with about 20 players in the market refreshed every two days), so I was thinking about using MySQL Partitioning, which I haven't used before.

This is the table I would like to partition:

CREATE TABLE `market` (
  `leagueID` int(10) unsigned NOT NULL,
  `playerID` smallint(5) unsigned NOT NULL,
  `userID` int(10) unsigned DEFAULT,
  `price` int(10) unsigned NOT NULL ,
  `date` int(10) unsigned NOT NULL,
  UNIQUE KEY `league_player` (`leagueID`,`playerID`),
  KEY `user_date` (`userID`,`date`)
);

Which approach (column, ranges, number of partitions, etc.) do you recommend?

This is my initial approach:

ALTER TABLE market
    PARTITION BY HASH(leagueID)
    PARTITIONS 10;

Can you explain why? The entire market is refreshed every two days, so the date column only will have fresh values. — Javier Marín, Apr 11 '16 at 12:07
if you want to know more about partitioning go here http://www.slideshare.net/datacharmer/mysql-partitions-tutorial/23-KEY_CREATE_TABLE_Employee_empid — Ankit Agrawal, Apr 11 '16 at 12:09

score 2 · Answer 1 · answered Apr 12 '16 at 21:57

2

Why do you want to partition?

I ask because most attempts at partitioning gain nothing. No performance boost; sometimes performance degradation. In particular BY HASH rarely helps.

Are you using MyISAM? If so switch to InnoDB. Since you mentioned 'deadlocks', maybe you are already using InnoDB? Partitioning won't help in transaction deadlocks; we need to look at the queries in the two transactions. The solution may be as simple as sorting an IN list.

But... Regardless of whether we 'solve' the deadlock you are having today, you need to check for errors and replay the entire transaction that was aborted. That's the only sure way to 'solve' deadlocks.

Datacharmer's slides give you the gory details; my blog lists the very few cases where PARTITIONing is useful, thereby making most of his slides useless.

Other issues...

I don't see a PRIMARY KEY. Recommend you change your UNIQUE key to PRIMARY KEY. InnoDB really needs a PK.
There is a DATE datatype; it might be less clumsy to use than some INT.

answered Apr 12 '16 at 21:57

Rick James

135,179
13
127
222

The deadlocks are produced when two transactions (I'm using InnoDB) try to insert a new player in the market, both trying to lock the UNIQUE key and therefore one of then failing. I'm making use of Google App Engine Task Queues, so the entire transaction is retried until succeeded. With partirions, the UNIQUE index is splitted across several tables, so the chance to be locked at the same time is way lower. – Javier Marín Apr 13 '16 at 22:38
If it is the _same_ key, then it will be locking the _same_ row in the _same_ partition. No benefit. – Rick James Apr 13 '16 at 22:41
With partitions, each transaction lock the key only in the corresponding partition of the corresponding league (I'm speaking about two transactions trying to insert new playera in differents leagues) – Javier Marín Apr 13 '16 at 22:44
Is the deadlock real? Or imagined? You would have to add a million players a day from multiple clients to happen to encounter a deadlock. I doubt if you have that many players. Have good indexes and keep queries fast and simple -- that _minimizes_ the chance of collisions, _some_ of which are Deadlocks. – Rick James Jun 07 '17 at 21:46
market is updated once a day for every league at the same time: a task queue limited to execute 500 leagues simultaneously – Javier Marín Jun 07 '17 at 22:06
Are there multiple players in each league? Typically how many? Would it make sense to update (once a day) a table with `PRIMARY KEY(league)` and just the columns specific to a league? That is, keep the player out of the picture so that this mass update will be more efficient. – Rick James Jun 07 '17 at 23:48
yes, I think it would be a solution to have some pre-created rows and update them instead of delete and insert, but it would make the process way more difficult as it will have to sync with existing rows and update them one by one – Javier Marín Jun 08 '17 at 03:16
Instead of delete & insert, consider `INSERT .. ON DUPLICATE KEY UPDATE ..`. If you are replacing the entire table, then simply do that--build a new table, then swap tables. – Rick James Jun 08 '17 at 05:02

score 1 · Answer 2 · answered Jun 07 '17 at 19:03

I've seen something similar a couple times now, and I'm not convinced that partitioning will solve your problem.

The deadlock you're seeing on the unique index might be due to two non-conflict-violating inserts using the same database page which the lock is on. Due to the B-tree structure of the index, if a single league is write heavy in a particular window in time (seems plausible), there is a higher probability that there will be a race condition for the mutex on that page being hit.

I'd consider distributing the index "hot spot" physically by changing the order of the two fields in the unique constraint, and adding the existing index as a regular index (for lookup reasons).

ALTER TABLE `market` 
ADD UNIQUE KEY player_league (`playerID`,`leagueID`),
DROP UNIQUE KEY `league_player`,
ADD KEY league_player (`leagueID`,`playerID`);

On that note, as you don't have a primary key yet (but InnoDB is gonna create one behind the scenes anyway), it logically makes sense for the new key to take on that role.

ALTER TABLE `market` 
ADD PRIMARY KEY (`playerID`,`leagueID`),
DROP UNIQUE KEY `league_player`,
ADD KEY league_player (`leagueID`,`playerID`);

MySQL Partitioning in a real-world example

2 Answers2