improve database design for monitoring application

Question

I need assistance with my DB design, I have a monitoring script that produce the following data every minute , and produce stats about virtual machine performance.

Array
(
 [timestamp] => 2015年05月05日T18:11:00Z
 [vm_name] => i-2-20-VM
 [memory_internal_free] => 3516900
 [vbd_xvdd_read] => 0
 [vbd_xvdd_write] => 0
 [vif_0_rx] => 0
 [vif_0_tx] => 0
 [memory] => 4294967296
 [vbd_xvda_read] => 0
 [vbd_xvda_write] => 0
 [cpu0] => 0.000100
 [cpu1] => 0.000100
)
Array
(
 [timestamp] => 2015年05月05日T18:11:00Z
 [vm_name] => i-2-24-VM
 [memory_target] => 4294967296
 [vbd_xvdb_read] => 0
 [vbd_xvdb_write] => 0
 [vbd_xvda_read] => 0
 [vbd_xvda_write] => 0
 [vif_0_rx] => 0
 [vif_0_tx] => 0
 [memory] => 4294967296
 [cpu0] => 0.000100
 [cpu1] => 0.000100
)

I'm trying to store the following info into my database, I have create single table

I have created table as follows

CREATE TABLE `md_metrics_status` (
 `id` int(100) NOT NULL AUTO_INCREMENT,
 `timestamp` datetime NOT NULL,
 `vm_name` varchar(50) DEFAULT NULL,
 `memory_target` varchar(255) DEFAULT NULL,
 `vif_2_rx` varchar(255) DEFAULT NULL,
 `vif_2_tx` varchar(255) DEFAULT NULL,
 `memory` varchar(255) DEFAULT NULL,
 `vbd_xvda_read` varchar(255) DEFAULT NULL,
 `vbd_xvda_write` varchar(255) DEFAULT NULL,
 `vbd_xvdd_read` varchar(255) DEFAULT NULL,
 `vbd_xvdd_write` varchar(255) DEFAULT NULL,
 `vif_1_rx` varchar(255) DEFAULT NULL,
 `vif_1_tx` varchar(255) DEFAULT NULL,
 `vif_0_rx` varchar(255) DEFAULT NULL,
 `vif_0_tx` varchar(255) DEFAULT NULL,
 `cpu0` varchar(255) DEFAULT NULL,
 `cpu1` varchar(255) DEFAULT NULL,
 `vif_3_rx` varchar(255) DEFAULT NULL,
 `vif_3_tx` varchar(255) DEFAULT NULL,
 `memory_internal_free` varchar(255) DEFAULT NULL,
 `vbd_xvdb_read` varchar(255) DEFAULT NULL,
 `vbd_xvdb_write` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=162339 DEFAULT CHARSET=utf8

Now this table has grown so large, and searching for records between specific timestamps is very slow.

I'm unable to add an index on timestamp field, because I get many duplicate records, as you can see in the example.

What is wrong with this table design? how can I improve this setup?

I have full control over the database, and over the script that produces the data.

Your input is highly appreciated.

Thank you

Answer 1

Add an index, not a unique key:

ALTER TABLE md_metrics_status ADD INDEX (`timestamp`);

I believe you are using PHPMyAdmin to add the index, and in PMA, unique is the default choice when you want to add an index, and that is why you are getting the duplicate key error.

• Deano: I have tried adding an index, Alter table ’status’.’md_metrics_status' add primary key ('timestamp’) but I'm getting duplicate key error still. type of index is set to primary.
• Jehad Keriaki: Your statement will add special key called Primary Key, which has to be unique. Try my statement instead.