What are some likely causes of high Client:ClientRead AAS in AWS RDS? I have a particular query, its a simple select with an inner join. Seems to be pretty well indexed. But it's registering as a worst offender in performance insights. With a 0.13 on CPU and a 4.5 on Client:ClientRead.
All the documentation and articles I can find indicate this is common for bulk data copying. But I don't think this is the case. Since it's such a basic select. It's being called around 70 times per second. Which isn't even that high compared to others I have in the 500 calls per second, and it's only serving back about 7 rows, 10 columns each.
So other than bulk data copying, what other factors might be giving me a high AAS for clientRead?
Posting an answer in the hopes that it helps anyone else seeing this issue. Client:ClientRead is best summarized as the time waiting on the client to read the data, and can include not just time transmitting but also time waiting for the client to be ready to transmit. Important to note for NodeJS users.
NodeJS, for better or worse, has that single-threaded event loop. So when we opened up our new app to a few hundred users and were still only using a single NodeJS replica of the service, that one service was queueing hundreds to thousands of events to loop through. So it would end up queuing all of these async calls to the DB and while it was working like crazy, it started taking seconds to get around to the callbacks, and that time was stacking up under this Client:ClientRead statistic.
As soon as we started firing up replicas of that service, that statistic dropped drastically, and we started seeing DB CPU waits be our problem. That's outside the scope of this question so I won't go in to it. But it's good to know the Client:ClientRead, when not dealing with transmitting large datasets back to the client, is likely explained by the calling service taking it's time to get back around to reading the data. If you're using Node in a K8s environment, it's likely time to scale up your replicas.