0

I am trying to reduce the writes to disk and in my social site I have a lastactivity table which is updated with the time() a user last used the site. It is currently MyIsam:

id int(7) PRI auto_increment
lastactivity int(10) unsigned
username varchar(25)

it has about 100,000 records currently. If mysql is restarted I can just populate it again from the last logins in the main user table. The machine has a lot of spare memory, but this table is frequently locked I imagine because of all the updates performed on it which would be better if it was MEMORY table type?

bearhunt
  • 3
  • 1
  • for anyone curious, I decided to benchmark it with a natural mix of selects and updates. The selects were a little faster (even though it uses a range despite HASH index) but the updates were much faster. I will stick to memory but yes perhaps this data doesn't need to be in the DB at all and I could use something like APC to keep track of it. – bearhunt Mar 31 '11 at 12:10

2 Answers2

1

Running from memory is always faster than disk with the only draw-back that you'll lose your data in a power-out. However, you claim that you can regenerate your data if such a situation occurs. So, I agree that you should use your memory since you have gobs of it.

That said, I recall something that Knuth once said about data. Something about not having to do any computation at all, if you structure your data correctly. Smart data is better than a smart algorithm.

So, it might be prudent for you to look at how you may re-structure your database and/or application to reduce the amount of locking required?

Take a step back?

sybreon
  • 7,405
  • 1
  • 21
  • 20
0

Be sure to know the restrictions applying to in memory tables like the default index type which is HASH and not BTREE. It might be a good idea to review the queries using this table especially if some of them work with range queries. In the later case force constructing the indexes using BTREE rather than the default HASH.

olefebvre
  • 472
  • 3
  • 6