Mysql Scaling for small app

I can not buy more hardware. I have 2gig ram for mysql.

Now my widget is being displayed on 57 websites with 3000 views on average. My widget requires 200 insert per view for the best user optimization of the content. which means on average there are almost 400 inserts per second.

Now what do you suggest I do to speed up the inserts. The data inserted is divided in the three tables with two of them getting 80 each and the last one getting 40 inserts.

I am using MYISAM, I dont like INNODB because I have never used it, nor I am cormfortable using it right now, but a good reason may persuade me to. The problem is that at peak times there are around 1200 inserts per second, which is a real bottleneck.

Mainly the queries are updates to increase the impressions. So update impressions = impressions + 1 where widget=widget_id

Any suggestions to speed up开发者_如何学运维 the database queries would be fantastic.

Also can there be a cache for the inserts, for example running inserts in batch. Something like that. Timely inserts, bulk inserts, etc. BTW, I would really appreciate if this could be explained in details, since I am a complete noob.

Thanks Guys

It's in your best interest to consider InnoDB. InnoDB is where MySQL behaves most like other proper relational databases and provides the most reliability and functionality. In your case the biggest immediate drawback to MyISAM is that the entire table is locked every time a row is inserted or updated. While MyISAM is very fast for small scale projects, if you have a lot of inserts going on that is likely to be a bottleneck eventually if it is not already. You will likely later also want the proper foreign keys, transactions, etc. that InnoDB provides.

You should also read over the MySQL docs and the articles at MySQL Performance Blog for tips on optimal configuration of MySQL regarding buffers and other configuration options.

MySQL Tuner can also be helpful in figuring out what you really need. It will show stats such as percent inserts and percent selects, how much memory is being used for various buffers, how your query cache is performing, and so on. It also gives tips as to what to tweak to improve these things, although those tips should be taken with a grain of salt and researched before you actually do them.