Causes of High Server Load

[todd]

Probably the quickest way to see if your MySQL queries are fairly well put together is to use the following two tools from within MySQL. I think with Cpanel's implementation of phpMyAdmin you can only use one though (depends on your web hosting company). Direct MySQL server access is best.

With the speed of your queries this may not be too important but really worth checking anyway.

From the MySQL prompt (or from in the SQL window in phpMyAdmin)

mysql>show full processlist;

Look out for "Copying to tmp table" in any of your processes. A query that has to create temporary tables can almost always benefit from some attention. Look out for any queries that use a join without properly using a where statement on the first table (and each table as it is joined in, especially tables with a lot of data). Any "sort by" or "distinct" can be bad news as well.


The second is just a general indicator that indexing may help - kind of tough on a shared server

mysql>show status;

Look for "Handler_read_rnd_next" - compare it with the other "Handler_read_*" - if "Handler_read_rnd_next" is really large, look into your indexing and your where statements. If you have a suspect page, you can try checking the number for "Handler_read_rnd_next" then running your page a bunch of times and checking again right away.

There can definitely be a lot of things that will run up the load and it can be very difficult to troubleshoot without being able to use command line tools. You might be headed for a good VPS or dedicated server with your needs.

Well, hope that helps!

[Daijoubu]

I think I may have solved my problem. I previously assigned a function for output buffering which manually gzencoded and reduced excess whitespace for all HTML files. I will update you with a definite answer to whether it has worked later.

Striping extra whitespace, especialy with preg_replace, is useless and use quite alot of CPU time
Increasing the compression level by 1 is faster and saves more bytes (say, from level 1 to 2)

Btw, do not use anything above level 1 as the saving would be minimal but use quite more processing power
If you're no longer using your own function but ob_gzhandler(), use ini_set('zlib.output_compression_level', 1) to set it

If you still have your own function, this is the fastest way to accomplish it:

if (strpos($_SERVER['HTTP_ACCEPT_ENCODING'], 'gzip') !== false)
{
    header('Content-Encoding: gzip');
    echo "\x1f\x8b\x08\x00\x00\x00\x00\x00", gzcompress($content, 1);
}
else
{
    // Code to strip space/tabs/newlines if browser doesn't support gzip
    //echo str_replace("\t", '', $content);
} 

I've tested douzens of browsers and all of them ignore the ending pack('V', CRC/lenght)

Btw, using output buffering and echo() is more effective than adding content to an $variable .= 'content'

is your server using the zend optimiser. I can tell you it makes a massive difference to the speed of the php software.

The Zend Optimizer usually slow things down! The time it waste on "optimizing" the code outweigh the benefits
Unless you have bunch of badly written loops, I doubt it will make any difference (except adding to the load...)

As for mod_rewrite, that thing also consume power, either or not it's in an .htaccess or directly inside httpd.conf
And aren't you in a shared/virtual environment? How are you going to change Apache settings?

As for optimizing your queries, run EXPLAIN on your SELECT and see what's mysql doing
Adding index or/and tweaking your row type can help
Even if it takes 0.01secs, it doesn't mean they're optimized

[foxysere]

Hello,

The load caused by arcadexl has now seem to have leveled out after CrabbyX has modified some of his code. It is usually staying below the 0.60 mark which is still quite high but it should be ok.

I ran Apache Benchmark on arcadexl and some other sites on the server, here are the results:

root@server [~]# ab -n 100 http://www.arcadexl.com/
This is ApacheBench, Version 2.0.40-dev <$Revision: 1.121.2.1 $> apache-2.0
Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright (c) 1998-2002 The Apache Software Foundation, http://www.apache.org/

Benchmarking www.arcadexl.com (be patient).....done


Server Software:        Apache/1.3.33
Server Hostname:        www.arcadexl.com
Server Port:            80

Document Path:          /
Document Length:        39641 bytes

Concurrency Level:      1
Time taken for tests:   29.975571 seconds
Complete requests:      100
Failed requests:        99
   (Connect: 0, Length: 99, Exceptions: 0)
Write errors:           0
Total transferred:      3996125 bytes
HTML transferred:       3959725 bytes
Requests per second:    3.34 [#/sec] (mean)
Time per request:       299.756 [ms] (mean)
Time per request:       299.756 [ms] (mean, across all concurrent requests)
Transfer rate:          130.17 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       0
Processing:   225  299 133.5    247     976
Waiting:      213  286 132.4    235     958
Total:        225  299 133.5    247     976

Percentage of the requests served within a certain time (ms)
  50%    247
  66%    259
  75%    291
  80%    331
  90%    440
  95%    661
  98%    812
  99%    976
 100%    976 (longest request)

root@server [~]# ab -n 100 http://www.hotdesign.co.uk/
This is ApacheBench, Version 2.0.40-dev <$Revision: 1.121.2.1 $> apache-2.0
Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright (c) 1998-2002 The Apache Software Foundation, http://www.apache.org/

Benchmarking www.hotdesign.co.uk (be patient).....done


Server Software:        Apache/1.3.33
Server Hostname:        www.hotdesign.co.uk
Server Port:            80

Document Path:          /
Document Length:        4532 bytes

Concurrency Level:      1
Time taken for tests:   2.344284 seconds
Complete requests:      100
Failed requests:        0
Write errors:           0
Total transferred:      480900 bytes
HTML transferred:       453200 bytes
Requests per second:    42.66 [#/sec] (mean)
Time per request:       23.443 [ms] (mean)
Time per request:       23.443 [ms] (mean, across all concurrent requests)
Transfer rate:          200.06 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       0
Processing:    18   22  19.8     19     196
Waiting:       12   21  19.9     19     195
Total:         18   22  19.8     19     196

Percentage of the requests served within a certain time (ms)
  50%     19
  66%     20
  75%     20
  80%     20
  90%     22
  95%     25
  98%     84
  99%    196
 100%    196 (longest request)

As you can see arcadexl's average request time is just 300ms, whereas hotdesigns is just under 24ms. It is noticable that arcadexl is slower, could this still be the amount of queries / includes?

Regards,

J Fox

[Roger Ramjet]

Just adding my 2 cents: Why does your page use 20 queries?

I ask this because, in my experience, many web and php programmers have no idea how to use a database properly. To me, 20 queries sounds like you are running a query for each table instead of using joins, group by, etc. And looking at your ArcadeXL site I don't see anything that should require so many queries. If you would like to post them here I can see what can be done to improve that part of your code.

[Daijoubu]

You could use IPB DB class and make use of it's debug
ie: http://ptprophecy.com/forum/?debug=1

[CrabbyX]

My home page uses about 12 queries max. Most are due to random display of items, latest played games, and users online/sessions.

I already use a IPB SDK and have modified the DB driver to tell me what queries are being used.

[Daijoubu]

My home page uses about 12 queries max. Most are due to random display of items, latest played games, and users online/sessions.

I already use a IPB SDK and have modified the DB driver to tell me what queries are being used.

And is it possible for us to take a look at that output?

[CrabbyX]

just add explain=1 to the query string on any page:

http://www.arcadexl.com/?explain=1

[Roger Ramjet]

just add explain=1 to the query string on any page:

http://www.arcadexl.com/?explain=1

Why should we? It is not us that has the problem. We are asking to see the whole set of queries to see if they cannot be rewritten to require fewer in number and also less processing of the resultsets. Now if you can't be bothered to post them, we can't be bothered to look them up ourselves.

[CrabbyX]

Why have you bothered getting yourself worked up and suggesting I can't be bothered putting the queries here? It's not nice to judge.

I will update this post in a couple of hours with all queries that are used often enough for them to be logged

1. 2.5657: DELETE FROM ibf_sessions WHERE running_time < [VALUE] or ip_address='[VALUE]'
2. 1.8772: SELECT * FROM `ibf_sessions` WHERE `member_name`!='' AND `running_time`>[VALUE] ORDER BY `running_time` DESC
3. 0.2393: SELECT * FROM `ibf_games_list` ORDER BY RAND() LIMIT 8
4. 0.2317: SELECT moderator.mid as is_mod, moderator.allow_warn, m.id, m.name, m.mgroup, m.password, m.email, m.restrict_post, m.view_sigs, m.view_avs, m.view_pop, m.view_img, m.auto_track, m.mod_posts, m.language, m.skin, m.new_msg, m.show_popup, m.msg_total, m.time_offset, m.posts, m.joined, m.last_post, m.last_visit, m.last_activity, m.dst_in_use, m.view_prefs, m.org_perm_id, m.temp_ban, m.sub_end, g.* FROM ibf_members m LEFT JOIN ibf_groups g ON (g.g_id=m.mgroup) LEFT JOIN ibf_moderators moderator ON (moderator.member_id=m.id OR moderator.group_id=m.mgroup ) WHERE m.id=[VALUE]
5. 0.0552: SELECT * FROM `ibf_games_list` LEFT JOIN `ibf_games_champs` ON `ibf_games_champs`.`champ_gid`=`ibf_games_list`.`gid` WHERE `ibf_games_list`.`gcat`='[VALUE]' ORDER BY `ibf_games_list`.`added` DESC LIMIT 25,25
6. 0.0386: SELECT `score` FROM `ibf_games_scores` WHERE `mid`='[VALUE]' LIMIT 1
7. 0.0289: SELECT id FROM ibf_members WHERE LOWER(name)='[VALUE]'
8. 0.0214: SELECT * FROM `ibf_links` ORDER BY `text`
9. 0.0124: SELECT * FROM `ibf_games_list` ORDER BY `gcount` DESC LIMIT 10
10. 0.0006: SELECT `avatar`, `avatar_size` AS `size` FROM `ibf_members` WHERE `id` = ''
11. 0.0006: SELECT MAX(id) as new_id FROM ibf_members
12. 0.0006: UPDATE ibf_stats SET MEM_COUNT=MEM_COUNT+1, LAST_MEM_NAME='[VALUE]'
13. 0.0006: SELECT * from ibf_badwords
14. 0.0006: SELECT `gid`, `highscore_type`, `gtitle` FROM `ibf_games_list` WHERE `gid`='[VALUE]' LIMIT 1
15. 0.0006: SELECT `s_id` FROM `ibf_games_scores` ORDER BY `s_id` DESC LIMIT 1
16. 0.0007: SELECT * from ibf_pfields_data WHERE fedit='[VALUE]'
17. 0.0007: SELECT * FROM `ibf_stats` LIMIT 1
18. 0.0007: UPDATE ibf_members SET last_activity='[VALUE]' WHERE id=[VALUE]

There are a few, although I'm not sure how you can shorten them without knowing which pages they're executed on, but whatever you insist!

[Daijoubu]

just add explain=1 to the query string on any page:

http://www.arcadexl.com/?explain=1

Cool, those numbers doesn't mean much, now if you can run EXPLAIN SELECT * FROM tbl...

And what are those 2 queries for?

0.0027
SELECT * FROM `ibf_sessions` WHERE `member_name`='' AND `running_time`>1109092767 AND `location`='Arcade,,' ORDER BY `running_time` DESC

0.0025 
SELECT * FROM `ibf_sessions` WHERE `member_name`!='' AND `running_time`>1109092767 AND `location`='Arcade,,' ORDER BY `running_time` DESC 

Oh and btw, you shouldn't backtick strings or quote integers in MySQL...

Looking at http://www.arcadexl.com/games/action.html?explain=1
You have a query that takes 0.4secs to execute...

SELECT * FROM `ibf_games_list` LEFT JOIN `ibf_games_champs` ON `ibf_games_champs`.`champ_gid`=`ibf_games_list`.`gid` WHERE `ibf_games_list`.`gcat`='3' ORDER BY `ibf_games_list`.`added` DESC LIMIT 0,25 

I'm sure this one can be tweaked and sometimes, 2 small queries do a better job than one with bunch of joins and temporary table

One more thing, it looks like MySQL is caching the results so you may want to add SQL_NO_CACHE after SELECT for more accurate debugging

[CrabbyX]

Backtick strings? I have not backticked any strings, only the names of fields and tables, which I'm sure is 100% correct.

Sorting out EXPLAINs now. Sorting out double query now.

[Daijoubu]

as `stuffs`

[CrabbyX]

Those two queries, one is for selecting all guests (no-names) from the table and the other for selecting active members. I can probably shorten that now.

Does EXPLAIN actually execute UPDATEs/DELETEs?

[Daijoubu]

EXPLAIN only works with SELECT
As for those 2 queries to ibf_sessions, I would suggest you to either grab them all and process with PHP (what IPB do on the board index) or run a SELECT for those with a member_id and a COUNT(*) for the guests

[CrabbyX]

Would a PHP rand() from an array selected from the database be quicker than doing ORDER BY rand()? .. from a table with currently 170 rows, likely to end up about 1000 in the future?

Something like this:

$result = mysql_query( "SELECT * FROM table" );
$num_rows = mysql_num_rows( $result );
$index = rand( 0, ($num_rows-1) );
$random_record = mysql_result( $result, $index );