When does a slow MySQL query on a given connection affect other connections?

Question

I think I have a basic understanding of this, but am hoping that someone can give me more details as I am interested in learning more about database performance.

Lets say I have a very large database, with many millions of entries, the database supports many connections. Doing simple queries on the database will be slow as there's so much data. I'm trying to understand exactly when a query on a given connection starts to have a direct effect on the performance of queries running on other connections.

If one connection locks some elements, I understand that that will hold up queries running the other connections that need those elements . For example doing:

SELECT FOR UPDATE

will lock what you are selecting.

What happens when you do something simple like:

SELECT COUNT(*) FROM myTable

lets say we have a table with a billion rows so running the count is going to take some time (running on innodb). Will it affect queries running on other connections?

What if you select a large amount of data using SELECT and JOIN, like:

SELECT * FROM myTable1 JOIN myTable2 ON myTable1.id = myTable2.id;

does having a join lock anything for other queries?

I'm finding it hard to know which queries will have a direct effect on the performance of queries running on other connections.

Thanks

Answer 1

There are different angles:

• Row locking: this shouldn't happen if you tune your architecture, so you should forget about it
• Real performances issues and bottleneck. In our case, collateral effects.

About this second point, the problem is mainly divided in 3 areas:

• Disk reads
• Memory usage (buffer)
• CPU usage.

About disk reads: the more data (in bytes) you will retrieve, the more the harddrive is going to be busy and slowdown any other activity using it. Reduce the size of selected rows to avoid disk overhead.

About memory usage: mysql manages an internal buffer, that can get stuck in some situations. I don't know enough about it to give you a proper answer, but I know this is definetly something you should keep an eye on.

About cpu usage: basically the cpu will get busy when it

• has to calculate (joins, preparing statements, arithmetics...)
• has to do all the peripheric stuff: moving bytes from disk to memory for instance. Optimize your queries to reduce cpu overhead. (sounds silly but, well, it always turns out to be the problem anyway...)

So, now when to know when there's a collateral effect? By profiling your hardware... How to profile?

• absolute profiling: use SHOW INNODB STATUS or SHOW PROFILE to get useful informations about main mysql harddrive, cpu and memory watches.
• relative profiling: use your favorite OS profiler. Under windows xp for instance, you can use the great perfmon.exe and watch for PRIVATE BYTES and VIRTUAL BYTES of the mysql process. I say relative, because afterall if a query is time consuming on your computer, it might not be on the NASA system...

Hope it helps, regards.