Instanteous Reporting = HEAP + MyISAM?

[tsowden]

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.

[Speeple]

ALTER TABLE stats_memory RENAME TO stats_archive, ENGINE=InnoDB;

Or to get more control over the contents of the stats_archive table something along the lines of:

CREATE TABLE IF NOT EXISTS stats_archive LIKE stats_memory;
INSERT INTO stats_archive SELECT * FROM stats_memory;
DROP stats_memory;

[tsowden]

Thanks for your reply Speeple.

I am really starting to like this solution. Like I said, I will have 4 or 5 tables in memory and I think I can easily archive them to disk in 1 archive table, indexed by a couple things. If server ever crashes I can quickly start rebuilding the memory tables from the last archive.

Now I have another problem.

I’ve been experimenting with MEMORY tables, and it seems they are table locking instead of row locking (innodb), any work around here? Maybe a different engine. I thought of using memcached but it is flushed out of ram every time there is an update to the table, obviously, this wont work.

Thanks again,
Tob

[sterin]

What about InnoDB for the summary tables also?

The good thing about that is that you can put the 1 insert and 4 updates within a transaction and know that you have consistent data in your database.

And since these are really small tables and very often accessed they will be in memory due to InnoDB caching mechanism.

This is what I would recommend you to begin with.
And if you still feel that you have performance problems then you can turn to find some other smart solution.
KISS is a very good thing (until it is actually proven to be too slow that is ) ).

[tsowden]

Hi Sterin,

How can I be assured that these tables are cached? Is there a setting/variable I should boost to be guaranteed that the summary tables get into memory?

Thanks for your thoughts.

Tob

[sterin]

You should set your innodb_buffer_pool_size server parameter to about 80% of the available RAM.
This is the innodb internal cache that handles both index and table data cache.

It uses LRU = Least Recently Used algorithm for deciding which data should be moved out of cache and written to disk.
So if these small tables are the ones mostly used they will basically always be in the cache.

[katrinam]

Tobyn,
I think you have a great idea here with a lot of potential.
I specialize in this area so any time you have questions just give me a shout.
Keep up the innovative ideas - they will pay off!
Wish I had a mind like you, working for me!
Take Care,
K cool: