0

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.

  • Initially, this doesn't strike me as a huge amount of data - how many GB in total now? Growth per month of data (in GB)? What sort of RAID setup do you have? Are you using MyISAM or InnoDB? – Vérace Aug 5 '14 at 11:07
  • Partitioning has some limitations. dev.mysql.com/doc/refman/5.6/en/partitioning-limitations.html , what you actually implement is an ad-hoc partitioning mechanism. The bad with partitioning is the insert performance. If your application is write heavy i suggest to keep it the way is now. What kind of select you execute – Antonios Aug 5 '14 at 11:09
  • I'm thinking of some sort of nightly batch update to a DW read-only (normally) server? Of course, you'll have to benchmark this for your OS/software-config/h/ware-config and budget. – Vérace Aug 5 '14 at 11:42
  • Updated question with the details required. – amparo69 Aug 5 '14 at 12:38
  • What's your performance like on these UNION queries? What's the RAID setup? – Vérace Aug 5 '14 at 13:48
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.

0

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.

0

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.

Your Answer

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

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