Thread: Database design wisdom needed
Hi
Sorry for this somewhat long email but I think it is relevant to most
people who run online databases. I am having trouble optimizing UPDATE
queries on a certain semi-large table that is only growing larger.
I've come across some very interesting thoughts from this list, so I
thought I'll post my conundrum here. Would truly appreciate any
guidance or pointers.
I have done the messy DBA's job of breaking one table into three,
which should actually have been one. Just did it to make sure the
large main table doesn't keep getting updated with small crumbs of
information.
This main table stores what users of our online service have created.
These are small Flash games, but over the last seven years, we have
collected about 5 million of these. The primary key of this table is
"GAME_ID". In our online account management interface I also need to
show each producer's creations only to himself, so of course I need a
"PRODUCER_ID".
Above that, in our service we have had three more bits of information
for each created game:
- Number of views for the game (online views)
- Number of plays of the game (online people playing)
- Number of unique plays of the game (based on ip or
subscriber_id..some games do not require login to play)
Because each of these pieces of information was individual to each
game, my original table looked like this:
GAME Table
-----------
GAME_ID
PRODUCER_ID
VIEWS_COUNT
PLAYED_COUNT
PLAYED_COUNT_UNIQUE
In the beginning, everytime someone played a game online, we updated
the PLAYED_COUNT in this table. When someone viewed a game, we updated
the VIEWS_COUNT. It was simple and it worked as it reflected the
business logic.
Over time, however, I realized that while GAME_ID and PRODUCER_ID
remained basically static, the next three columns had a very high rate
of change. For example, VIEWS_COUNT would increment every second for
popular games. The PLAYED_COUNT would increment everytime someone
played, but the fact that we have "unique" into the equation means
that I had to break this away into a log table, so that I could GROUP
BY ip address and then update the columns accordingly in the GAME
table. So I had:
GAME_PLAYED_LOG Table
----------------------
GAME_ID
PLAYER_IP_ADDR
From here, I would select COUNT(PLAYER_IP_ADDR) and COUNT(DISTINCT
PLAYER_IP_ADDR), grouped by GAME_ID. I would then update the main
GAME table's columns with these values. This process was done every
hour to make sure we didn't have a huge number of rows to manipulate.
Anyway, now we're realizing that we are becoming increasingly popular
and our tables are large enough for us to require optimization.
Because GAME table is mostly for static information, and is also
required to be highly available because of our online interface, I
have just taken away the VIEWS_COUNT and PLAYED_COUNT into another
table.
GAME Table (~5 million rows)
----------------------------
GAME_ID
PRODUCER_ID
GAME_COUNTS Table (also ~5 million rows of course)
---------------------------------------------------
GAME_ID
VIEWS_COUNT
PLAYED_COUNT
PLAYED_COUNT_UNIQUE
This way, from our GAME_PLAYED_LOG, we do not need to update the main
GAME table every hour, but only the GAME_COUNTS. This leaves the GAME
table free to do its job. That is our main table, so keeping it static
was our priority.
My problems:
1. Because we are a popular website, the GAME_PLAYED_LOG table grows
at massive rates every hour. About 1,000,000 records. Doing COUNT
queries on this table already is pretty resource hogging, even if we
do them every hour. Especially the DISTINCT grouping to get unique
played count.
1.5. After we update the GAME_COUNTS table, we also truncate the
GAME_PLAYED_LOG table because its records have no significance
anymore. This hourly deletion leads to fragmentation etc in the table.
2. The UPDATE queries to GAME_COUNTS table are also a problem. The
sheer number of UPDATE queries is massive. Of the 1,000,000 views
every hour, many are just one or two views of many, many games
(remember, we have 5,000,000 games). So every hour we end up running
thousands of small UPDATE queries like:
update GAME_COUNTS set VIEWS_COUNT = VIEWS_COUNT + 3, PLAYED_COUNT + 1...
3. Finally, the JOIN queries between the GAME table and GAME_COUNTS
table are not very fast. They take about 1 second each, even if I do a
LIMIT 20 in every query.
Now I suspect this scenario is *very* common in online databases that
must report viewer statistics. How are you guys doing it? What am I
missing? Isn't the decoupling of the static information from the more
frequently updated information a good idea? Would using a different
storage engine for different kinds of tables help -- engines that were
better for INSERT and SELECT, while others that were good for UPDATE?
We ran MySQL until a year ago but we have switched to Pg since we had
data corruption issues. I am sure I'm missing some tricks in the Pg
world, and would truly appreciate any ideas.
If you are reading until here, well done! And so many thanks in
advance for any insight you can shed into this matter.
.EP
On 06/05/07 00:46, Erick Papadakis wrote: > Hi > > Sorry for this somewhat long email but I think it is relevant to most > people who run online databases. I am having trouble optimizing UPDATE > queries on a certain semi-large table that is only growing larger. > I've come across some very interesting thoughts from this list, so I > thought I'll post my conundrum here. Would truly appreciate any > guidance or pointers. > > I have done the messy DBA's job of breaking one table into three, > which should actually have been one. Just did it to make sure the > large main table doesn't keep getting updated with small crumbs of > information. > > This main table stores what users of our online service have created. > These are small Flash games, but over the last seven years, we have > collected about 5 million of these. The primary key of this table is > "GAME_ID". In our online account management interface I also need to > show each producer's creations only to himself, so of course I need a > "PRODUCER_ID". > > Above that, in our service we have had three more bits of information > for each created game: > > - Number of views for the game (online views) > - Number of plays of the game (online people playing) > - Number of unique plays of the game (based on ip or > subscriber_id..some games do not require login to play) > > Because each of these pieces of information was individual to each > game, my original table looked like this: > > > GAME Table > ----------- > GAME_ID > PRODUCER_ID > VIEWS_COUNT > PLAYED_COUNT > PLAYED_COUNT_UNIQUE > > > In the beginning, everytime someone played a game online, we updated > the PLAYED_COUNT in this table. When someone viewed a game, we updated > the VIEWS_COUNT. It was simple and it worked as it reflected the > business logic. > > Over time, however, I realized that while GAME_ID and PRODUCER_ID > remained basically static, the next three columns had a very high rate > of change. For example, VIEWS_COUNT would increment every second for > popular games. The PLAYED_COUNT would increment everytime someone > played, but the fact that we have "unique" into the equation means > that I had to break this away into a log table, so that I could GROUP > BY ip address and then update the columns accordingly in the GAME > table. So I had: > > > GAME_PLAYED_LOG Table > ---------------------- > GAME_ID > PLAYER_IP_ADDR > > > From here, I would select COUNT(PLAYER_IP_ADDR) and COUNT(DISTINCT > PLAYER_IP_ADDR), grouped by GAME_ID. I would then update the main > GAME table's columns with these values. This process was done every > hour to make sure we didn't have a huge number of rows to manipulate. > > Anyway, now we're realizing that we are becoming increasingly popular > and our tables are large enough for us to require optimization. > Because GAME table is mostly for static information, and is also > required to be highly available because of our online interface, I > have just taken away the VIEWS_COUNT and PLAYED_COUNT into another > table. > > > GAME Table (~5 million rows) > ---------------------------- > GAME_ID > PRODUCER_ID > > > GAME_COUNTS Table (also ~5 million rows of course) > --------------------------------------------------- > GAME_ID > VIEWS_COUNT > PLAYED_COUNT > PLAYED_COUNT_UNIQUE > > > This way, from our GAME_PLAYED_LOG, we do not need to update the main > GAME table every hour, but only the GAME_COUNTS. This leaves the GAME > table free to do its job. That is our main table, so keeping it static > was our priority. And good database theory, too, since the number of times that a game is played is not "tied" to the PRODUCER_ID. <golf clap> > My problems: > > 1. Because we are a popular website, the GAME_PLAYED_LOG table grows > at massive rates every hour. About 1,000,000 records. Doing COUNT > queries on this table already is pretty resource hogging, even if we > do them every hour. Especially the DISTINCT grouping to get unique > played count. Any index on GAME_PLAYED_LOG? > 1.5. After we update the GAME_COUNTS table, we also truncate the > GAME_PLAYED_LOG table because its records have no significance > anymore. This hourly deletion leads to fragmentation etc in the table. > > 2. The UPDATE queries to GAME_COUNTS table are also a problem. The > sheer number of UPDATE queries is massive. Of the 1,000,000 views > every hour, many are just one or two views of many, many games > (remember, we have 5,000,000 games). So every hour we end up running > thousands of small UPDATE queries like: > > update GAME_COUNTS set VIEWS_COUNT = VIEWS_COUNT + 3, PLAYED_COUNT + 1... > > 3. Finally, the JOIN queries between the GAME table and GAME_COUNTS > table are not very fast. They take about 1 second each, even if I do a > LIMIT 20 in every query. > > > Now I suspect this scenario is *very* common in online databases that > must report viewer statistics. How are you guys doing it? What am I > missing? Isn't the decoupling of the static information from the more > frequently updated information a good idea? Would using a different > storage engine for different kinds of tables help -- engines that were > better for INSERT and SELECT, while others that were good for UPDATE? > We ran MySQL until a year ago but we have switched to Pg since we had > data corruption issues. I am sure I'm missing some tricks in the Pg > world, and would truly appreciate any ideas. > > If you are reading until here, well done! And so many thanks in > advance for any insight you can shed into this matter. What index(es) is/are on GAME and GAME_COUNTS? What version of PG are you running? Are you pushing the box too hard? -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good!
On Tue, Jun 05, 2007 at 01:46:42PM +0800, Erick Papadakis wrote:
>
> GAME_COUNTS Table (also ~5 million rows of course)
> ---------------------------------------------------
> GAME_ID
> VIEWS_COUNT
> PLAYED_COUNT
> PLAYED_COUNT_UNIQUE
This is a poor normalisation. While views_count is necessarily a
superset of played_count, the two values are not functions of one
another really (for instance, there will be games that have been
viewed but never played); therefore they should be in separate
tables, I'd say. I'd also like to point out that a 5 million row
table is actually not very big.
In order to make this fast, I'd probably implement a dirtyish hack
along the following lines.
create table game_views_summ (
game_id int references games(id),
views bigint,
counted_at timestamp with time zone);
create table game_views (
game_id int references games(id),
viewed_at timestamp with time zone);
Then, you have a daemon that summarizes data in game_views regularly
into game_views_summ and deletes the data in game_views that's
just been updated. If you do this more or less all the time, you can
keep the vacuums up to date and keep the bloat to a minimum. It's
an increase in overall I/O, but it ought to be smoother than just
trying to cope with it in big lumps. (A similar strategy will work
for the play counts.)
One thing to be careful about is that you don't end up with
game_views with very few rows, but a huge number of dead rows. This
two-table approach can rapidly become a pessimal implementation in
the event you are too efficient at eliminating the
rows-to-be-summarized, but have a lot of dead rows that are
unrecoverable because of running transactions. You'll end up with a
seqscan on a table that contains four live rows, except that it's
400M. That pattern is a killer.
> 1.5. After we update the GAME_COUNTS table, we also truncate the
> GAME_PLAYED_LOG table because its records have no significance
> anymore. This hourly deletion leads to fragmentation etc in the table.
Are you truncating, or deleting? TRUNCATE leaves no dead rows,
fragmentation, &c. That's its point.
> (remember, we have 5,000,000 games). So every hour we end up running
> thousands of small UPDATE queries like:
>
> update GAME_COUNTS set VIEWS_COUNT = VIEWS_COUNT + 3, PLAYED_COUNT + 1...
Why is this bad? (You don't actually need thousands of these, I
think, because you ought to be able to design one query to do it all.
But I'd be unhappy with the locking, I think, given what you're
trying to do.)
> 3. Finally, the JOIN queries between the GAME table and GAME_COUNTS
> table are not very fast. They take about 1 second each, even if I do a
> LIMIT 20 in every query.
This sounds like something's wrong in your query or your plan.
EXPLAIN ANALYSE is your friend here. I'd suspect vacuum issues.
Oh, one other thing. I noted you're storing the player's IP address.
You do know that maps very poorly to actual individuals on the other
end, right?
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Unfortunately reformatting the Internet is a little more painful
than reformatting your hard drive when it gets out of whack.
--Scott Morris