Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from programmers.stackexchange.com Aug 20 '11 at 18:29

This question came from our site for professional programmers interested in conceptual questions about software development.

2  
Have you tried storing data in memcache and then flush it in one transaction every few seconds? –  Daniel Iankov Aug 20 '11 at 14:31
2  
"data changes like 200 times/seconds for this particular table" I think that line spells out this data should be held in memory, the lifetime it has to persist for is tiny so probably shouldn't go to disk? –  Greg K Aug 20 '11 at 14:38
    
Indexes come and go? I can't think of any reason why you'd need to create and drop indexes very often. –  Barry Brown Aug 21 '11 at 21:59
add comment

3 Answers 3

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.

share|improve this answer
4  
+1 for suggesting a solution which keeps the data in memory. –  Falcon Aug 20 '11 at 12:44
add comment

Here is a crazy idea. It involves assumptions and not always recommended practices (like updating a key) - I will get lots of negatives for suggesting this but here it goes...

Assuming that you have a very high volume of rows and high volume of deletes, you can enhance the delete performance by creating 2 partitions on your table. The partitions would differ by the first digit of the key. Example:

Key value 1123234441 is for active rows and key value: 9123234441 is for inactive rows (first digit in this example is used as follows: 1=active, 9=inactive).

Now when the user deletes a row, you don't physically delete the row, you update the key (Ouch!), this would automatically move the row to the inactive rows partition.

Of course, you need to restrict your selects to read data from the active partition only. Now the cool part is that dropping the inactive rows partition is extremely quick.

As I said earlier, this works if you have only 1 table. I have not tested this, so it is just a theoretical approach but I have experienced the speed of partition dropping and it is amazingly quick.

To enhance your selects, use proper indexing and to enhance inserts minimize row size and number of indexes (this statement is very generic...)

For a reference see: http://dev.mysql.com/doc/refman/5.1/en/partitioning-types.html Hope this helps.

share|improve this answer
2  
I'm not sure, if this makes sense for this specific problem (My guess still is, that mysql will cache the whole thing and most likely those records will not see see a disk ever). But +1 for pointing out an interesting optimization technique I didn't know by now. –  thorsten müller Aug 20 '11 at 13:04
add comment

One thing that has to be taken into consideration is how MySQL uses buffers for its major storage engines: InnoDB and MyISAM.

What lies cached in memory differs greatly between these storage engines.

InnoDB caches both data and index pages. They are loaded into the InnoDB Buffer Pool, which is sized by innodb_buffer_pool_size.

MyISAM caches only index pages and they are loaded into the Key Cache (Key Buffer), which is sized by key_buffer_size.

You must use information_schema.tables to get the data and index sizes occupied on disk in order to size the InnoDB Buffer Pool and MyISAM Key Cache correctly.

Depending on on how much data you have and how much time you will allow, you can warm the caches as follows:

For every table TableT

  • goto to each index NDX
  • for each index NDX
    • run SELECT every column in NDX,at least one column not indexed in TableT from TableT

By doing this you guarantee that every data and index page gets read at least once. They will sit in the cache. This concept is practiced, in part and in principle, by Percona. Percona built this concept into mk-slave-prefetch. What this program does is

  • read relay logs on a slave ahead of the slave processing the SQL in it
  • take an SQL statement from the relay log and convert it into a SELECT utilizing the WHERE, GROUP BY, and ORDER BY clauses as a guide to choosing indexes
  • execute the SELECT statement that came from converted SQL

This forces the slave to have 99.99% of the data needed by the slave to process the SQL quickly. This also makes the slave prepared in the event you manually failover to the slave and promote it to a master WHOSE CACHES ARE JUST ABOUT THE SAME AS THE MASTER YOU FAILED OVER FROM.

CONCLUSION

Nothing beats having caches ready, willing, and able for you to use in an environment of heavy INSERTS, UPDATEs, and DELETEs.

Give it a Try !!!

CAVEAT

With the birth of products like memcached, some have gotten away from the need to perform proper tuning of MySQL. Granted, many sites benefit from the boost in data retrieval provided by controlling the caching behavior of data as developers have quickly seen with memcached. Many other sites, just by switching storages engines or correctly configuring MySQL, have realized the same performance benefits. Before giving up on the database and strictly using it as a repository, make the most of the your database. Follow through on the due diligence and you might be pleasantly surprised what MySQL will do for you.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.