I am creating a database that will store 100.000 (and probably more in the future) users. While this obviously happens in a table with 1 row per user, every user can (and will) store hundreds of items. In programming language this would mean the user has 2 arrays (or one 2-dimensional array) of integers: a column for the itemid's and a column for the amounts.

My instincts tell me to create a table to hold all these items, with rows like (userid, itemid, amount). However this would result in a huge table. 200.000 users with 250 items each... that's 50 million entries in one table. This, plus the fact that the table will undergo continuous and rapid change, frightens me. (How rapid? I estimate up to 100 modifications per second.)

Typically there will be anywhere between 100 and 2000 users, all adding and removing items, and modifying amounts. These actions can and will happen in programming code. It would go as follows:

  • User starts session, program loads all the users items from the database
  • User modifies the item list
  • Every few minutes, the changes are saved into the database
  • When the user ends the session, it is also saved into the database

It is worth noting that there is a maximum to the number of items a user can store.

Are there any alternatives to using a separate table? Perhaps save the values in a formatted text string? Or is this one of the instances where using a MySQL database is actually a Bad Idea™?

Thank you for your time and insights.

  • possibly a serverfault question? – bluefeet Jun 27 '11 at 11:00
  • Thanks for the suggestion. I have posted it there as well. serverfault.com/questions/284438/… – Rapsey Jun 27 '11 at 11:13

RDBMs were built to handle these kinds of relations and operations. Plus, storing the users and items separately will help with detailed reporting (eg: number of items added today).

With optimized indexes, reading the many items per user is just one lookup away. The items table can be indexed to store the related items (per user) consecutively, thereby giving a array like reading scenario. Just make sure to use page padding to allow for the future updates/inserts/deletes.

RDBMs are pretty fast and can handle hundred-thousand operations per second, so this may not be an issue. When your items are on a different table, updates done per item will perform better than if the items were stored in a text column (as an array) in the same rows as the user. This will give you faster updates, lesser memory required for the operation, quicker locks and releases (and you may not have to lock your respective user), smaller read/writes, etc...

| improve this answer | |

Keep the items as a separate table. You need quick response time for many concurrent sessions. If using Oracle you could make a nice IMDB Cache scenario. In this scenario, you pre-load the cache for a user at connect time, making the rows that are updated in high frequencies in memory available. For this to use, you do need Oracle 11gR2. See Oracle In-Memory Database Cache It gives you a very scalable solution. Response times can be sub millisecond.

| improve this answer | |
  • Interesting feature (IMDB cache) about Oracle. I wonder if SQL server/MySQL has anything similar. – StanleyJohns Jun 27 '11 at 15:28

MySQL will have no problem handling the workload you suggest. As long as you properly index the tables and have MySQL tuned (The default config should even be fine for a while)

As mentioned, you'll want InnoDB - and you'll want to invest some time learning how InnoDB works.

MySQL has no problem handling over 1000 qps and tables with hundreds of millions of rows.

| improve this answer | |

IF you are going to use MySQL for this application, by all means STAY AWAY FROM MyISAM !!!

All INSERTs, UPDATEs, and DELETEs on MyISAM tables will perform a full table lock before executing the update to the table, even if you are updating a single row.

This makes using InnoDB a whole lot more conducive since it features row-level locking and InnoDB buffer both data and index pages. If you use a large InnoDB buffer pool and users are consistently hammering updates, the users will be cached already and data will systematically flushed to disk at regular intervals.

Once you have data loaded, you can run this formula to estimate (or guesstimate) how big an InnoDB buffer pool you are going to need:

SUBSTR(' KMG',IF(PowerOfTwo<0,0,IF(PowerOfTwo>3,0,PowerOfTwo))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 2 PowerOfTwo) B;

This will read statistics about the consumption of diskspace by InnoDB and print the suggested size in megabytes and it will cap the answer at 4GB. You can use large values for innodb_buffer_pool_size if you are using a 64-bit OS. Case and point: I have a client with 162GB buffer pool on 3 DB servers (MySQL 5.5 with dual hexacore [not a typo, 12 CPUs]).

  • Use (SELECT 0 PowerOfTwo) to see it in Bytes
  • Use (SELECT 1 PowerOfTwo) to see it in Kilobytes
  • Use (SELECT 2 PowerOfTwo) to see it in Megabytes
  • Use (SELECT 3 PowerOfTwo) to see it in Gigabytes

Technically speaking, PowerOfTwo is really power of 1024 for displaying bytes.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy