Large innodb tables and hard drive. How to optimize?

Question

MySQL

Input :
server 8GB RAM, 2* hdd 7200rpm, ext4
percona 5.6.24-72.2-log, table with 40Gb texts 9mil records.

CREATE TABLE `tmp_news` (
  `guid` CHAR(50) NOT NULL,
  `s_id` INT(10) UNSIGNED NOT NULL,
  `title` VARCHAR(250) NOT NULL,
  `text` TEXT NOT NULL,
  `date` DATETIME NOT NULL,
  `category_id` INT(11) UNSIGNED NOT NULL,
  `rating` TINYINT(1) NULL DEFAULT NULL,
  `frating` TINYINT(1) NULL DEFAULT NULL,
  `trating` TINYINT(1) NULL DEFAULT NULL,
  PRIMARY KEY (`guid`),
  INDEX `i3` (`category_id`, `source_id`),
  INDEX `i4` (`emote`),
  INDEX `i2` (`date`, `category_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

If the table is added and updated quickly enough, then the selection is not. This does not always happen, and the pattern could not be determined. The same query at different times can be executed for 2 seconds and 40.
If we execute iotop at the time of the execution of the "hanging" query, we will see the mysql process which takes up 99% of IO by reading from disk.
EXPLAIN queries shows that all queries are covered by indexes and more than 3-4 thousand rows are not affected during execution.
I really want to get away from 40 seconds, tell me in which direction to go? (hardware changes, system software tweaks, or query generation)?

Answer 1

`guid` CHAR(50) is a bad PK.
1. It is present in each index and eats a lot of memory, so 400 meters per index in your case. And in this memory there could be data that would not have to go to the disk. The way out is to make PK int autoincrement and guid a unique key.
2. I don't know how your guid is generated, but I can assume that it is evenly distributed, in which case the rows are inserted into random places in the table, which leads to a large number of seeks when fetching. In addition, buffer bool memory is consumed inefficiently in this case. In combination with item 1 - quite a disaster. The output is the same - pk int autoincrement. In addition, number the rows in ascending order of date (I assume that you are more often asked for recent texts) and defragment the table.
The index (date, category) also raises suspicions - when searching by date interval, the index will not be used for categories, because seconds.

Answer 2

cat my.cnf | grep innodb
Ideally, of course, all, impersonal
You need to configure the cache, increase the size of RAM

Answer 3

With 9 million records, the table somehow weighs a lot, because my table design is larger and with 30 million it weighed about 4 GB. I would say you need to look towards partitioning if you cannot add more RAM. It's strange what happens to you there if everything should be taken from the index. Maybe the request needs to be changed.

Answer 4

You can try setting innodb-log-file-size = 512M (innodb-buffer-pool-size / 4) and, as suggested above - innodb-flush-method = O_DIRECT and innodb-flush-log-at-trx-commit = 0 -- I advise you to read about this parameter before installing:
https://www.percona.com/blog/2007/11/03/choosing-i...
Well, if you started, then [paranoid mode on]And the tables themselves in what format? It should be: innodb_file_format=BARRACUDA[paranoid mode off]