It’s really person dependent. I really mean it, and a lot of folks do. Additionally if you said “not good,” most people will be caught off guard but pivot into sympathy and asking what’s wrong etc. It’s a perfectly acceptable answer. A key thing though is to make sure it’s appropriate to the moment. If my boss asks me how I’m doing I’ll tell them if it’s not good in some way related to work or my performance (I.e., “not good, I can’t get this to compile” or “not good, my mom died I need to take time off,” or even, “not good, I didn’t sleep well last night.”) for friends the “not good” can be deeper, and for family it’s pretty open. For strangers, I still might say “not good” if something particularly acute is happening (“not good, my mom just died,” “not good, I just got out of the hospital this morning.”) I’ve never had someone get uppity about a “not good” response, and have always had an appropriate pivot to sympathy and a refocus on the question.
As such, I’ve always found it odd people narrow in on the “how are you” question being perfunctory and people don’t genuinely care. They routinely ask it and generally expect “good thanks” but react appropriately to other answers.