Mysql – How to analyze impact of a query on another queries

Since the selective predicate on the big table eav_value_text_data big table is v.value = 'rs145368920', you need an index on value more than anything else. The index on attribute is hardly relevant - only in combination with the first to allow index-only scans if possible:

CREATE INDEX eav_value_text_data_val_att_idx ON eav_value_text_data (value, attribute);

This should make a huge difference.
value should be the first column in the index. More:

• Is a composite index also good for queries on the first field?

Index md5 hash as uuid

Since it turns out that your value column is too big to be indexed and you decided to use md5 hashes instead (which is a good solution):

• Character varying index overhead & length limit

Consider storing the md5 hash as uuid, which would be most efficient (smaller, faster storage). You can just cast the result to uuid:

md5(value)::uuid

Details in the last chapter of this answer:

• Convert hex in text representation to decimal number

Related:

• Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
• How to add a PostgreSQL GIN index on a uuid in Postgres 9.6

Your index would look like this:

CREATE INDEX eav_value_special_idx
ON eav_value_text_data (cast(md5(value) AS uuid), attribute);

Note the explicit cast() syntax in the index definition:

• How to create an index on an integer json property in postgres

Your query can still use shorthand syntax:

SELECT count(*)
FROM   eav_value_text_data v 
JOIN   eav_attribute a ON a.id = v.attribute 
WHERE  a.data_type = 11 
AND    a.name = 'id' 
AND    md5(v.value)::uuid = md5('rs145368920')::uuid;

Short solution: The problem was IO-related. It was solved by moving to a new, more modern system with faster SSDs.

Longer version:

I invested a lot of time over several months trying to get to the bottom of the performance issue described above. As a stopgap measure, I did everything possible to prevent concurrent queries that caused a lot of IO (such as backups and search engine indexing). However, this never really solved the problem or answered the questions I posted -- it only covered up the underlying issue with one IO-intensive process blocking database activity.

My "answer," if you can call it that, was finally realized by moving to more modern hardware. Unlike the old system, which used consumer-grade Micron SSDs, the new server uses IBM datacenter SSDs and a faster processor. Otherwise, the software configuration is identical as in the system tested above (same OS, same use case for MySQL, same mdraid RAID-1 config, etc.).

I can only assume that the new system's better SSDs and/or more modern motherboard and chipset are better at handling concurrent operations. I no longer experience slow queries of any sort, no matter what's going on with the database server. Database operations do not seem to be affected by disk activity nearly like before.

So, if you're experiencing strange slowdowns with database queries and can't find any application-level reasons for this, don't forget to look at the underlying hardware as a possible source. Try renting a new dedicated server or VPS for a month from a different hosting provider, replicate your software configuration, and give it a try. Worked for me!