Have you moved to a VPS already, I remember you were going to.

It would be good to know the queries that take long time to execute. It might be some buggy/heavy plugin or hit logging query. How many days of stats do you keep? Also I've had some mysql errors in hitlog table in version 4.0-alpha. You should update to 4.0.5

I moved to a VPS, then had to move to a dedicated server, tried to go back to a VPS, and then just yesterday went back to the dedicated server. I am told I kept crashing the VPS.

I will do the upgrade this weekend and see if that helps. And I will see if they can tell me more about the slow queries. Thanks!

What were the specs of your VPS? I can't believe that one b2evo site can crash it.

I can, and I'm damn sure Tilqi can ... server loads in excess of 600 can crash a vps quite easily ;)

Check the size of your hitlog table

¥

I'm not talking about a VPS with 256M ram and a single 800Mhz CPU.

I run a pretty busy b2evo blog on 1G ram and 4 cores XEN VPS, and I can easily fit another 2 blogs like that.

Other factors are very important too, the server must be properly configured, of course.

I'm on about a vps that has the following specs

reserved 2gb ram burstable to 8
reserved single 2.6 cpu burstable to quad core
huge swap, but swap is pointless as it's hdd and the moment you hit swap you're fckd anyway
only 1 site on the vps and it's evo 4

crashed regularly.

How many records do you have in your hitlog sam?

¥

One week of stats

sessions - 60 000
hitlog - 110 000
keyphrase - 200 000
comments - 20 000
items - 2 300

I run nginx/php-fpm/apc & mysql 5.1

Mysql is the bottleneck here, that's why I increased caches so it eats 40% of total ram (which is good since it operates a lot faster)

Feel free to PM me if you need help tuning up your VPS or Dedicated server ;)

Try millions of records ;)

¥

[edit]
Aside : your vps ( at OP ) shouldn't have brought down the main hardware though. Most it should have affected was just your vps
[/edit]

[edit2]Sam,
We've got a (non-evo, it's a custom built directory) vps on the same box that takes the equivalent of your stats on a daily basis that has never had a problem ( barring when we had a physical ram failure ... but that shagged all 3 vps's :P )

100% agree it's an sql problem, but not a "daily" one ( if that makes sense )
[/edit2]

@¥åßßå
I did have millions of records at winter holidays last year on the same VPS and b2evo website, and it was working just fine. I showed you current stats from phpmyadmin.

@cslepage
You can monitor slow queries on your own, ask google. Then post the queries here.

Were you running evo 4 and INNODB etc then?

Reason I asked you originally was because I knew you had a busy site ;)

¥

Yep. It was some early-alpha version of b2evo 4 :) and I'm using innodb since v3 if I'm not mistaken.

My images are hotlinked on many websites (bastards) and apache is not an option if you don't use external server for static files. Nginx handles that easily and I'm not going to stop hotlinking in the near future because it doesn't add much load and I don't care about traffic.

Wonder if it's a later evo 4 thing then?

Tilqi runs a release version

¥

I upgraded to release version just a couple weeks ago and don't see any difference in load. I was running that pre-alpha version for a long time.

Does he use b2evo page cache and APC? I also tuned up kernel vars a bit, and have a software firewall that blocks various muppets :)

Have you run millions of records since you upgraded to stable?

Yep at caching ... the death is intermittent ( 5 > 7 day cycle ) ... which is what makes me think it's summat like evo pruning stats

Signs are, traffic hits a spike, sql overloads, php piles up, swap starts getting used, loads increase and shit rapidly hits the fan.

Only happens if traffic spike coincides with something else, and that seems to be time based,, and that's the bit I'm trying to narrow down ;)

Killing hitlog has (so far) helped

Fact is, we have another vps that takes way more traffic/hits on a daily basis and shows zero signs of getting warm, never mind breaking into a sweat. It doesn't run evo but it does make heavy use of mysql as it's a directory ( with several hundred thousand active members ), mind you it also uses sphinx to reduce the mysql impact

¥

Have you run millions of records since you upgraded to stable?

No

Hit pruning queries are very light. A query like this should not crash MYSQL even with huge tables

delete from x where y < z

This might be a problem on large tables, though.

$rows_affected = $DB->query( "
			DELETE T_basedomains
			  FROM T_basedomains LEFT JOIN T_hitlog ON hit_referer_dom_ID = dom_ID
			 WHERE hit_referer_dom_ID IS NULL
			 AND dom_type = 'unknown'
			 AND dom_status = 'unknown'" );

I'm creating a test b2evo install with 10M hits and 100K referred domains.

I'll post the results when I'm done testing various delete stats methods.

sam2kb wrote:

This might be a problem on large tables, though.

$rows_affected = $DB->query( "
			DELETE T_basedomains
			  FROM T_basedomains LEFT JOIN T_hitlog ON hit_referer_dom_ID = dom_ID
			 WHERE hit_referer_dom_ID IS NULL
			 AND dom_type = 'unknown'
			 AND dom_status = 'unknown'" );

That's the kind of thing I meant by pruning, truncating a table takes fraction of a second ;)

sam2kb wrote:

I'm creating a test b2evo install with 10M hits and 100K referred domains.

I'll post the results when I'm done testing various delete stats methods.

Thanks :D I'd be very interested in the outcome of that.

¥

A query like this should not crash MYSQL even with huge tables

delete from x where y < z

I was wrong! The query like that IS taking forever to delete 10M rows. Mysql is locking a table on every DELETE statement.

The only workaround I found is to break the hitlog table on partitions by day. And then add new and prune old partitions (not individual rows) on schedule.

Then deleting 7 days of stats is a matter of unlinking a partition (file) and takes no more than 1 second, while individual rows get deleted at the rate of ~7000/sec on my test laptop.

For those who wants to run tests too :)

Create 10K referred domains

DELIMITER //
CREATE PROCEDURE domains()
BEGIN
DECLARE var INT DEFAULT 1;
varloop: WHILE var <= 10000 DO

INSERT INTO evo_basedomains (`dom_name`, `dom_status`, `dom_type`) VALUES ( CONCAT("test", var, ".com"), "unknown", "unknown");

SET var = var+1;
END WHILE varloop;
END //
DELIMITER ;

Create 1M hits from those 10K domains

DELIMITER //
CREATE PROCEDURE hits()
BEGIN
DECLARE var INT DEFAULT 1;
varloop: WHILE var <= 1000000 DO

INSERT INTO evo_hitlog (`hit_sess_ID`, `hit_datetime`, `hit_uri`, `hit_referer_type`, `hit_referer_dom_ID`, `hit_remote_addr`)
VALUES (1, NOW(), CONCAT("/some-not-very-long-uri-", FLOOR(1 + (RAND() * 9999))), "direct", FLOOR(1 + (RAND() * 9999)), "127.0.0.1");

SET var = var+1;
END WHILE varloop;
END //
DELIMITER ;

mysql> call domains();

mysql> call hits();

call hits(); will take a while to execute. My laptop spent about 20 minutes on that procedure call :)

sam2kb wrote:

I was wrong! The query like that IS taking forever to delete 10M rows. Mysql is locking a table on every DELETE statement.

Try changing the tables to myisam to avoid row locking ... it shouldn't help much ;)

¥

Hit log tables are set to myisam already. When I converted the table to innodb, delete query was running ~6 times longer than on myisam.

Anyway I decided to play with partitions, this is not only for b2evo since I have a hand-written script that prunes monthly stats on schedule, and there are millions of rows.

If you ever find the bottleneck let me know ... keeping hitlog small is the only solution I've currently found ( admittedly I've not chased to hard because I'm only interested on Tilqi's behalf )

¥