I can't envision too many users relative to other objects in the system. My guess is if this is really a problem then it is a database issue such as too little memory allocated forcing the use of temporary tables.
Database server is dedicated with 4gb ram, I haven't seen it spike above 25% usage. Are there setting in the config or somewhere that can be adjusted to not use temp tables?
Database server is dedicated with 4gb ram, I haven't seen it spike above 25% usage. Are there setting in the config or somewhere that can be adjusted to not use temp tables?
There are user generated temp tables (I.e. for sorting and temporary storage) and then mysqld itself will create tmp tables if/when it can predict memory shortfalls or determines it would be more efficient. If you have a 4GB dedicated mySQL server, you should be fine. My next question would be about the connection between your web-server (cs-cart controllers) and your DB server.
If you truly have a dedicated DB server, then you should have about 50% dedicated to mysql and the remaining memory dedicated to system and file buffers since you would have no user interaction/usage on this server. Then TMP tables would be created on disk, but cached in memory for use.
I guess I wouldn't make assumptions about the source of the problem until the needed investigation/diagnostics have been done. Caching users may not solve you problem. It is not clear how you are triggering the issue. Is it from customer interactions with your site or as Flow indicated, admin operations?
In cs-cart tmp tables are generally used for sorting (as they should be) of data that is "joined" from many different tables and where the sort critera can spread across those joins. Otherwise tmp tables are not generally used in cs-cart from what I've seen.