Re: Utilize maximum system resources for better performance

[Rick James]

Are content and content_description one-to-one?
Are Users and content_description currently empty?
What are the indexes of Users and content_description?

| innodb_buffer_pool_size             | 1073741824     |
| key_buffer_size                     | 2411724800     |

and you have 32GB of RAM?
I see multiple notes in this thread on those two settings; I recommend
innodb_buffer_pool_size = 10G
key_buffer_size = 500M
until you convert all tables to InnoDB. At that point, change to
innodb_buffer_pool_size = 22G
key_buffer_size = 40M

> No, not complete replacements, only fields seeders, leechers, verified, category and last_updated, which gets updated daily .

Would it make sense to have those columns in a separate table? This would make the nightly processing simpler and more efficient?

6.5M UPDATE statements must take a long time.

LOAD DATA into a `new` table would be orders of magnitude faster, then
RENAME TABLE `real` TO `old`, `new` TO `real`;
DROP TABLE `old`;
would instantly and atomically update that table -- _zero_ downtime.

(OK, I don't know if there is an issue with the hash.)

The QC, at least for that table, will be purged every _minute_ because of the cron job. If users fetch the same stuff repeatedly in 59 seconds, then the QC is somewhat useful.

> you mean count it once a day and the store it in another table i guess.

Yes.

> reading 6M rows , i guess those are queries fired by sphinx indexer ,as it requres to laod all data to create index.

Ouch. Do you know when that is happening? Does it have an adverse impact on other things?