Mysql & Server Issue

[GMIC]

Looking for some help with this as IPB support are trying hard but not getting anywhere. I have a slow query issue which is down to Topic Markers the query taking approx 5 sec to execute (see below) my board is currently 460,000 posts with 7000 members on average a hard core of 250 members on in 24hours so not that demanding. I have over 300 tables for IPB and other IPB add on Gallery blog etc.

Extract from Slow query log, top, mysqltuner after upgrade. IPB say that the reason the mysql query is still so long is because I need to go for at lead 4GB RAM or dedicated for the level I am trying to run at. Never had an issue before 3.3.1. But to put it in a nutshell is this a server issue or an IPB issue as if it is a server I am going to have to look elsewhere.

[/font]

[size=3]SELECT p.*,m.member_id as mid,m.name,m.member_group_id,m.email,m.joined,m.posts,

m.last_visit, m.last_activity,m.login_anonymous,m.title as member_title, m.warn_level,

m.warn_lastwarn, m.members_display_name, m.members_seo_name, m.member_banned,

m.has_gallery, m.has_blog,

m.members_bitoptions,m.mgroup_others,pp.*,w.wl_id,pc.*,rep_index.rep_rating as

has_given_rep,rep_cache.rep_points, rep_cache.rep_like_cache,ccb.cache_content as

cache_content_sig, ccb.cache_updated as cache_updated_sig FROM ibf_posts p LEFT JOIN

ibf_members m ON ( m.member_id=p.author_id )

LEFT JOIN ibf_profile_portal pp ON ( m.member_id=pp.pp_member_id )

LEFT JOIN ibf_members_warn_logs w ON ( w.wl_content_app='forums' and

w.wl_content_id1=p.pid )

LEFT JOIN ibf_pfields_content pc ON ( pc.member_id=p.author_id )

LEFT JOIN ibf_reputation_index rep_index ON ( rep_index.app='forums' AND

rep_index.type='pid' AND

rep_index.type_id=p.pid AND

rep_index.member_id=0 )

LEFT JOIN ibf_reputation_cache rep_cache ON ( rep_cache.app='forums' AND

rep_cache.type='pid' AND rep_cache.type_id=p.pid )

LEFT JOIN ibf_content_cache_sigs ccb ON ( ccb.cache_content_id=m.member_id )

p.topic_id=3758 AND p.queued=0 ORDER BY p.pid asc LIMIT 20,20;

# Time: 120503 20:41:54

# User@Host:

[font=Monaco]WHERE [/font]

# Query_time: 5.619694 Lock_time: 0.000062 Rows_sent: 152 Rows_examined: 3730688

SET timestamp=1336074114;

SELECT f.*,p.*,itemmarking.* FROM ibf_forums f LEFT JOIN ibf_permission_index p ON

( p.perm_type='forum' AND p.app='forums' AND p.perm_type_id=f.id )

LEFT JOIN ibf_core_item_markers itemmarking ON ( itemmarking.item_member_id=11689 AND

itemmarking.item_app='forums' AND itemmarking.item_app_key_1=f.id );

# Time: 120503 20:41:55

# User@Host:

# Query_time: 1.427429 Lock_time: 0.000252 Rows_sent: 1 Rows_examined: 460574

SET timestamp=1336074115;[/size][font=Monaco]

top - 23:35:24 up 1:57, 1 user, load average: 1.08, 1.76, 1.60

Tasks: 78 total, 1 running, 77 sleeping, 0 stopped, 0 zombie

Cpu(s): 3.0%us, 4.5%sy, 0.0%ni, 92.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

Mem: 3145728k total, 843328k used, 2302400k free, 0k buffers

Swap: 0k total, 0k used, 0k free, 0k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

25662 mysql 15 0 1135m 207m 6100 S 100.2 6.7 89:29.50 mysqld

31792 macmedal 15 0 136m 22m 7984 S 19.0 0.7 0:22.78 php

1 root 15 0 10352 732 608 S 0.0 0.0 0:00.57 init

7588 nobody 17 0 72428 5852 1880 S 0.0 0.2 0:00.00 httpd

7598 nobody 18 0 72692 6016 1844 S 0.0 0.2 0:00.00 httpd

8043 root 18 0 90156 3344 2576 S 0.0 0.1 0:00.04 sshd

8087 root 15 0 54040 2104 1512 S 0.0 0.1 0:00.09 sftp-server

11468 root 16 0 12196 1752 1304 S 0.0 0.1 0:00.02 bash

11677 root 15 0 12728 1248 944 R 0.0 0.0 0:00.01 top

12086 macmedal 16 0 146m 31m 8032 S 0.0 1.0 0:18.86 php

12100 macmedal 15 0 145m 31m 8008 S 0.0 1.0 0:31.61 php

20312 root 19 0 64704 8548 2392 S 0.0 0.3 0:00.09 leechprotect

20313 nobody 18 0 72428 4712 772 S 0.0 0.1 0:00.02 httpd

23757 root 15 -4 12620 680 356 S 0.0 0.0 0:00.00 udevd

23884 root 11 -10 38912 31m 1708 S 0.0 1.0 0:00.04 iscsiuio

24122 root 16 0 5912 612 492 S 0.0 0.0 0:00.13 syslogd

24126 root 22 0 3808 428 340 S 0.0 0.0 0:00.00 klogd

>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>

>> Bug reports, feature requests, and downloads at

http://mysqltuner.com/

>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.5.22-cll

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 962M (Tables: 928)

[--] Data in InnoDB tables: 80K (Tables: 5)

[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)

[!!] Total fragmented tables: 36

-------- Security Recommendations -------------------------------------------

[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------

[--] Up for: 1h 59m 35s (130K q [18.136 qps], 4K conn, TX: 3B, RX: 47M)

[--] Reads / Writes: 42% / 58%

[--] Total buffers: 1.0G global + 8.2M per thread (150 max threads)

[OK] Maximum possible memory usage: 2.2G (74% of installed RAM)

[OK] Slow queries: 1% (1K/130K)

[OK] Highest usage of available connections: 27% (41/150)

[OK] Key buffer size / total MyISAM indexes: 512.0M/300.1M

[OK] Key buffer hit rate: 99.9% (49M cached / 28K reads)

[OK] Query cache efficiency: 54.6% (40K cached / 74K selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 2% (47 temp sorts / 2K sorts)

[!!] Joins performed without indexes: 923

[!!] Temporary tables created on disk: 45% (461 on disk / 1K total)

[OK] Thread cache hit rate: 99% (41 created / 4K connections)

[!!] Table cache hit rate: 14% (512 open / 3K opened)

[OK] Open file limit used: 10% (824/8K)

[OK] Table locks acquired immediately: 98% (120K immediate / 122K locks)

[OK] InnoDB data size / buffer pool: 80.0K/128.0M

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

MySQL started within last 24 hours - recommendations may be inaccurate

Adjust your join queries to always utilize indexes

Temporary table size is already large - reduce result set size

Reduce your SELECT DISTINCT queries without LIMIT clauses

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

join_buffer_size (> 4.0M, or always use indexes with joins)

table_cache (> 512)

ANY HELP APPRECIATED !

[AlexJ]

What's your current my.cnf aka mysql settings?

[GMIC]

mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve # saves dns resolution time.
query_cache_limit=1M
local-infile=0
skip-external-locking
query_cache_limit=2M
query_cache_size=128M
query_cache_type=1
max_connections=150
interactive_timeout=100
wait_timeout=100
connect_timeout=90
thread_cache_size=256
key_buffer=512M
sort_buffer_size=1M
read_buffer_size=2M ## sort+read x connections + key = memory usage
join_buffer=4M
max_allowed_packet=32M
table_cache=512
read_rnd_buffer_size=1M
tmp_table_size=256M
myisam_sort_buffer_size=256M
max_heap_table_size=256M
long_query_time = 1
log-slow-queries = /var/lib/mysql/slow.log

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192


[isamchk]
key_buffer=96M
sort_buffer=96M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=96M
sort_buffer=96M
read_buffer=16M
write_buffer=16M

[AlexJ]

Run those stats again after 1 day up time. I see you have run it just after one hour.

[AlexJ]

Try this ones:

[color=#282828][font=helvetica, arial, sans-serif][mysqld][/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]datadir=/var/lib/mysql[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]socket=/var/lib/mysql/mysql.sock[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]skip-name-resolve # saves dns resolution time.[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]local-infile=0[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]skip-external-locking[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]query_cache_limit=2M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]query_cache_size=128M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]query_cache_type=1[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]max_connections=100[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]interactive_timeout=100[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]wait_timeout=100[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]connect_timeout=90[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]thread_cache_size=128[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]key_buffer=512M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]sort_buffer_size=1M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]read_buffer_size=2M  [/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]join_buffer=4M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]max_allowed_packet=32M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]table_cache=1024[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]read_rnd_buffer_size=1M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]tmp_table_size=64M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]myisam_sort_buffer_size=256M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]max_heap_table_size=64M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]long_query_time = 2[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]log-slow-queries = /var/lib/mysql/slow.log[/font][/color]

[Luis Manson]

Try this ones:

[color=#282828][font=helvetica, arial, sans-serif][mysqld][/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]datadir=/var/lib/mysql[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]socket=/var/lib/mysql/mysql.sock[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]skip-name-resolve # saves dns resolution time.[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]local-infile=0[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]skip-external-locking[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]query_cache_limit=2M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]query_cache_size=128M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]query_cache_type=1[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]max_connections=100[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]interactive_timeout=100[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]wait_timeout=100[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]connect_timeout=90[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]thread_cache_size=128[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]key_buffer=512M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]sort_buffer_size=1M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]read_buffer_size=2M [/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]join_buffer=4M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]max_allowed_packet=32M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]table_cache=1024[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]read_rnd_buffer_size=1M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]tmp_table_size=64M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]myisam_sort_buffer_size=256M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]max_heap_table_size=64M[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]long_query_time = 2[/font][/color]

[color=#282828][font=helvetica, arial, sans-serif]log-slow-queries = /var/lib/mysql/slow.log[/font][/color]

i removed code tag from alex reply so you can see it bether

[GMIC]

Thanks for the pointers guys appreciated. IPB can't support me anymore eon this as they say it is a server issue. OK this is after 24hr.

Gary worked on my old VPS and did a good job but I need to refine this mysql issue for the new VPS. I will restart after loading the suggested my.cnf

>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>

>> Bug reports, feature requests, and downloads at

http://mysqltuner.com/

>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.5.22-cll

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 559M (Tables: 536)

[--] Data in InnoDB tables: 80K (Tables: 5)

[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)

[!!] Total fragmented tables: 22

-------- Security Recommendations -------------------------------------------

[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------

[--] Up for: 1d 0h 9m 23s (1M q [17.905 qps], 56K conn, TX: 47B, RX: 551M)

[--] Reads / Writes: 40% / 60%

[--] Total buffers: 1.0G global + 8.2M per thread (150 max threads)

[OK] Maximum possible memory usage: 2.2G (74% of installed RAM)

[OK] Slow queries: 1% (20K/1M)

[OK] Highest usage of available connections: 27% (41/150)

[OK] Key buffer size / total MyISAM indexes: 512.0M/75.6M

[OK] Key buffer hit rate: 100.0% (896M cached / 434K reads)

[OK] Query cache efficiency: 56.0% (487K cached / 869K selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 2% (712 temp sorts / 24K sorts)

[!!] Joins performed without indexes: 7640

[!!] Temporary tables created on disk: 40% (9K on disk / 23K total)

[OK] Thread cache hit rate: 99% (41 created / 56K connections)

[!!] Table cache hit rate: 2% (512 open / 25K opened)

[OK] Open file limit used: 10% (884/8K)

[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

[OK] InnoDB data size / buffer pool: 80.0K/128.0M

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

Adjust your join queries to always utilize indexes

Temporary table size is already large - reduce result set size

Reduce your SELECT DISTINCT queries without LIMIT clauses

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

join_buffer_size (> 4.0M, or always use indexes with joins)

table_cache (> 512)

[Gary.]

Its 100% not server issues, IPB sometimes have lack of experience which I have worked on many many servers after there setup and needed a re-configure.

( Trust me )

If your website functions fine when not logged in and you browse about which is extremely quick then the server is ok, When you log in to activate queries ( Only on main index ) and then it goes sluggish then I would say it's something IPB related.

Do not try the ones luis mentioned, Max heap is to low, The other settings are not not important to your problem so only tweak these:

Max heap, TMP, Table cache and join buffer.

Keep the original configuration and set these values.

join_buffer_size=4M
table_cache = 756M
tmp_table_size=128M
Do not go lower on big boards like yourself with the tmp and table cache.

[AlexJ]

His old config has few issues:

query_cache_limit has been defined 2 times.
key_buffer is depreciated now.. I guess with 5.1 or + it's key_buffer_size
Also, thread_cache_size works fine with 128 so why use 256? He doesn't have 50+ connections anyways.

When I was testing for my forums i noticed either with 64M or 128M their is not much different in creating temporary tables.. it remains same i.e. 30-35% for me. If his forum get's slower with 64M he can surely increase to 128M but why to feed extra RAM when it's not worth it and when he is on VPS?

[color=#282828][font=helvetica, arial, sans-serif][size=3][background=rgb(247, 247, 247)]table_cache = 756M[/background][/size][/font][/color]

table_cache of 756M? really? Isn't that to big when you add "M" to it? ..... More or less table_cache is depreciated since 5.1.3.. it's table_open_cache now. Normally table_open_cache around 2000 is good enough, i guess. I use 2600 though cos i host 3 IPB forums on same machine and it works fine.

Might want to set table_definition_cache to 800 as it doesn't uses file descriptor limits and it speeds up opening of tables.

Also what do you use InnoDB for?