More memory is needed ?

[Enc0der]

Hi.

I'm having some serious slow-downs on the peak hours of my forum. (SMF 1.0.5, PHP 4.4.0, MySQL 4.1.14-standard)
This is a very big forum (80K page views/day, 200+ users online 24/7, and around 900 messages per day with a total of more then a million..) using a dedicated server running on Dual Xeon 2.4GB and 2GB DDR.

I tried all the tweaking tricks, include installing eAccelerator.. didn't help.

here is a link to my status.php file:
http://www.hwzone.co.il/community/status.php

it seems like the memory is EXTREMELY HIGH (80-95%).. I guess this is the bottleneck ? the only solution is to get more RAM (I already have 2GB..) ?
I noticed that it doesn't use almost any SWAP (0.153%..).. it's a problem, or maybe the server is simply configured to use all the real memory before going to the swap, and that's why it's 95% (?!$?!) full ?

[Compuart]

I think your forum would benifit from the recent changes that were made to SMF, especially the better usage of eAccelerator's cache and the rewritten search engine. RC1 will soon be out, it might be worth a try (eventhough the version's still not completely 'gold').

[Ben_S]

To answer your question, *nix will use all available RAM for caching, as long as it's not using swap then it doesn't need more memory.

Your MySQL configuration looks like it is running the default values, whilst this is the case, performance will always be terrible.

I'm about to go out so can't really comment fully, but edit /etc/my.cnf and up table_cache to something like 2000 for now.

You have plenty of ram so up key_buffer to a fairly high value, say 200M or 300M.

Thread_cache needs raising a fair bit, 30 works well for me.

[[Unknown]]

I don't so much appreciate the logo...

Okay, so first, I see:

CPU: mysqld (201) 63.1%, httpd (107) 46.1%
Memory: 92.832% (1925884k / 2074580k)
MySQL: 95 (87 sleeping, 3 running, 5 locked)

This tells me you are not yet using InnoDB, and that you are not efficiently using your memory.  So, the following are the steps I would take:

1. Convert your tables to InnoDB.
2. Your MySQL settings could use some love.  How do I change MySQL settings?
  - for table_cache, I would recommend 1024.
  - key_buffer_size should be increased, I suggest 16M.
  - thread_cache_size should be non-zero, perhaps 8 is good to start with.
  - query_cache_size should be non-zero, I strongly suggest trying 24M or so to start.
3. Since Apache is using so much CPU, I would assume you don't have an accelerator installed.  What is eAccelerator?

Of course, my suggestions are just basic ones (to try to get things running more smoothly.)  It is very likely Ben_S's setting suggestions would be better than the ones I suggested.

The above steps should make a huge difference, even without upgrading to 1.1.  It will increase memory usage slightly, but 2 gig should be enough for your forum, and you're not maxing out yet.

It's less important to look at the memory usage than the swap usage.  Since your swap usage is low, you are pretty safe.  Compare that to here, where swap usage is too often high... we only have 512 megs of ram, and we do need more.  We're working on getting a new server, in fact.

Can you tell me what times are peak times for you, on that forum?

-[Unknown]

[Ben_S]

With my suggestions, I've assumed that the server is pretty much just running that site and maybe a few other small ones, if thats the case then with 2GB of memory MySQL would certainly work happily with lots of resources as I suggested, if the box is also serving lots of other sites, then [Unknown]s figures may be better.

Running a server is never a case of do something and it will be fine. What works with todays traffic may not work so well with tomorrows.

[Enc0der]

Thanks!

I've made these changes:
1. convert tables to InnoDB (for some reason I thought I've already done this before..)
2. set these settings:

• table_cache = 1024
• key_buffer_size = 16M
• thread_cache_size = 8
• query_cache_size = 24M

3. my ISP said that eAccelerator makes errors, for example:

7084] EACCELERATOR: PHP crashed on opline 34 of vgxb7yi8()
at /home/hwzone/domains/hwzone.co.il/public_html/twatch_include/logger.php:17
[8364] EACCELERATOR: PHP crashed on opline 34 of vgxb7yi8()
at /home/hwzone/domains/hwzone.co.il/public_html/twatch_include/logger.php:17
[8330] EACCELERATOR: PHP crashed on opline 34 of vgxb7yi8()
at /home/hwzone/domains/hwzone.co.il/public_html/twatch_include/logger.php:17
[20374] EACCELERATOR: PHP crashed on opline 34 of vgxb7yi8()

and that the current eAccelerator version doesn't support PHP 4.4.0 (the version I have installed) yet, so he disabled it at the moment..


I'll wait a few days to see if there's a different now, and I shall also check out the RC1 of SMF 1.1.. looks sweet

[[Unknown]]

We run eAccelerator and PHP 4.4.0 right here on this server.  Couldn't live without it, the server isn't powerful enough to recompile everything every page view on top of what it's already doing.  If you do not install it properly, you will get the errors your host quoted.

If in doubt, run:

$ locate eaccelerator.so

And delete all the eaccelerator.so files lying about.  Then, recompile eAccelerator.  Make sure you're using the correct eaccelerator.so file in the php.ini line.  If not, you will get the errors as quoted.

-[Unknown]

[Enc0der]

some updates

here's the answer my isp gave me:

those errors were manufactored when eaccelerator was running on php version
4.3.11 , this time we decided to remove it based on the two reasons i
specified in the last email:
1. it causes wierd crashes even though everything was compiled right and
working as it should.
2. when i upgraded php to 4.4.0 it rejected the module stating: eaccelerator
version is too old and cant run on the current php version, download a newer
version of eaccelerator (it was the newest).

anyway,
I've recently upgraded to SMF 1.1 RC1 (sweet..), but it still didn't help enough..
though, I'm using an old function of mine in SSI.php (top_topics_for_Xdays) because I couldn't implement that on the new "top_topics" function (if you've interested i'll post it here...) is there a significant performance difference ? (my function actually runs everytime a post is made/removed, and write the results to a text-file and is included on the main site.. I found this to be better "mysql-load-wise"..  )


moreover, I discovered that the log_topics table is EXTREMELY large (4,xxx,xxx rows, 400MB+ on InnoDB) and that it takes ALOT of time to work with that table (optimize tables took something like 45 minutes!) .. I renamed that table (for backup) and created a new clean one because it seems to contain a lot of garbage-data.. I hope it'll help. i'll have to wait for the peak hours to check this out...

I also run "Find and repair any errors.". I'm getting HUGH amount of "The subject of topic #XXXXXX is currently not stored in the subject cache." that can't be fixed because it gives me this error:

Got a packet bigger than 'max_allowed_packet' bytes
File: /home/hwzone/domains/hwzone.co.il/public_html/community/Sources/RepairBoards.php
Line: 626

(I actually had more problems that I managed to solve by temporarily disabled the above function on RepairBoards.php...)
is there a way to fix that without changing my 'max_allowed_packet' (because I'm sure i'll never get to a satisfying number.. I'm talking thousands of lines...) ?


oh, btw,

Can you tell me what times are peak times for you, on that forum?

we're talking mainly 20:00PM - 2:00AM, and sometimes it takes a few minutes (!!!) to load the page.. I still have some locked processes on the peak hours, on the 'messages' table I believe (cause it's not InnoDB of couse)..

[Compuart]

I also run "Find and repair any errors.". I'm getting HUGH amount of "The subject of topic #XXXXXX is currently not stored in the subject cache." that can't be fixed because it gives me this error:

Got a packet bigger than 'max_allowed_packet' bytes
File: /home/hwzone/domains/hwzone.co.il/public_html/community/Sources/RepairBoards.php
Line: 626

(I actually had more problems that I managed to solve by temporarily disabled the above function on RepairBoards.php...)
is there a way to fix that without changing my 'max_allowed_packet' (because I'm sure i'll never get to a satisfying number.. I'm talking thousands of lines...) ?

try replacing (RepairBoards.php)
while ($row = mysql_fetch_assoc($request))
{
foreach (text2words($row['subject']) as $word)
$insertRows[] = "'$word', $row[ID_TOPIC]";
}
mysql_free_result($request);

if (!empty($insertRows))
db_query("
INSERT INTO {$db_prefix}log_search_subjects
(word, ID_TOPIC)
VALUES (" . implode('),
(', array_unique($insertRows)) . ")", __FILE__, __LINE__);



by:
while ($row = mysql_fetch_assoc($request))
{
foreach (text2words($row['subject']) as $word)
$insertRows[] = "'$word', $row[ID_TOPIC]";
if (count($insertRows) > 500)
{
db_query("
INSERT INTO {$db_prefix}log_search_subjects
(word, ID_TOPIC)
VALUES (" . implode('),
(', $insertRows) . ")", __FILE__, __LINE__);
$insertRows = array();
}

}
mysql_free_result($request);

if (!empty($insertRows))
db_query("
INSERT INTO {$db_prefix}log_search_subjects
(word, ID_TOPIC)
VALUES (" . implode('),
(', $insertRows) . ")", __FILE__, __LINE__);


Should get that error out of the way.

[Enc0der]

worked, thanx