Need Advice or what now ?

[Sergio Sanchez]

Hello Every body,

I am currently using Percona-Server-5.6.26-74

and I have reached 2010M Register, yes 2 billion  :)

I need some advice as I don't know what to do to maintain my system to ensure the durability of the data.

My current machine is a HP Z440 Workstation  with 64GB of ram an OS Hard Disk (Centos) and an SSD disk for the Database.

My Current DB is

DROP TABLE IF EXISTS `values`;

CREATE TABLE IF NOT EXISTS `values` (

  `event_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  `tag` smallint(6) NOT NULL,

  `value` float NOT NULL,

  `quality` tinyint(3) unsigned NOT NULL,

  PRIMARY KEY (`tag`,`event_date`)

) ENGINE=TokuDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci ROW_FORMAT=TOKUDB_SMALL;

I tried to add a secondary index suggest by Tim Callaghan  CLUSTERING KEY key_tag_event_date (`tag`, `event_date`) but

I had no benefice in speed and the data used twice the size in the disk, so I deleted it.

I have very few users (about 10) and no concurrency.

The think is I don't know what to do from here to maintain my database.

The DB is getting more and more slow (it's a sensation, how can I test it ? )

Should I add a second SSD to make a  Raid 1 crash proof, I am very afraid of a hw failure. ( I am making monthly backup shutting down the DataBase and copying the whole disk, it's very fast

and the shutdown time is not a problem for my client)

I could say that all my queries are mostly month based or year based, I mean my users are using the data to make statistics by month and year,

so the queries are filtering by year and month, would it be a good idea to partition the data by year or month ? currently queries invloving years 2014 and before are very rare.

Does it have any sense to have a replication slave ?

Any advice,idea would be appreciated.

Thank you.

Bye

[Mathieu Longtin]

Is tag always specified in your queries?

--
You received this message because you are subscribed to the Google Groups "tokudb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tokudb-user...@googlegroups.com.
To post to this group, send email to tokud...@googlegroups.com.
Visit this group at http://groups.google.com/group/tokudb-user.
For more options, visit https://groups.google.com/d/optout.

--

Mathieu Longtin

1-514-803-8977

[Sergio Sanchez]

Hi Mathieu,

Yes. I always querie by taag and date (in a month).

why ?

[Mathieu Longtin]

Making sure the index (primary key) can be used.

Run a typical query with "explain" in front and look if it's doing a full scan or not.