MySQL – Hardware and Software Configuration for Database with 10,000+ Tables

MySQL

I am looking at rolling out a CMS system that will require the creation of around 10,000 tables within the primary MySQL database of the system.

The database will be the data store for several hundred small website front ends that might draw a modest load of around 150k unique viewers per month, but this might have to scale on short notice.

I'm looking for some advice around what kind of hardware should be used to be cost effective but also to have the ability to scale if the need arises.
I would also like some advice around the software configuration: i.e. should the MySQL setup be clustered or just straight forward MySQL with a high number of open files?

Any advice will be greatly appreciated!

Best Answer

This is no different than the average shared web hosting company. Hundreds to a thousand or more small sites sharing single database server. The high number of tables won't affect anything. I have a database server with over 40,000 actively used tables.

So don't use that as a basis in your hardware planning at all. Get as much RAM and fast disks as you can, as that's what your bottlenecks will always be.

When you grow beyond the capacity of one server, partitioning will be simple thanks to having separate tables for each CMS instance. You just need a scheme to map each one to which server its tables are on.

Related Solutions

MySQL Configuration for Large MyISAM Tables

You will need to check out the sum total of your indexes for MyISAM. Please 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;

Whatever number comes out, use either that number or 8G, whichever is smaller.

MyISAM only caches index pages.

If you plan to migrate your data to InnoDB, use these settings:

[mysqld]
innodb_file_per_table
innodb_log_file_size=2047M
innodb_log_buffer_size=64M
innodb_buffer_pool_size=18G
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=10000

You wiil need to optimize the InnoDB infrastructure

Mysql – Look for software that can convert sql (thesql) to erd

You can use MySQL Workbench (http://www.mysql.com/products/workbench/) or Navicat for MySQL (http://navicat.com/en/products/navicat_mysql/mysql_overview.html)

Best Answer

Related Solutions

MySQL Configuration for Large MyISAM Tables

Mysql – Look for software that can convert sql (thesql) to erd

Related Question