MySQL Performance: MySQL => Instanteous Reporting = HEAP + MyISAM?

Instanteous Reporting = HEAP + MyISAM? [message #1027]

Thu, 05 April 2007 21:01

tsowden
Messages: 3
Registered: April 2007

Junior Member

Hi,

I'm in the early stages of developing an application that basically records web site statistics / analytics for reporting. I am required to have up to the second reporting for these numbers. I have multiple columns with plenty of information, making group by & order by ridiculous when using InnoDB on 500,000+ rows of data.

My current plan is to split the data in the following way: 1 table for all information which is InnoDB this is for proper accounting, 4 tables with summary information. When a visitor comes to the website 5 inserts will happen, 1 to main, 4 to summaries. When the webmaster/site owner comes to view his statistics it hits the summary tables and shows instantaneous data!

What I'd like to do is store all this summary information in a MEMORY-type table (from 10-200 rows each) I have been experimenting and this is working quite well on a small scale. 250kb each memory table so 1MB per website. I'm worried about server crash/ mysql restart situations that will clear these summary tables.

Is there a good way to flush these heap tables to disk, or just create a cron to do it manually...?

Any ideas or suggestions here would be great, as I said I'm in the beginning stages, so if I'm on the wrong track let me know!

Thanks,
Tob

EDIT: btw, my solution needs to be rather scalable - probably have 1000+ websites doing millions of hits per day.

[Updated on: Thu, 05 April 2007 21:04]

Report message to a moderator