custom log system

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

custom log system

Sam-6

I'm about to put together a custom log system for our online store.

I was thinking of creating a table with these fields:
- date
- time
- order_id
- client_ip
- client_browser

This way I can quickly retrieve statistics for each day, month, year, etc.
with simple queries.

I know this table would grow quickly. I'm thinking about 50k rows each year
or so.

Here are questions:

1. How much will this slow down mysql?
2. Should I dedicate a table for each year (logs2005, logs2006, etc.) to
avoid large amounts of rows?
3. Or will I be better off using a flat-file system and write logs to plain
text file? It would be lot harder to search them though.
4. Any other suggestions/solutions for a similar custom log system?

Many thanks




--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: custom log system

Olivier Miossec

Sam ?crit:

>
> I'm about to put together a custom log system for our online store.
>
> I was thinking of creating a table with these fields:
> - date
> - time
> - order_id
> - client_ip
> - client_browser
>
> This way I can quickly retrieve statistics for each day, month, year, etc.
> with simple queries.
>
> I know this table would grow quickly. I'm thinking about 50k rows each year
> or so.
>
> Here are questions:
>
> 1. How much will this slow down mysql?
> 2. Should I dedicate a table for each year (logs2005, logs2006, etc.) to
> avoid large amounts of rows?
> 3. Or will I be better off using a flat-file system and write logs to plain
> text file? It would be lot harder to search them though.
> 4. Any other suggestions/solutions for a similar custom log system?
>
> Many thanks

The problem is that you in some moment mysql will need to write a lot of
data to mysql while trying to retreive data.
So you may use a database stored in an other HD or an other mysql server.
You can also use log tag to create a log file and import it into mysql
every day or every week (just create a simple cvs file and use LOAD DATA to
import this file)


---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--

Need a Lasso consultant ?
(Lasso, Java, Databases Consultant ...)
http://www.lasso-developpeur.net/oliviermiossec/

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: custom log system

Fabrizio Carioni
In reply to this post by Sam-6
Ciao.

I do the same kind of stuff, been doing that for over a 2 years and
Lasso is handling that very well.
It really depends on how much visitors you get.
If you say 50k a year you are tralking about average 136 records a
day, which is close to nothing for Lasso and Mysql.

What we do is to log different actions: Logins, failed logins, add
product to cart, delete from cart , access order form, close order
etc.

My table looks like this:
date_time
ip
client
user_code (if available)
permaanent_cookie (if available)
action (add to cart, order step 1, etc)
custom_field1
cusstom_field2
...

I use the custom fields to store data specific to some actions.
Eg. For actio "add to cart" I store the product code.

We get 3/5k visitors a day on a G4 800, all pages Lasso driven, Lasso 6.
With no problems or slow downs.
This just to give you an idea of a real world example.

HTH




>I'm about to put together a custom log system for our online store.
>
>I was thinking of creating a table with these fields:
>- date
>- time
>- order_id
>- client_ip
>- client_browser
>
>This way I can quickly retrieve statistics for each day, month, year, etc.
>with simple queries.
>
>I know this table would grow quickly. I'm thinking about 50k rows each year
>or so.
>
>Here are questions:
>
>1. How much will this slow down mysql?
>2. Should I dedicate a table for each year (logs2005, logs2006, etc.) to
>avoid large amounts of rows?
>3. Or will I be better off using a flat-file system and write logs to plain
>text file? It would be lot harder to search them though.
>4. Any other suggestions/solutions for a similar custom log system?
>
>Many thanks
>
>
>
>
>--
>------------------------------
>Lasso Support: http://support.omnipilot.com/
>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>Manage your list subscription:
>http://www.listsearch.com/lassotalk.lasso?manage


--
======================================================================
Fabrizio Carioni - Golem100
S.r.l.                                                                                
Via Gran Sasso, 50 - 20090 - Segrate (Mi) - Italy
Voice +39-02-2133402  -  Fax +39-02-2132105  - Mobile 3356463448
Email [hidden email] - URL http://www.golem100.com/
======================================================================

--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: custom log system

Mark Palmer
In reply to this post by Sam-6
I do something similar for each page requested but store 15 fields and add
8,000 to 20,000 records per day. Approximately every 3 month I push the
oldest 3 months into another table.

MySQL doesn't appear to have slowed down at all. Works really well and being
able to link my access log to my users table through my user_id mean I can
produce all sorts of useful reports.

One thing I do that has proved interesting is the add the page build time to
the log.


on 31/5/05 00:59, Sam at [hidden email] wrote:

>
> I'm about to put together a custom log system for our online store.
>
> I was thinking of creating a table with these fields:
> - date
> - time
> - order_id
> - client_ip
> - client_browser
>
> This way I can quickly retrieve statistics for each day, month, year, etc.
> with simple queries.
>
> I know this table would grow quickly. I'm thinking about 50k rows each year
> or so.
>
> Here are questions:
>
> 1. How much will this slow down mysql?
> 2. Should I dedicate a table for each year (logs2005, logs2006, etc.) to
> avoid large amounts of rows?
> 3. Or will I be better off using a flat-file system and write logs to plain
> text file? It would be lot harder to search them though.
> 4. Any other suggestions/solutions for a similar custom log system?
>
> Many thanks
>
>
>


Regards


Mark Palmer, Pageworks

T: 01902 620500            F: 01902 620440
E: [hidden email]    W: www.pageworks.co.uk



--
------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage