Redesign basic Mysql storage architecture

Question

We are a small Internet provider and we are storing all our clients traffic data in a MySQL database (MyISAM). We insert data every few minutes in three tables like this:

capture_table
----------
client_id
date
time
data1
...
datan
---------

The data is always queried by date.

After the tables were getting too big, we decided to use several tables to divide the data. We created three PHP scripts that ran every few minutes dividing the original data into 3 tables (MyISAM) per month and creating an index table.

index_table
data_table_2014-01-1
data_table_2014-01-2
data_table_2014-01-3
...
...

Currently we have this structure and about 2.5 million of rows per table for our current number of clients, and growing (DB weights about 15GB).
The queries are done by joining the data from all the tables involved between two dates using UNION ALL, always searching for date and client. Example, search between 2014-01-01 and 2014-01-15:

(SELECT * FROM data_table_2014-01_1 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')
UNION ALL (SELECT * FROM data_table_2014-01_2 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')
UNION ALL (SELECT * FROM data_table_2014-01_3 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')

Is this the better approach? Could this be achieved in a better way? I was thinking of table partitioning for example...

Thanks a lot.

Answer 1

In my opinion, you should use an SSD if you are facing performance problems. Here is a 120 GB drive for approx. 64 Euro (< 100$ US). This will keep your application ticking over for a couple of years without any need for a redesign and performance should substantially improve for minimum expense.

Down the road, if you are ultimately deciding to move to a DW scenario, may I recommend PostgreSQL as your DW server over MySQL? MySQL is good for read-heavy web-facing apps, but not for DW type work. PostgreSQL has CHECK CONSTRAINTs, SET operators (INTERSECT, EXCEPT), CTEs (Common Table Expressions) and Windowing functions (all of which MySQL (incredibly) lacks).

Traditionally PostgreSQL was not strong on the replication, clustering and connection pooling front, but that has changed.

Answer 2

My opinion since you are going to have multiple VMs and you want to stick with Mysql use Fabric to shard your database. http://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric-quick-start-sharding-scenario.html. Sharding is horizontal partitioning and you can split your data across multiple servers and gain faster writes and reads.

Answer 3

If you choose not to use built-in partitioning you can roll your own by having a view

create view capture_table as
select * from data_table_2014-01-1
UNION ALL
select * from data_table_2014-01-2
UNION ALL
select * from data_table_2014-01-3
etc.

Overnight maintenance creates a new data table, drops the old ones and re-generates the definition of the view. Read processes never have to change. Write processes may be trickier, depending on MySQL's abilities (I'm SQL Server centric). Option 1 - dynamic SQL to always write to the current table. 2 - constant name for the current table, and rename during maintenance. 3 - write to the view and let the RDBMS sort it out.