suggestion for mysql optimization

[Grumpy]

You have a 6gig server.... VPS?

Anyway, I only see 5 apache processes. Your maximum server count is too low. 5 is probably fine if you had something else to handle static, but right now, you're kinda bottlenecked there.

In httpd.conf

Increase your max apache child count to 20.

Keep it on dynamic

min spare of 5

max spare of 10

start with 10

See how that works out.

But, if you really want something better, use nginx to serve static and just forward php to apache. simple setup, should give big boost (and some security).

[GreenLinks]

Yes ipb support staff checked these things before sending me to this forum

You should post all questions directly to IPB support if you'll keep hiding information from us.

[Dhil_]

You should post all questions directly to IPB support if you'll keep hiding information from us.

Hej Sjv,

I am not hiding any informations :) please feel free to ask any questions and i will be honoured to answer your requires and i really appreciate the help from you guys.

i will do the suggestions to http.conf and let you know, i have to admit that board is running much better now.

[GreenLinks]

I think it was pretty clear , i want to see output of your slow query log

[thompsone]

Sjv -

I've got this one random query that's taking 15 seconds to complete. Can you offer some insight?

# Time: 130204 23:00:35
# User@Host: ******[******] @ localhost []
# Query_time: 15.569455  Lock_time: 0.000092 Rows_sent: 221  Rows_examined: 221
use com20bat_forum;
SET timestamp=1360040435;
SHOW TABLE STATUS;

[GreenLinks]

It is normal to see locks on that query as the query is designed to update all tables timestamp values. I am not sure why you need that query honestly.

[thompsone]

Sjv -

Truly. That query doesn't scale. From what I've been able to determine it's being sent from the scheduler as it's frequency is every 24 hours. I'm still narrowing it down.

While I was looking through things I thought I'd look at queries that are being flagged for not using indexes or using indexes with a limit set. I believe on my version of MySQL 5.5 that the flag is thrown for both reasons.

# Time: 130205  0:35:26
# User@Host: user****[user****] @ localhost []
# Query_time: 0.000432  Lock_time: 0.000028 Rows_sent: 0  Rows_examined: 1
use com20bat_forum;
SET timestamp=1360046126;
DELETE FROM IVBsessions WHERE id='google=42e6cae335e9ee21139b7d2a04494e51_session' OR ip_address='66.249.73.100' OR id='google=8466e4eb36339a36f6f8f46c48be2b14_session';

# Time: 130205  0:35:28
# User@Host: user****[user****] @ localhost []
# Query_time: 0.011539  Lock_time: 0.000077 Rows_sent: 219  Rows_examined: 442
SET timestamp=1360046128;
SELECT f.*,p.*,itemmarking.* FROM IVBforums f  LEFT JOIN IVBpermission_index p ON ( p.perm_type='forum' AND p.app='forums' AND p.perm_type_id=f.id ) 
 LEFT JOIN IVBcore_item_markers itemmarking ON ( itemmarking.item_member_id=83089 AND itemmarking.item_app='forums' AND itemmarking.item_app_key_1=f.id );

# Time: 130205  0:35:29
# User@Host: user****[user****] @ localhost []
# Query_time: 0.000327  Lock_time: 0.000097 Rows_sent: 0  Rows_examined: 43
SET timestamp=1360046129;
SELECT blog_id FROM IVBblog_blogs WHERE FIND_IN_SET(83089, blog_authorized_users);

# User@Host: user****[user****] @ localhost []
# Query_time: 0.000661  Lock_time: 0.000058 Rows_sent: 0  Rows_examined: 1
SET timestamp=1360046129;
DELETE FROM IVBsessions WHERE ip_address='66.249.73.100' OR id='google=c3caf06d2e57860a9a961f5b8381473a_session';

# Time: 130205  0:35:35
# User@Host: user****[user****] @ localhost []
# Query_time: 0.000617  Lock_time: 0.000060 Rows_sent: 0  Rows_examined: 1
SET timestamp=1360046135;
DELETE FROM IVBsessions WHERE ip_address='157.55.35.76' OR id='bingbot=83dee689c8bf5117949750ecfbdaffad_session';

# Time: 130205  0:35:38
# User@Host: user****[user****] @ localhost []
# Query_time: 0.000528  Lock_time: 0.000032 Rows_sent: 0  Rows_examined: 1
use com20bat_forum;
SET timestamp=1360046138;
DELETE FROM IVBsessions WHERE ip_address='157.55.35.76' OR id='bingbot=39c68eee0b2fd470c34f5a1ffa173921_session';

# Time: 130205  0:35:39
# User@Host: user****[user****] @ localhost []
# Query_time: 0.011530  Lock_time: 0.000065 Rows_sent: 219  Rows_examined: 442
SET timestamp=1360046139;
SELECT f.*,p.*,itemmarking.* FROM IVBforums f  LEFT JOIN IVBpermission_index p ON ( p.perm_type='forum' AND p.app='forums' AND p.perm_type_id=f.id ) 
 LEFT JOIN IVBcore_item_markers itemmarking ON ( itemmarking.item_member_id=83089 AND itemmarking.item_app='forums' AND itemmarking.item_app_key_1=f.id );

Thanks for looking. Greatly appreciated.