patternsqlMinor
MySQL High Performance for Lots of SELECTs/INSERTs/UPDATEs/DELETEs
Viewed 0 times
insertsdeleteshighlotsmysqlforperformanceselectsupdates
Problem
I am creating a module where every user often gets a record into a table for 10 to 300 seconds.
When the time expires a record gets deleted. The case is: there will be a lot of users and records will change really often - how this will affect application's performance for this table, because records will change really often and I am wondering if mysql is fine with that? Like indexes would come and go, data changes like 200 times/seconds for this particular table. Maybe I am choosing a bad solution for this kind of job. Any suggestions ?
Thank you!
When the time expires a record gets deleted. The case is: there will be a lot of users and records will change really often - how this will affect application's performance for this table, because records will change really often and I am wondering if mysql is fine with that? Like indexes would come and go, data changes like 200 times/seconds for this particular table. Maybe I am choosing a bad solution for this kind of job. Any suggestions ?
Thank you!
Solution
If that's a bad solution would depend on many things. Does this data need to be persistent? Otherwise maybe a solution that simply keeps this data in memory would work better.
"A lot of users" isn't really helping anybody. MySQL will most likely be fine if "a lot" would mean a few hundreds. (Though depending on what else your database has to handle. Several thousand should most likely work too.)
After all, it doesn't matter that much, if you write those records to stay or delete them after a few seconds to minutes. Deleting just makes two operations out of one. And MySQL can for sure handle a very large amount of creating and deleting records. Make sure you use a simple index to find those records again for deletion.
But without actual numbers and some information about the hardware your database server uses, that can't be answered with much precision.
Best thing would be to write some small application, that simply simulates the amount of load you think you will get without doing much real processing, just drop a lot of records against the server, delete them, at the same rate run some queries like the rest of your program would generate. Have a look at your server and see, if that affects it in any way.
Not sure, but there are options to set for MySQL that would allow it to cache a table in memory completely.It does this anyway in many situationsand most likely you won't have to change much. But if you talk about a really very large amount of users and records, you can maybe tweak a few parameters to optimize caching for your special needs.
"A lot of users" isn't really helping anybody. MySQL will most likely be fine if "a lot" would mean a few hundreds. (Though depending on what else your database has to handle. Several thousand should most likely work too.)
After all, it doesn't matter that much, if you write those records to stay or delete them after a few seconds to minutes. Deleting just makes two operations out of one. And MySQL can for sure handle a very large amount of creating and deleting records. Make sure you use a simple index to find those records again for deletion.
But without actual numbers and some information about the hardware your database server uses, that can't be answered with much precision.
Best thing would be to write some small application, that simply simulates the amount of load you think you will get without doing much real processing, just drop a lot of records against the server, delete them, at the same rate run some queries like the rest of your program would generate. Have a look at your server and see, if that affects it in any way.
Not sure, but there are options to set for MySQL that would allow it to cache a table in memory completely.It does this anyway in many situationsand most likely you won't have to change much. But if you talk about a really very large amount of users and records, you can maybe tweak a few parameters to optimize caching for your special needs.
Context
StackExchange Database Administrators Q#4871, answer score: 5
Revisions (0)
No revisions yet.