Since I updated from 0.9 to 1.8.2, my blog is VERY slow. Any suggestions how to fix the performance would be much appreciated.

rossputin,

fwiw, your site loaded much quicker than the original poster's. i dont think youre seeing a similar problem.

AslanLyle,

intertestingly enough, while the first load of your site is pretty slow, and subsequent pages loaded via clicking a link display fairly quickly. Almost as if the initial mysql connection is being done slowly.

AslanLyle, try adding something like

if( $_SERVER['REMOTE_ADDR'] == 'X' )
{
  $debug = 1;
}

at the end of your /conf/_basic_config.php file, where 'X' should get replaced with your current IP, so only you see the debug information at the bottom of each page.
Of course, you could just write "$debug = 1;", but it may "surprise" your normal visitors.

Then see the "Timers" table. What takes so long there? Also, all SQL queries should get dumped (with time information). Is it a particular query that takes so long?

I turned on debugging as you suggested and here is the results of loading the main page of one of my blogs. The particular page has only 4 blog entries on it. I'm not exactly sure what to do with this information, so any interpretation would be helpful. Do the results below provide any insight? It's obviously the queries on the evo_posts table that are taking the longest.

As you can see, it took almost 9 seconds to load the page. I was lucky it loaded at all. There are some times of day when things seem fine, which makes me think it's a server load problem...

BTW, I had to add your code to _advanced.php because it was explicitely setting $debug=0 and preventing the setting of it in _basic_config.php from working...

Timers Table:
Timers
Category Time % Count
total 8.881 100.00% 1
sql_queries 8.585 96.67% 29
blog_main.inc 8.534 96.09% 1
main.inc 0.337 3.79% 1
plugins_register 0.019 0.21% 4
auto_p_plugin_(#2) 0.011 0.12% 8
smilies_plugin_(#8) 0.011 0.12% 8
plugin_init 0.009 0.10% 1
plugins_inst_UserSettings 0.007 0.08% 4
autolinks_plugin_(#3) 0.006 0.07% 8
plugins_inst_Settings 0.002 0.02% 4
texturize_plugin_(#4) 0.000 0.00% 2
Hit::get_remote_host 0.000 0.00% 1
Database queries: 29

Here are the queries which took a long time:
Query #3:
SELECT sess_ID, sess_key, sess_data, sess_user_ID
FROM evo_sessions
WHERE sess_ID = '168'
AND sess_key = 'hxEwZ6T7u6N3RgfNpJhqjghjzUX8DSG4'
AND UNIX_TIMESTAMP(sess_lastseen) > 1161414191
Rows: 1 – Time: 0.1324s (1.54%)

Query #11: Get ID list for Item List (Main|Lastpostdate) Query
SELECT DISTINCT post_ID
FROM evo_posts INNER JOIN evo_postcats ON post_ID = postcat_post_ID INNER JOIN evo_categories ON postcat_cat_ID = cat_ID
WHERE (cat_blog_ID = 3)
AND ( ( ( post_status = "private"
AND post_creator_user_ID = 3 )
OR post_status IN ('published','protected') ) )
AND (post_datestart <= '2006-10-28 00:03:12')
ORDER BY post_datestart DESC
LIMIT 10
Rows: 6 – Time: 2.7060s (31.52%)

Query #14: Get result count
SELECT COUNT( DISTINCT post_ID )
FROM evo_posts INNER JOIN evo_postcats ON post_ID = postcat_post_ID INNER JOIN evo_categories ON postcat_cat_ID = cat_ID
WHERE (cat_blog_ID = 3)
AND ( ( ( post_status = "private"
AND post_creator_user_ID = 3 )
OR post_status IN ('published','protected') ) )
AND (post_datestart <= '2006-10-28 00:03:12')
Rows: 1 – Time: 3.3645s (39.19%)

Query #26: Record the hit
INSERT INTO evo_hitlog( hit_sess_ID, hit_datetime, hit_uri, hit_referer_type, hit_referer, hit_referer_dom_ID, hit_blog_ID, hit_remote_addr, hit_agnt_ID )
VALUES( "168",
FROM_UNIXTIME(1162018991), "/blog/index.php?blog=3&skin=basic", "direct", "", "0", '3', "70.90.180.213", 1 )
Rows: 1 – Time: 1.2044s (14.03%)

Query #27: Autopruning hit log
DELETE
FROM evo_hitlog
WHERE hit_datetime < '2006-09-28'
Rows: 0 – Time: 0.4485s (5.22%)

Query #29:
REPLACE INTO evo_settings (set_name, set_value)
VALUES ('auto_prune_stats_done', '2006-10-28 00:03:11')
Rows: 2 – Time: 0.5247s (6.11%)

Here are the times for the same blog with the wpc-default skin, which includes a sidebar. 41 seconds to load!

Note that every one of the queries that takes a long time except the last one is against evo_posts.

Timers
Category Time % Count
total 41.890 100.00% 1
blog_main.inc 41.412 98.86% 1
sql_queries 40.819 97.44% 32
calendar_plugin_(#5) 15.109 36.07% 3
archives_plugin_(#6) 8.043 19.20% 3
categories_plugin_(#7) 3.283 7.84% 3
...

Query #11: Get ID list for Item List (Main|Lastpostdate) Query
Rows: 6 – Time: 4.6379s (11.36%)

Query #14: Get result count
Rows: 1 – Time: 3.2212s (7.89%)

Query #22: Get ID list for Item List (Main|Lastpostdate) Query
Rows: 5 – Time: 4.2169s (10.33%)

Query #24: Load postcounts
Rows: 1 – Time: 3.2814s (8.04%)

Query #25:
SELECT COUNT( DISTINCT YEAR(post_datestart), MONTH(post_datestart) )
FROM evo_posts INNER JOIN evo_postcats ON post_ID = postcat_post_ID INNER JOIN evo_categories ON postcat_cat_ID = cat_ID
Rows: 1 – Time: 4.1984s (10.29%)

Query #26:
SELECT YEAR(post_datestart) AS year, ...
Rows: 5 – Time: 3.8418s (9.41%)

Query #27:
SELECT COUNT(DISTINCT post_ID) AS item_count, ...
Rows: 0 – Time: 3.4239s (8.39%)

Query #28: Calendar: find prev year with posts
Rows: 1 – Time: 2.8945s (7.09%)

Query #29: Calendar: Find prev month with posts
Rows: 1 – Time: 2.9067s (7.12%)

Query #30: Calendar: Find next month with posts
Rows: 0 – Time: 2.8345s (6.94%)

Query #31: Calendar: find next year with posts
Rows: 0 – Time: 3.0420s (7.45%)

Query #32: Record the hit
Rows: 1 – Time: 1.9481s (4.77%)

AslanLyle, how big is your evo_posts table? (how many entries)

Are there indexes on the table?

There should the the following indexes:
PRIMARY KEY post_ID( post_ID ),
UNIQUE post_urltitle( post_urltitle ),
INDEX post_datestart( post_datestart ),
INDEX post_main_cat_ID( post_main_cat_ID ),
INDEX post_creator_user_ID( post_creator_user_ID ),
INDEX post_status( post_status ),
INDEX post_parent_ID( post_parent_ID ),
INDEX post_assigned_user_ID( post_assigned_user_ID ),
INDEX post_ptyp_ID( post_ptyp_ID ),
INDEX post_pst_ID( post_pst_ID )

You can check this with PhpMyAdmin. If the indexes are there, try a "OPTIMIZE table" (also with phpMyAdmin).

Looks like there's 187 rows in the evo_posts table.

All the indexes appear to be there, and I ran the optimize table on evo_posts and evo_postcats.

This morning, as usual, the performance is better, but it will tank again tonight. Here are the times this morning:

total 2.678 100.00% 1
main.inc 1.579 58.96% 1
sql_queries 1.472 54.97% 34
antispam_url 1.092 40.78% 1
blog_main.inc 1.089 40.66% 1
plugins_register 0.349 13.03% 7
auto_p_plugin_(#2) 0.085 3.17% 13
calendar_plugin_(#5) 0.067 2.50% 3
archives_plugin_(#6) 0.026 0.97% 3
smilies_plugin_(#8) 0.024 0.90% 13

Query #13: Get ID list for Item List (Main|Lastpostdate) Query
Rows: 6 – Time: 0.0014s (0.10%)

Query #14: Get data for Item List (Main|Lastpostdate) Query
Rows: 6 – Time: 0.0023s (0.15%)

Now, the longest queries are the antispam query and session query (which seems to be broken because it doesn't always work):

Query #2: Check URL against antispam blacklist
Rows: 0 – Time: 1.0912s (74.13%)

Query #5:
SELECT sess_ID, sess_key, sess_data, sess_user_ID
FROM evo_sessions
Rows: 1 – Time: 0.0780s (5.30%)

Thanks for your help!

0.07s for the session query does not seem to be really much.. but the "Check URL against antispam blacklist" seems to be too long. Is there the index on aspm_string? How many entries are in the table?