MySQL and high volume data storage
We currently use Oracle 8i to house a database that stores around 10 million rows a day. We currently store the most current 7 days, partitioned by day. We have 15 tables and of those 15 tables 2 main tables are accessed the most. One of these tables has 44 columns -- data types ranging from char to varchar2 (400). The other table has number and varchar2 (4000). We could have around 1 million recoders returned by one query. We have 15 concurrent heavy users. The number of conditions per query range from 6-15.
Will MySQL be able to handle this type of data warehouse database? We will make sure that the hardware can handle either database. We will store 90 days worth or data and we currently partition the data by day. We roll over the data at the 91st day so we can have the most current day. We do not have any MySQL expertise in-house. In the future we will like to insert the data real-time. Today we use temporary tables and do not let our customers see the current day's data.
Table partitioning is a feature that is currently in active development and is part of MySQL 5.1, which will be available as an alpha release very soon. MySQL is currently being used for this scale of data warehousing by a number of customers (one example would be a 20Tb database I encountered being used for analysis of sonar data), but you would certainly want either a MySQL data warehousing professional in-house or additional training on MySQL data warehousing (one example would be the MySQL Data Warehousing Workshop
offered by MySQL AB).
This was first published in September 2005