Slow logs being generated - can anyone advise on content?

[markopolo2002]

I've just noticed that although the site seems to run pretty much OK, sometimes when I'm updating or responding to a topic or download, etc, it can take a while for the reply to go through - the page just sits there and does nothing for a short while - sometimes the reply doesn't even go through and I have to re-submit it again. I took a look at the slow queries log and have noted them all for today underneath. If anyone can advise what could be happening here I'd really appreciate it. I've also pasted the output for the mysql tuner just in case anyone is able to spot anything there also ....

Thanks in advance :)
Marko

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

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.95-community-log

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM


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

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

[--] Data in MyISAM tables: 640M (Tables: 436)

[--] Data in MEMORY tables: 8M (Tables: 1)

[!!] Total fragmented tables: 50


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

[--] Up for: 1d 16h 9m 33s (2M q [18.075 qps], 121K conn, TX: 1B, RX: 3B)

[--] Reads / Writes: 56% / 44%

[--] Total buffers: 458.0M global + 15.2M per thread (150 max threads)

[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability

[!!] Maximum possible memory usage: 2.7G (67% of installed RAM)

[OK] Slow queries: 0% (48/2M)

[OK] Highest usage of available connections: 12% (18/150)

[OK] Key buffer size / total MyISAM indexes: 256.0M/184.8M

[OK] Key buffer hit rate: 99.8% (137M cached / 341K reads)

[OK] Query cache efficiency: 75.2% (1M cached / 1M selects)

[!!] Query cache prunes per day: 5599

[OK] Sorts requiring temporary tables: 7% (4K temp sorts / 61K sorts)

[OK] Temporary tables created on disk: 11% (5K on disk / 50K total)

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

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

[OK] Open file limit used: 17% (960/5K)

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


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

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

query_cache_size (> 96M)

table_cache (> 512)

MySQL slow queries ....

Time: 120710 1:08:59

# User@Host: user_name[user_name] @ localhost []

# Query_time: 36 Lock_time: 0


Rows_sent: 1 Rows_examined: 1155211

SELECT COUNT(*) as dl, SUM(dsize) as bw FROM ibf_downloads_downloads WHERE dmid=0


AND dtime > 1341810503 AND dip='xxxxxxxxxxxxx';

#



Time: 120710 1:12:04

# User@Host: eximstats[eximstats] @ localhost []

# Query_time: 74 Lock_time: 0 Rows_sent: 0


Rows_examined: 10950

use eximstats;

update smtp INNER JOIN sends ON (sends.msgid=smtp.msgid) set smtp.processed=3 where


smtp.transport_is_remote=1 and sends.user IN ('-remote-','root','mailman') and smtp.processed=0;

#



Time: 120710 1:19:39

# User@Host: user_name[user_name] @ localhost []

# Query_time: 11 Lock_time: 0


Rows_sent: 1 Rows_examined: 263

use database_name;

SELECT COUNT( * ) as cnt FROM ibf_members m LEFT JOIN


ibf_pfields_content p ON ( p.member_id=m.member_id )

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


) WHERE m.members_l_display_name !='' AND m.members_l_display_name IS NOT NULL AND m.member_group_id NOT IN(5,12)


AND m.member_banned=0 AND ( ! ( members_bitoptions & 1 ) != 0) AND m.members_l_display_name LIKE 'v%';

#



Time: 120710 1:21:49

# User@Host: user_name[user_name] @ localhost []

# Query_time: 17 Lock_time: 0


Rows_sent: 0 Rows_examined: 0

DELETE FROM ibf_reputation_cache WHERE cache_date < 1341638490;

#



Time: 120710 1:29:26

# User@Host: eximstats[eximstats] @ localhost []

# Query_time: 35 Lock_time: 0 Rows_sent: 0


Rows_examined: 10956

use eximstats;

update smtp INNER JOIN sends ON (sends.msgid=smtp.msgid) set smtp.processed=3 where


smtp.transport_is_remote=1 and sends.user IN ('-remote-','root','mailman') and smtp.processed=0;

#



Time: 120710 1:32:00

# User@Host: user_name[user_name] @ localhost []

# Query_time: 17 Lock_time: 0


Rows_sent: 0 Rows_examined: 0

use database_name;

DELETE FROM ibf_downloads_urls WHERE url_expires < 1341898303;

#


Time: 120710 1:32:08

# User@Host: user_name[user_name] @ localhost []

# Query_time: 17 Lock_time: 0


Rows_sent: 47 Rows_examined: 121

SELECT author_id, topic_id FROM ibf_posts WHERE queued=0 AND author_id=4977 AND


topic_id IN(87228,87244,87261,87262,87266,87281,87282,87288,87295,97441,97449,97467,97502,97507,97515);

#



Time: 120710 4:16:34

# User@Host: eximstats[eximstats] @ localhost []

# Query_time: 14 Lock_time: 0 Rows_sent: 0


Rows_examined: 10984

use eximstats;

update smtp INNER JOIN sends ON (sends.msgid=smtp.msgid) set smtp.processed=3 where


smtp.transport_is_remote=1 and sends.user IN ('-remote-','root','mailman') and smtp.processed=0;

#



Time: 120710 6:28:29

# User@Host: user_name[user_name] @ localhost []

# Query_time: 6 Lock_time: 0 Rows_sent:


0 Rows_examined: 0

use database_name;

DELETE FROM ibf_downloads_urls WHERE url_expires < 1341916103;

#



Time: 120710 8:11:39

# User@Host: eximstats[eximstats] @ localhost []

# Query_time: 6 Lock_time: 0 Rows_sent: 0


Rows_examined: 11039

use eximstats;

update smtp INNER JOIN sends ON (sends.msgid=smtp.msgid) set smtp.processed=3 where


smtp.transport_is_remote=1 and sends.user IN ('-remote-','root','mailman') and smtp.processed=0;

#



Time: 120710 8:15:08

# User@Host: user_name[user_name] @ localhost []

# Query_time: 7 Lock_time: 0 Rows_sent:


10 Rows_examined: 103

use database_name;

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,rep_index.rep_rating as


has_given_rep,rep_cache.rep_points, rep_cache.rep_like_cache,cca.*,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_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_posts cca ON (


cca.cache_content_id=p.pid )

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


p.topic_id=97507 AND p.queued=0 ORDER BY p.pid asc LIMIT 10,10;

#



Time: 120710 8:18:28

# User@Host: user_name[user_name] @ localhost []

# Query_time: 6 Lock_time: 0 Rows_sent:


1 Rows_examined: 21931

SELECT max(record_id) as for_file FROM ibf_downloads_files_records WHERE record_type='ssupload'


AND record_location='monthly_02_2011/68f53869623f34aa9b8dd3f603a3a74d.gif' AND record_id<>44009;

[Gary.]

[color=#666600][size=2]--------[/size][/color][color=#000000][size=2] [/size][/color][color=#660066][size=2]Recommendations[/size][/color][color=#000000][size=2] [/size][/color][color=#666600][size=2]-----------------------------------------------------[/size][/color]

[color=#660066]General[/color][color=#000000] recommendations[/color][color=#666600]:[/color]

[color=#660066]Run[/color][color=#000000] OPTIMIZE TABLE to defragment tables [/color][color=#000088]for[/color][color=#000000] better performance

[/color][color=#660066]Increase[/color][color=#000000] table_cache gradually to avoid file descriptor limits

[/color][color=#660066]Variables[/color][color=#000000] to adjust[/color][color=#666600]:[/color]

[color=#000000]query_cache_size [/color][color=#666600](>[/color][color=#000000] [/color][color=#006666]96M[/color][color=#666600]) [/color]

[color=#000000][size=2]table_cache [/size][/color][color=#666600][size=2](>[/size][/color][color=#000000][size=2] [/size][/color][color=#006666][size=2]512[/size][/color][color=#666600][size=2])[/size][/color]

Have you done or added this to your my.cnf as it requested ?

Gary.

[markopolo2002]

Hey Gary, I didn't yet, no, because I didn't see how those limits could cause slow queries, unless I'm missing something? :thumbsup:

[markopolo2002]

Well, I raised this with the host and as per usual, there isn't anything wrong - all seems fine at their end which I'm fed up with now - it doesn't matter what host we use, it's normally the same answer everywhere - "looks fine our end". The latest from the host was "the site is loading very quickly for us" and ...

I see that you have been hitting the hard limit for memory. This may be causing the slowness you are seeing at times. I think it would be best to drop down the query_cache_size set for mysql, as the current setting of 128M is a bit large for your current VPS package. Would it be alright if I dropped that down to 64M for you?

.... which hasn't made a difference and we're still generating slow-queries!!

Have you done or added this to your my.cnf as it requested ?

Gary.

Yes, did this Gary - new output from mysqltuner ...

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

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.95-community-log

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM


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

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

[--] Data in MyISAM tables: 649M (Tables: 437)

[--] Data in MEMORY tables: 8M (Tables: 1)

[!!] Total fragmented tables: 57


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

[--] Up for: 17h 2m 33s (804K q [13.107 qps], 51K conn, TX: 2B, RX: 1B)

[--] Reads / Writes: 55% / 45%

[--] Total buffers: 682.0M global + 17.2M per thread (150 max threads)

[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability

[!!] Maximum possible memory usage: 3.2G (79% of installed RAM)

[OK] Slow queries: 0% (23/804K)

[OK] Highest usage of available connections: 10% (15/150)

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

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

[OK] Query cache efficiency: 62.2% (319K cached / 514K selects)

[!!] Query cache prunes per day: 1136

[OK] Sorts requiring temporary tables: 6% (1K temp sorts / 22K sorts)

[OK] Temporary tables created on disk: 11% (2K on disk / 21K total)

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

[OK] Table cache hit rate: 33% (538 open / 1K opened)

[OK] Open file limit used: 18% (986/5K)

[OK] Table locks acquired immediately: 99% (519K immediate / 519K locks)


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

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

MySQL started within last 24 hours - recommendations may be inaccurate

Variables to adjust:

query_cache_size (> 64M)

I have 2Gb RAM (4Gb burst) on a VPS with (according to output from "cat /proc/cpuinfo") 11 CPU's - Intel® Xeon® CPU E5620 @ 2.40GHz if this makes any difference :smile: my.cnf as follows ...

[mysqld]

port = 3306

socket = /var/lib/mysql/mysql.sock

log-slow-queries = /var/log/mysql-slow.log

long_query_time=5

skip-locking

max_connections = 150

max_user_connections = 150

key_buffer = 512M

max_allowed_packet = 16M

max_connect_errors = 10

thread_concurrency = 2

concurrent_insert = 2

table_lock_wait_timeout = 35

wait_timeout = 50

net_read_timeout=60

net_write_timeout=60

interactive_timeout=100

connect_timeout = 100

tmp_table_size = 96M

max_heap_table_size = 96M

table_cache = 800

open_files_limit = 5400

join_buffer_size = 4M

sort_buffer_size = 4M

read_buffer_size = 8M

thread_cache_size = 16

wait_timeout = 900

read_rnd_buffer_size = 1M

bulk_insert_buffer_size = 8M

net_buffer_length = 4M

thread_stack = 256K

skip-bdb

skip-innodb

query_cache_limit = 1M

query_cache_size = 64M

query_cache_type = 1

query_prealloc_size = 131072

query_alloc_block_size = 65536

default-storage-engine = MyISAM


[mysqldump]

quick

max_allowed_packet = 16M


[mysql]

no-auto-rehash

#safe-updates


[myisamchk]

key_buffer = 128M

sort_buffer = 96M

read_buffer = 16M

write_buffer = 16M


[mysqlhotcopy]

interactive-timeout

[Elly]

I suggest moving your tables to InnoDB, since your database is pretty small ~600MB, you could raise innodb_buffer_pool_size to 1GB and also take advantage of row locking. You can then install sphinx search in response to no fulltext search.

The slow queries don't look particularly strange, it could be high IOwait or something on your server node, again it's hard to say.

If you like you can PM me your server details for me to take a look.

[Grumpy]

I'll admit I'm not diligent enough to look through all of the slow queries for someone over the Internet forums... But, I did look at a large number of them.

One of it was a user search and slowness is valid.
Some of them were multiple delete/updates in one query and thus slowness is valid. Which may or may not indicate some write locking issues.
I don't have IP.Downloads, so I don't know how its tables are structured, but I don't think you're referencing any indexes either (or at least a fine-grained index).

Overall, I don't think your slow queries are queries that necessarily should be fast. The very fact that you have slow queries isn't a bad thing, if there's too many of it, it's a problem.

If you find your site consistently slow, try looking at your top output. If your %wa if above 1%, it's likely that your disk capability is not optimal and you may need to look for better solutions. That being another vps or a dedi server.