Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am in the process of creating a massive archive of bitboards stored in MySQL. In my schema, there are four linked sets of bitboards which--for query sake--are represented in four different tables. Each of the four sets will have ~100k rows and I expect there to be about 35k of these sets.

My question is about the performance ramifications and limitations of the SQL server itself. I have considered the following models:

  1. 35k databases, each with four tables of 100k rows each
  2. 1 database, four named tables for each set, e.g., db948-a, db948-b, db948-c, db948-d

Additional information:

  1. These tables are in no way linked to eachother; they do not need to be joined at any point.
  2. They do not need to be UPDATED at any point (one-time INSERT for each of the four tables) and only SELECTs from then on.
  3. This will be stored on ReiserFS (or comparable FS with no file-count concerns)
  4. I expect to use MyISAM (no transactional support needed)

So my main concern is about MySQL limitations on table and database counts--given the above information, is there any limitation I'll exceed that would disqualify one of the schemas?

In addition, are there any glaring performance issues that would arise from either the 35k number of distinct databases, or 140k tables in one database?

Thanks!

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Let's look at many perspectives

PERSPECTIVE #1 : SELECTs against MyISAM tables

If you are doing only SELECTs, you need to run this query:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will recommend the correct size for the MyISAM Key Cache. This maxs at 4GB for 32-bit. You can go higher on 64-bit machines. Yet, use common sense based on the amount of RAM the DB Server has. This should be a concern for you because only the pages from the .MYI are cached. Any thing from the .MYD must be read from disk over and over again.

If the amount of RAM recommended from this query far exceeds the amount of RAM installed by orders of magnitude, just set key_buffer_size to 4G and call it a day on this perspective.

PERSPECTIVE #2 : Leveraging Different Row Formats for MyISAM tables

Altering MyISAM tables to use a FIXED row format can increases overall performance for SELECTs. Why ?

Since CHAR fields require less string manipulation because of fixed field widths, index lookups against CHAR field are on average 20% faster than that of their VARCHAR counterparts. This is not any conjecture on my part.

The book MySQL Database Design and Tuning performed something marvelous on a MyISAM table to prove this. The example in the book did something like the following:

ALTER TABLE tblname ROW_FORMAT=FIXED; 

This directive forces are VARCHARs to have the fixed amount of space allocate like that of a CHAR. I did this at my previous job back in 2007 and took a 300GB table and sped up index lookups by 20%.

Of course, by changing the ROW_FORMAT to FIXED, you will be double the MyISAM table's size in the worst case scenario. If you choose to do this, make sure you have sufficient diskspace.

PERSPECTIVE #3 : Limits on the Number of Open Files from mysqld's vantage point.

Whenever you startup mysqld, many variables are set based on /etc/my.cnf. The rare exception is the option open_files_limit. If my.cnf does not have this setting, mysqld will attempt to compute the best number for this option such that mysqld can still operate and leave whatever files handles available to the OS.

You can set this number higher strictly at your own risk.

PERSPECTIVE #4 : Disk Considerations

Since your storage engine of choice is MyISAM, you must have fast reading disks since data pages are never cached. Please use RAID10 sets. They are easier to do disk maintenances on than other setups. You can could also go with SSD drives. Whatever you choose, prepare for aging disks and periodic disk maintenances. Make sure your RAID cards have the latest firmware upgrades.

SUMMARY

All the information you supplied must be examined from these 4 perspectives. You must strike a good balance and may have to make concessions on your choices based on either budget, available hardware, amount of diskspace, and overall limitations of MyISAM that you have not anticipated.

share|improve this answer
    
In addition to perspective #2: you can run myisampack on the tables. it will change all data types to the smallest possible (considering the data) and compress the rows. Tables will be read-only, but you mentioned they already are anyway. dev.mysql.com/doc/refman/5.5/en/myisampack.html –  Jannes Feb 26 '12 at 10:32
    
Thank you very much; I greatly appreciate the additional perspectives and gotchas to look out for in this design! –  hexparrot Feb 27 '12 at 3:16

Have you considered a mixture? You're at two extremes here. I would suggest meeting somewhere in the middle. How about 280 databases with 500 tables each, or some similar breakdown? Advantage here is you can easily move a large number of databases in one shot should you need to shard / splinter / federate or whatever the cool kids are calling it these days. This also keeps you away from management issues at each of your extremes. Backing up a database with 140K tables, or backing up 35K databases, is not going to be fun. But somewhere in between might be manageable.

share|improve this answer
    
I wish I had a meaningful way to make that sort of split. As is, a given user's data will be processed and saved into four tables. Each of the four tables are relevant to recalling the data, each query only needing one table...but for me to find a medium of 280 databases/500 tables means I need to make an arbitrary separation. But I see what you're saying...perhaps I can find a systematic way of evenly splitting the databases so I needn't waste time searching each database for a table hit. Thanks –  hexparrot Feb 25 '12 at 2:18
1  
Do the clients have some kind of surrogate key like a customerID? I've done similar grouping before based on the mod 10 or the mod 100 of some unique identifier. That makes it easy to distribute initially and also easy to code later for identifying where a client lives. –  Aaron Bertrand Feb 25 '12 at 2:40
    
I greatly appreciate your input--I believe I shall indeed implement a system that can help me find a happy medium between an overdose of either tables or databases as well as a non-arbitrary splitting mechanism to call up the correct table/db. Thanks again! –  hexparrot Feb 27 '12 at 3:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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