why dont you just change your webhost, many new webhost give 30 day money back guarantee so try them.
Hi,
How do you find out how many queries your database averages per second and how long they roughly take.
Thanks!
I’m pretty sure you can find it in phpmyadmin stats
Same issue here with us, after about one month ongoing.
is your server using the zend optimiser. I can tell you it makes a massive difference to the speed of the php software.
Have you tryed removing certain pages?
Do 1 page at a time, it will probley take all day but try it, you will notice what page is causing the load. Take a page down for 15minutes, if the load is still high, put it back up, and take donw another until you find out what the cause is.
i had this once on an old site… i was sending out email through a cron job… i didn’t think that every registered user would ask for an update every single 15 minutes… before i knew it we were sending out 2 million emails a day… and none of them spam… imagine that.
Does this really make a huge difference? I run mod_rewrite rules in my .htaccess, can you maybe give an example of how I would move them to the httpd.conf file?
Just a quick thought - in cases where you control your server, adding more RAM might be less expensive and time consuming than optimizing the life out of your code. Sure, clean,efficient code helps you sleep better at night, but we’re talking business where time = money!
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!
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("\ ", '', $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’
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
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
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.
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?
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.
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
[list=1]
[]2.5657: DELETE FROM ibf_sessions WHERE running_time < [VALUE] or ip_address=‘[VALUE]’
[]1.8772: SELECT * FROM ibf_sessions
WHERE member_name
!=‘’ AND running_time
>[VALUE] ORDER BY running_time
DESC
[]0.2393: SELECT * FROM ibf_games_list
ORDER BY RAND() LIMIT 8
[]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]
[]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
[]0.0386: SELECT score
FROM ibf_games_scores
WHERE mid
=‘[VALUE]’ LIMIT 1
[]0.0289: SELECT id FROM ibf_members WHERE LOWER(name)=‘[VALUE]’
[]0.0214: SELECT * FROM ibf_links
ORDER BY text
[]0.0124: SELECT * FROM ibf_games_list
ORDER BY gcount
DESC LIMIT 10
[]0.0006: SELECT avatar
, avatar_size
AS size
FROM ibf_members
WHERE id
= ‘’
[]0.0006: SELECT MAX(id) as new_id FROM ibf_members
[]0.0006: UPDATE ibf_stats SET MEM_COUNT=MEM_COUNT+1, LAST_MEM_NAME=‘[VALUE]’
[]0.0006: SELECT * from ibf_badwords
[]0.0006: SELECT gid
, highscore_type
, gtitle
FROM ibf_games_list
WHERE gid
=‘[VALUE]’ LIMIT 1
[]0.0006: SELECT s_id
FROM ibf_games_scores
ORDER BY s_id
DESC LIMIT 1
[]0.0007: SELECT * from ibf_pfields_data WHERE fedit=‘[VALUE]’
[]0.0007: SELECT * FROM ibf_stats
LIMIT 1
[]0.0007: UPDATE ibf_members SET last_activity=‘[VALUE]’ WHERE id=[VALUE]
[/list]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!