Problems with mysql with more than 900 users online

[SecondSight]

Hello ! :)

My server is a 64 bit Bi Xeon Quad 8x 2.33+ with 16GB RAM and two 1TB HDD (RAID), with :
Apache 2.0.63
PHP 5.2.9
MySQL 5.0.89
EAccelerator
Sphinx as the search engine.

At the moment, I'm having between 900 and 1000 users online and most of the time everything is ok and the server load is about 2/2.5. But at moments, I'm having problems with MySQL using 100 to 150 % of the CPU and the server load going up to 10.

I disabled the search engine but this didn't fix the problem at all... I wonder if this is not caused by the "View new content" tool which doesn't use the Sphinx search engine ?


I have MySQLTuner installed. I ran it and got :

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

[--] Skipped version check for MySQLTuner script

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

[OK] Operating on 64-bit architecture

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

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

[--] Data in MyISAM tables: 4G (Tables: 250)

[!!] Total fragmented tables: 28

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

[--] Up for: 26d 10h 8m 8s (305M q [134.015 qps], 11M conn, TX: 10814B, RX: 253B)

[--] Reads / Writes: 22% / 78%

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

[OK] Maximum possible memory usage: 10.5G (67% of installed RAM)

[OK] Slow queries: 0% (3K/305M)

[OK] Highest usage of available connections: 30% (152/500)

[OK] Key buffer size / total MyISAM indexes: 3.0G/2.5G

[OK] Key buffer hit rate: 100.0% (13B cached / 3M reads)

[OK] Query cache efficiency: 53.9% (57M cached / 107M selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (3K temp sorts / 1M sorts)

[!!] Temporary tables created on disk: 30% (42K on disk / 138K total)

[OK] Thread cache hit rate: 99% (152 created / 11M connections)

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

[OK] Open file limit used: 35% (2K/6K)

[!!] Table locks acquired immediately: 55%

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

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

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

Optimize queries and/or use InnoDB to reduce lock wait

Variables to adjust:

table_cache (> 3072)

I also ran TuningPrimer and I got :

MySQL Version 5.0.90-community-log x86_64

Uptime = 26 days 10 hrs 16 min 26 sec

Avg. qps = 134

Total Questions = 306040477

Threads Connected = 3

SLOW QUERIES

The slow query log is enabled.

Current long_query_time = 3 sec.

You have 3566 out of 306040519 that take longer than 3 sec. to complete

Your long_query_time seems to be fine

BINARY UPDATE LOG

The binary update log is NOT enabled.

You will not be able to do point in time recovery

See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

WORKER THREADS

Current thread_cache_size = 512

Current threads_cached = 150

Current threads_per_sec = 0

Historic threads_per_sec = 0

Your thread_cache_size is fine

MAX CONNECTIONS

Current max_connections = 500

Current threads_connected = 2

Historic max_used_connections = 152

The number of used connections is 30% of the configured maximum.

Your max_connections variable seems to be fine.

No InnoDB Support Enabled!

MEMORY USAGE

Max Memory Ever Allocated : 4.73 G

Configured Max Per-thread Buffers : 4.02 G

Configured Max Global Buffers : 3.50 G

Configured Max Memory Limit : 7.53 G

Physical Memory : 15.63 G

Max memory limit seem to be within acceptable norms

KEY BUFFER

Current MyISAM index space = 2.48 G

Current key_buffer_size = 3.00 G

Key cache miss rate is 1 : 3651

Key buffer free ratio = 69 %

Your key_buffer_size seems to be fine

QUERY CACHE

Query cache is enabled

Current query_cache_size = 512 M

Current query_cache_used = 143 M

Current query_cache_limit = 12 M

Current Query cache Memory fill ratio = 27.97 %

Current query_cache_min_res_unit = 4 K

MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS

Current sort_buffer_size = 4 M

Current read_rnd_buffer_size = 1 M

Sort buffer seems to be fine

JOINS

Current join_buffer_size = 2.00 M

You have had 193 queries where a join could not use an index properly

You have had 208 joins without keys that check for key usage after each row

You should enable "log-queries-not-using-indexes"

Then look for non indexed joins in the slow query log.

If you are unable to optimize your queries you may want to increase your

join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when

ANY joins not using indexes are found.

OPEN FILES LIMIT

Current open_files_limit = 6654 files

The open_files_limit should typically be set to at least 2x-3x

that of table_cache if you have heavy MyISAM usage.

Your open_files_limit value seems to be fine

TABLE CACHE

Current table_cache value = 3072 tables

You have a total of 267 tables

You have 2093 open tables.

The table_cache value seems to be fine

TEMP TABLES

Current max_heap_table_size = 3.00 G

Current tmp_table_size = 3.00 G

Of 96020 temp tables, 30% were created on disk

Perhaps you should increase your tmp_table_size and/or max_heap_table_size

to reduce the number of disk-based temporary tables

Note! BLOB and TEXT columns are not allow in memory tables.

If you are using these columns raising these values might not impact your

ratio of on disk temp tables.

TABLE SCANS

Current read_buffer_size = 1 M

Current table scan ratio = 164 : 1

read_buffer_size seems to be fine

TABLE LOCKING

Current Lock Wait ratio = 1 : 1

You may benefit from selective use of InnoDB.

If you have long running SELECT's against MyISAM tables and perform

frequent updates consider setting 'low_priority_updates=1'

If you have a high concurrency of inserts on Dynamic row-length tables

consider setting 'concurrent_insert=2'.

Here is my my.cnf :

[mysqld]

datadir=/var/lib/mysql

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

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

long_query_time = 3

log-long-format

skip-locking

skip-innodb

query_cache_limit=12M

query_cache_size=512M

query_cache_type=1

tmp_table_size=3072M

max_heap_table_size=3072M

max_user_connections=500

max_connections=500

interactive_timeout=100

wait_timeout=200

connect_timeout=100

thread_cache_size=512

key_buffer=3072M

join_buffer=2M

max_allowed_packet=16M

table_cache=3072

record_buffer=4M

sort_buffer_size=4M

read_buffer_size=1M

read_rnd_buffer_size=1M

max_connect_errors=10

thread_concurrency=8

myisam_sort_buffer_size=64M

server-id=1

#[mysql.server]

#user=mysql

#basedir=/var/lib

[safe_mysqld]

err-log=/var/log/mysqld.log

pid-file=/var/lib/mysql/mysql.pid

open_files_limit=8192

#[mysqldump]

#quick

#max_allowed_packet=16M

[mysql]

no-auto-rehash

[isamchk]

key_buffer=64M

sort_buffer=64M

read_buffer=16M

write_buffer=16M

[myisamchk]

key_buffer=64M

sort_buffer=64M

read_buffer=16M

write_buffer=16M

[mysqlhotcopy]

interactive-timeout

Can you give me pieces of advice to fix my problems ?

Thank you ! :)

[Graeme S.]

I strongly suggest you submit a ticket for something like this :).

[Nixniz]

mysql using cpu %100 ? this is not right

when i testing a attack on my server 2000 Ppl just using %25 mysql :huh:

[SecondSight]

Hello ! :)

Below is an example. When this happens the other figures are bad too (idle, wa, etc.) :

[Akabane]

Hello french mate (but damn, I need to find a job under the sun, Paris is SO under the rain today...)

So, I did the upgrade yesterday, and I found a BIG problem with the "latest thread" block. Simply put, it's killing my board in 20 sec.



mysql> explain SELECT SQL_NO_CACHE t.tid, t.title, t.posts, t.start_date as post_date, t.views, t.title_seo, t.starter_name,m.member_id, m.members_display_name, m.members_seo_name FROM ibf_topics t LEFT JOIN ibf_forums f ON ( f.id=t.forum_id ) LEFT JOIN ibf_members m ON ( m.member_id=t.starter_id ) WHERE t.approved=1 and t.state != 'closed' and (t.state != 'link') AND t.forum_id IN (2,5,39,40,41,43,33,36,7,18,9,10,8,11,12,13,14,15,17,16,26,27,28,22,23,24,29,30,31,37,44,48,46,47,25,42,32,34,38,45) ORDER BY t.tid DESC LIMIT 0,10;
+----+-------------+-------+--------+-------------------------------------------------+----------+---------+----------------------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------------------+----------+---------+----------------------+-------+-----------------------------+
| 1 | SIMPLE | t | range | forum_id,last_x_topics,last_post,approved,state | forum_id | 2 | NULL | 41504 | Using where; Using filesort |
| 1 | SIMPLE | f | eq_ref | PRIMARY,id | PRIMARY | 2 | cafzone.t.forum_id | 1 | Using index |
| 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 3 | cafzone.t.starter_id | 1 | |
+----+-------------+-------+--------+-------------------------------------------------+----------+---------+----------------------+-------+-----------------------------+
3 rows in set (0.05 sec)

Filesort ? WTF ? That block takes around 2sec to generate on my little baby server...

Check if you have that prob too ? Just my 2 cents. Damn I miss your island now... (was there in feb ;) )

[SecondSight]

Hello ! :)

Things have been doing much better since I :
- modified my.cnf
- modified Apache configuration

But I noticed I had a problem tonight again with httpd being restarted by PRM after a high server load.

How can I check if I have this problem too ?

[SecondSight]

Hello ! :)

I've had a look in the mysql-slow.log file and I find dozens of lines like this one :

# Time: 100616 17:53:46

# User@Host: xxxxxxxx_forums[xxxxxxx_forums] @ localhost []

# Query_time: 22 Lock_time: 0 Rows_sent: 1 Rows_examined: 12168

use xxxxxxx_forums;

SELECT COUNT(*) as count, MIN(post_date) as min FROM ibf_posts WHERE author_id=39 AND post_date > 1276617204;

How should I proceed now ?

Thank you ! :)

[Fast Lane!]

Go to a member's profile that has a ton of posts (find your top poster). Click the profile tab to see their last 5 posts.... *poof* your server will stall while the posts table locks for a while. Loads will soar. Welcome to an unoptimized query :). Despite a time restriction this query can bring my server with similar specs to its knees.

[Computec]

Go to a member's profile that has a ton of posts (find your top poster). Click the profile tab to see their last 5 posts.... *poof* your server will stall while the posts table locks for a while. Loads will soar. Welcome to an unoptimized query :). Despite a time restriction this query can bring my server with similar specs to its knees.

We've the same problem - massive query locks everytime we reach an user peak (12000+ sessions with 1600 logged in members and 10400 guests for example, at 15 minute cut off, Spiders and Bots are already blocked from guests) while the board is doing some caching or cleaning stuff. Also i can't activate the topic marking function - this crashes our website immediately. The session table is heap, the tasks are reduced to one of our ten webservers, where the board is running simultanous on (load balancing), xcache is running (also tried to use memcache and eaccelerator). The database server is realy erm .. i would say powerfull (exempted mysql is running on it ^^) - similar to the hardware SecondSight described but with multi cpu power. Ok posts and topics table are still running as myisam, but i dont think that should be a problem - sphinx is running as search engine. So i hope some query and index tweaks will be implemented in future versions.

Board version is 3.0.5.

Btw.: What is this query for?
SELECT COUNT(*) as cnt, MIN(last_post) as lastItem FROM ibf_topics WHERE forum_id=10 AND approved=1 AND tid NOT IN(some id) AND last_post > 0 AND state != 'link'

[MarcusInMd]

We've the same problem - massive query locks everytime we reach an user peak (12000+ sessions with 1600 logged in members and 10400 guests for example, at 15 minute cut off, Spiders and Bots are already blocked from guests) while the board is doing some caching or cleaning stuff. Also i can't activate the topic marking function - this crashes our website immediately. The session table is heap, the tasks are reduced to one of our ten webservers, where the board is running simultanous on (load balancing), xcache is running (also tried to use memcache and eaccelerator). The database server is realy erm .. i would say powerfull (exempted mysql is running on it ^^) - similar to the hardware SecondSight described but with multi cpu power. Ok posts and topics table are still running as myisam, but i dont think that should be a problem - sphinx is running as search engine. So i hope some query and index tweaks will be implemented in future versions.

Board version is 3.0.5.

Btw.: What is this query for?

SELECT COUNT(*) as cnt, MIN(last_post) as lastItem FROM ibf_topics WHERE forum_id=10 AND approved=1 AND tid NOT IN(some id) AND last_post > 0 AND state != 'link'

That is insane. We are having load issues with 3.x when we reach around 1200 to 1500 users (one server at the moment though) Getting ready to redeploy our database server now that it's upgraded. I am blaming the problems on Myisam for posts and topics. I hope that innodb will correct the problems.

[MarcusInMd]

Go to a member's profile that has a ton of posts (find your top poster). Click the profile tab to see their last 5 posts.... *poof* your server will stall while the posts table locks for a while. Loads will soar. Welcome to an unoptimized query :). Despite a time restriction this query can bring my server with similar specs to its knees.

Oddly enough this does nothing to our server load, but we are using sphinx.

[mat206]

Btw.: What is this query for?

SELECT COUNT(*) as cnt, MIN(last_post) as lastItem FROM ibf_topics WHERE forum_id=10 AND approved=1 AND tid NOT IN(some id) AND last_post > 0 AND state != 'link'

I suppose you could try adding some indexes on that table. Maybe forum_id, approved, and state together? e.g.

ALTER TABLE `topics` ADD INDEX ( `state` , `forum_id` , `approved` ) ;

[Computec]

I suppose you could try adding some indexes on that table. Maybe forum_id, approved, and state together? e.g.

ALTER TABLE `topics` ADD INDEX ( `state` , `forum_id` , `approved` ) ;

Hi,

thank you for the advice. :) I've added the index.

Today is wednesday .. as usual the german World of Warcraft realm servers were shut down for extended maintainance, today till 12 p.m. so our board reached a user peak and locked again with over 3000 mysql connections for ipb because of 12100 user sessions (1200 logged in users - 15 minute cut off) ... :\

[mat206]

Hi,

thank you for the advice. :) I've added the index.

Today is wednesday .. as usual the german World of Warcraft realm servers were shut down for extended maintainance, today till 12 p.m. so our board reached a user peak and locked again with over 3000 mysql connections for ipb because of 12100 user sessions (1200 logged in users - 15 minute cut off) ... :

Can you describe your server setup? What are the specs on the machines you are running? Also in the index I suggested perhaps the topic id has to be added as well. I don't have a setup under that much load yet but if you begin to figure out what works / doesnt work could you please report back to this topic?

[Computec]

2 x Intel Xeon X5460 (each with 4 cores @ 3,16GHZ ) ; 10x2048 MB RAM ; Discspace 12x36GB 15k 3Gb/s SAS an LSI MegaRAID ; running Lenny - always the latest kernel and latest security patches ; latest mysql ; everything is up to date ; nothing else then the database is running on this machine and its simultaneously requested by 10 webservers (load balancing) - average 25-30 connections at once, 3000+ on session peaks by ipb :-P

Some other websites (by us) are also running on it, but the ipb stuff is the only locked "mysql is going crazy" stuff when the site reaches a sessions peak.

[MarcusInMd]

2 x Intel Xeon X5460 (each with 4 cores @ 3,16GHZ ) ; 10x2048 MB RAM ; Discspace 12x36GB 15k 3Gb/s SAS an LSI MegaRAID ; running Lenny - always the latest kernel and latest security patches ; latest mysql ; everything is up to date ; nothing else then the database is running on this machine and its simultaneously requested by 10 webservers (load balancing) - average 25-30 connections at once, 3000+ on session peaks by ipb :-P

Some other websites (by us) are also running on it, but the ipb stuff is the only locked "mysql is going crazy" stuff when the site reaches a sessions peak.

We have had the same issues with IPB 3.x. Next week I will be converting the posts and topics tables over to Innodb. For a forum with the kind of traffiic that we see as well as you do, the only suggestion that really comes up on these forums is to convert those tables from MyIsam which is locking up things to Innodb.

[mat206]

We have had the same issues with IPB 3.x. Next week I will be converting the posts and topics tables over to Innodb. For a forum with the kind of traffiic that we see as well as you do, the only suggestion that really comes up on these forums is to convert those tables from MyIsam which is locking up things to Innodb.

MySQL uses table-level locking for MyISAM, MEMORY, and MERGE tables, and row-level locking for InnoDB tables. So using HEAP for sessions is probably a bad idea as well I would think because you are using table level locking for everything. The best performance would probably come from InnoDB as you suggested.

I wrote some custom software that used HEAP tables and ran into problems because of the table level locking.. you'd think because it's an in memory table it would work better but the fact that the entire table can be locked makes it pretty terrible as you ramp up session traffic.

[SecondSight]

By the way, my problem of high cpu figures has been fixed by IPS :
http://community.invisionpower.com/topic/319295-upgraded-to-312-and-still-have-problems-with-ibf-core-item-markers/page__view__findpost__p__2007325

[Computec]

By the way, my problem of high cpu figures has been fixed by IPS :

http://community.invisionpower.com/topic/319295-upgraded-to-312-and-still-have-problems-with-ibf-core-item-markers/page__view__findpost__p__2007325

Nice, but as i wrote, the marking function isn't activated (because of that issue). But we've had a user peak on friday (12.000 Users / 15 minute timeout / Spiders & bots blocked). Result was "Too many connections" .. again.

Oh btw. i think thats no posts table, or memory problem .. its a problem with the users session handling, when every click is forcing a update on the database...

[Computec]

Wednesday is patch day in world of warcraft - and as i foretell, the drastically database connection amount forced by ipb queries will happen again...

[Gary.]

12000 users in the past 15 mins ? You must have some incredible server to handle that, I'm not talking a 400euro a month one but in the thousand mark ! LOL.

sure you wasent under a Dos / flood / http attack ? :unsure:

[Computec]

12000 users in the past 15 mins ? You must have some incredible server to handle that, I'm not talking a 400euro a month one but in the thousand mark ! LOL.

Haha .. Mark ;)
1X webservers, highend multi cpu database server and other stuff.

sure you wasent under a Dos / flood / http attack ? :unsure:

Iam definitely sure - the situation is always forced by real user peaks ... usualy on wednesdays when the WoW servers are going down in europe. Many (mostley german) users are heading for our site to spend their time there. As i've already explained the support, we're using the userhandler (SDK) on the whole side, not only for the forum. I think because the session handler is also concidering guests, the database connections including board update stuff and update queries for user sessions are growing up to over 4.000. Queries of our own source codes aren't that mentionable while the connection peaks happen... most of the queries are coming from the board.