domain_traffic table growing huge

[ispcomm]

Hi all,

I have a domain_traffic table which is getting several gigabytes big. Is this normal?

It's causing the server to be unresponsive for up to a minute time when a reseller logs in (or when admin becomes a reseller with 'change').

I traced down the problem to being this SQL query:

SELECT /*!40001 SQL_NO_CACHE */ * FROM `domain_traffic`;

which is executed on each reseller login. As domain_traffic is several gigabytes on this particular server (yes... it's a busy worker) you can guess how long it takes.

Is this normal?

ispcomm

[ispcomm]

Nobody has this issue?

I have written some code to summarize data from the table to keep it short. If anybody is interested I could post some info here.

ispcomm

[ephigenie]

hi,

great - any suggestions and better solutions are always welcome.
Best practice would be to open a ticket with that issue and append your code there.
We'll see then if this could be integrated.

[ispcomm]

ephigenie Wrote:great - any suggestions and better solutions are always welcome.

This is nice to know. I am using vhcs2 (modded heavily) for real hosting and I don't like to track my own mods and an external repository. I prefer to try to get my changes in the trunks (as usually they solve a real world problem I had and are usefull to others).

ephigenie Wrote:Best practice would be to open a ticket with that issue and append your code there.
We'll see then if this could be integrated.

I created a ticket: http://www.isp-control.net/ispcp/ticket/1232 and posted my hints on how to handle this. I didn't go as far as mofifying the engine (mods are trivial for a dev). I did test my mods before posting.

Hope this can get into trunk at some point.

ispcomm.

[kilburn]

Somehow also related to ticket #940, this problem would also be solved by using vlogger to handle the apache logs as it allows you to keep track of per-vhost traffic usage in a myql db.

My proposed solution plan:

1. 1. Remove EVERY current log declaration in the apache config (including vhosts ones)
2. 2. Add two new CustomLog directives at the "master" apache level (one for error, one for access) and pipe it's output to vlogger
3. 3. Set up vlogger to split & rotate files on a daily (maybe monthly?) basis
4. 4. Set up vlogger to count traffic usage and store it to a new database / table in ispcp's database
   
   Code:
   

   CREATE TABLE WWWUSAGE (
        VHOST   VARCHAR(128) NOT NULL,
        -- YYYYMMDD format, so we cannot use ispc's traffic table as is
        LDATE   INT(8) UNSIGNED NOT NULL,
        BYTES   INT(32) UNSIGNED NOT NULL DEFAULT '0'
);
5. 5. Disable the ispcp's web traffic manager
6. 6. Create a new script (single SQL sentence in fact) to update the current traffic table using the values from the new one
   

What do you think ephigenie, ispcomm, rats, ...? Do you want me to execute this plan?

[sci2tech]

I also studyed this problem (hurts me as hell becose i must use mod_php insteed fastcgi). Seems that are 3 options vlogger, cronolog and asql. As far as i studied vlogger seems the best solution. Don`t know perl enough to help. For me i`ll try a php aproach, but it will be nice to have a solution integrated in ispcp

[ispcomm]

vlogger is the easy approach (very similar to a solution I wrote back in the '90s, where I had a single log file for all vhosts and a splitter that created the per-vhost files in a cron job run a night).

But I would evaluate better mod_log_sql as there are provisions in it for handling mysql server down times via a temporary local log file (pls. correct me if I'm wrong... the plan of gplhost was this). Also provisions are there for logging mail traffic.

In the above ticket I have summarized what needs to be done to reduce the size of the domain_traffic table. I have a cron job that runs at night and summarizesd data every day for any record older than 1 month. My tables went down to few hundred thousand rows. In case you need more optimization you can summarize anything older than 1 day.

ADDED: Seems thigs are little different since last time I checked the sql options. sorry for being imprecise above with pointers etc. The main idea still applies.

ispcomm.

[kilburn]

I prefer a file-based logging solution for multiple reasons:

1. Logs can be stored with the users data (/var/www/virtual/user), counting on disk quota and easing backups.
2. It gives better performance.
3. No network-related errors (mysql server could be down, you could severly drain bandwidth, raise latency, etc.)
4. Almost everything is logged the same way

I think that your proposal is better for a grid of a couple machines, but not on a single one (wich I guess is the most used configuration of ispcp as of now).

What would you think about file logging and (optional) file-to-sql feeding with aggresive rotation. This way you get the best of both worlds...

[ispcomm]

File logging by default is OK for me. That can always be changed with a proper "parts" config for apache for more specific configurations (like multi-server as you mentioned).

I like the idea of gplhost because it suits me better (specially when you have a geo-distributed infrastructure), but I agree that for a single server file logging is better.

I woudn't use vlogger then but simple plain combined-compatible file with the %V (or was it %v) directive that prints out the "vhost" in the logs. A nightly (perl) script can easily split it in 200-300 different files and perhaps update the databases.

When daily rotation becomes a problem, a hourly rotation can be implemented.

You loose the "real-time" logs for the customers: Logs will be available the next day or the next hour, but this can be accepted for a shared hosting account. If a customer needs real-time logs they can be pushed to a vps.

ispcomm.

[kilburn]

I'm trying to find a suitable solution for real-time curstomer logs because the "best practice" in php is to disable error displaying on production servers. Now the use case:

- user.tld uploads his website to our server
- user.tld loads his website using a browser
- user.tld gets a nice all-blank page
- user.tld can't see any error in his error log (he has to wait for the next rotation)
- user.tld calls the support center and gets angry because having to wait one hour between every fixed error is unacceptable for him

Two ways from here:

1. (Bad support) tells him that this is a server issue, nothing can be done about it and user.tld gets another hosting provider.
2. (Good support) tells him to use the display_errors directive, wich partly solves the issue (he will be able to see runtime errors, but not startup ones) and user.tld may or may not get another provider.

By the way, why won't you use vlogger? Any advice against it? (I haven't tried it so there may be drawbacks I don't know!).

sci2tech Wrote:... For me i`ll try a php aproach, but it will be nice to have a solution integrated in ispcp

Have you done anything on this front? I'm writing "my own vlogger" as a coding exercice, but don't really know if it's performance (in-memory size, cpu consumption) will be suitable for high load webservers.