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

Members

Help

Home

Home » Performance » MySQL » Instanteous Reporting = HEAP + MyISAM?

Show: Today's Messages :: Show Polls :: Message Navigator

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

Re: Instanteous Reporting = HEAP + MyISAM? [message #1033 is a reply to message #1027 ]

Sat, 07 April 2007 05:39

Speeple
Messages: 91
Registered: August 2006

Member

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;

Martin Gallagher | Speeple: The latest news

Report message to a moderator

Re: Instanteous Reporting = HEAP + MyISAM? [message #1039 is a reply to message #1033 ]

Sat, 07 April 2007 12:52

tsowden
Messages: 3
Registered: April 2007

Junior Member

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

Report message to a moderator

Re: Instanteous Reporting = HEAP + MyISAM? [message #1041 is a reply to message #1027 ]

Sat, 07 April 2007 14:50

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

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 Smile

Report message to a moderator

Re: Instanteous Reporting = HEAP + MyISAM? [message #1042 is a reply to message #1041 ]

Sat, 07 April 2007 18:41

tsowden
Messages: 3
Registered: April 2007

Junior Member

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

Report message to a moderator

Re: Instanteous Reporting = HEAP + MyISAM? [message #1043 is a reply to message #1027 ]

Sun, 08 April 2007 01:39

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

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.

Report message to a moderator

Re: Instanteous Reporting = HEAP + MyISAM? [message #1073 is a reply to message #1027 ]

Wed, 11 April 2007 20:42

katrinam
Messages: 1
Registered: April 2007

Junior Member

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

Report message to a moderator

Previous Topic:	MySQL poor MyIsam UTF8 performance
Next Topic:	no topic

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 10 06:52:06 EDT 2009

Total time taken to generate the page: 0.00564 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.