Calculate hardware needs based on desired writes per second

[Jan Bromberger]

We want to be able to replace into a table of offers around 7 mio rows daily, and within half an hour max. The data is stored for three months, so it will have up to 644 Mio rows.

It takes around 2 hours on my local development machine, that sports a SSD and really long (cancelled after 30 hours) on an old server of mine. I want to order a new server that can do it fast enough and want to estimate the required hardware/setup.

7 mio rows consume around 400 MB of data and around 500 MB of index, so after 92 days we can assume 37 GB of data and 46 GB of index. We are currently using InnoDB.

7,000,000 / 60 * 30 seconds lead to almost 4,000 sustained writes per second.

How can one calculate the hardware requirements for this?

Do we need enough RAM to hold the whole index, or even the index and data? 46+X GB, or even 83+X GB?

I assume, hard disk will be the bottleneck when I don't have enough RAM, CPU will be the bottleneck, when I do have enough RAM?

SSDs aren't that terribly expensive anymore, our hoster offers them for a modest setup fee. Should we get two of those?

Would partitioning the offers by date (into 92 partitions) help us? We have one single query that needs to get offers for one product over all days, all other queries only work on one or two days.

Our table looks like this:

CREATE TABLE `offers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`source` enum('user','other') COLLATE utf8_unicode_ci DEFAULT NULL,
`valid_at` date DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`other_id` int(11) DEFAULT NULL,
`product_id` int(11) DEFAULT NULL,
`other_category_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`shop_id` int(11) DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
`price_with_shipping` decimal(10,2) DEFAULT NULL,
`availability_code` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_offers_on_other_id` (`other_id`),
KEY `index_offers_on_user_id` (`user_id`),
KEY `index_offers_on_product_id_and_price_and_source` (`product_id`,`price`,`source`),
KEY `index_offers_on_product_id_and_pricews_and_source` (`product_id`,`price_with_shipping`,`source`),
KEY `index_offers_on_other_category_id` (`other_category_id`),
KEY `index_offers_on_shop_id` (`shop_id`)
) ENGINE=InnoDB AUTO_INCREMENT=X DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Thanks,
Jan