Mysql – MySQL maximum database or table count

MySQLperformance

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:

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

Additional information:

These tables are in no way linked to eachother; they do not need to be joined at any point.
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.
This will be stored on ReiserFS (or comparable FS with no file-count concerns)
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!

Best Answer

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.

Related Solutions

Mysql – How to Best Implement nearest neighbour search in thesql

As far as design patterns, the Yelp question is pretty standard stuff.

For a more complex answer, you will probably need the geospatial distance. Here is a fascinating powerpoint about that topic (and here is a pdf version of that as well). However, the math involved is quite ugly.

From their slide:

set @orig_lat=122.4058; set @orig_lon=37.7907;
set @dist=10;

SELECT *, 3956 * 2 * ASIN(SQRT(
POWER(SIN((@orig_lat - abs(dest.lat)) * pi()/180 / 2), 2) +  COS(@orig_lat * pi()/180 ) * COS(abs(dest.lat) * pi()/180) *  POWER(SIN((@orig_lon – dest.lon) * pi()/180 / 2), 2) )) as  distance
FROM hotels dest 
having distance < @dist
ORDER BY distance limit 10

There's a longer, more in-depth answer about geospatial distance on Stack Overflow.

But you still want to limit the results by latitude and longitude.

Ultimately, I would avoid the POINT datatype and go with latitude/longitude. There's currently no way to determine the distance between two POINTs, so you're going to have to store latitude/longitude for that calculation anyways.

One last link: you may also want to check out this SO thread regarding speeding up the queries using spatial indexes.

Sql-server – Four db servers all identical, one is much slower than the others

I'd be inclined to point a finger at the storage of the poorly performing server.

Sounds like you're in the unfortunate position of having a storage admin telling you everything is ok when it quite possibly isn't. Only way to be sure is to prove it.

If you have the luxury of a maintenance window, run a batch of SQLIO tests against each server and compare the results. @BrentOzar has a good intro article on SQLIO which also covers some alternative tools such as Crystal Diskmark, which might be enough to prove the point in your case. Run the tests in collaboration with the storage admin, so they can witness the issues first hand. Perhaps arm yourself with How to Prove It’s a SAN Problem before hand.

If IO testing proves fruitless or impossible, throw sp_whoisactive or a similar monitoring solution at the problem. Kendra Little's Collecting Data from sp_WhoIsActive in a Table would be the best approach for a long running ETL process.

Before doing any of the above, take 1 minute to check something very very basic that crops up time and time again... the NTFS allocation unit size on each of the arrays.