Server sql loop or?

[ASTRAPI]

Hello

On my server the performance of read and open topics and everything on the forum is super high and less than half second even on pick times.

My problem is sql when i have only 10 users per minute the database shows load of cpu: 114% and ram 5%.

At pick time it goes to about cpu 170% and ram if i am not wrong is the same about 5%.

The problem is at pick time when i try to edit pin unpin move a topic as it takes more than 20 seconds and sometimes i get 500 intrnal server error.

(researching about that error i set all folders on correct permissions and i use 775 permissions on the places that need it...)

I think something is always hit the sql and don't let it to calm down :(

From cpanel and statistics i was trace the process and got this:

Process 27265 attached - interrupt to quit

select(4, [3], NULL, NULL, NULL)        = 1 (in [3])

fcntl(3, F_SETFL, O_RDWR|O_NONBLOCK)    = 0

accept(3, {sa_family=AF_FILE, path=@""}, [8516025424675864578]) = 444

fcntl(3, F_SETFL, O_RDWR)               = 0

getsockname(444, {sa_family=AF_FILE, path="/var/lib/mysql\1"...}, [8516025424675864604]) = 0

fcntl(444, F_SETFL, O_RDONLY)           = 0

fcntl(444, F_GETFL)                     = 0x2 (flags O_RDWR)

fcntl(444, F_SETFL, O_RDWR|O_NONBLOCK)  = 0

setsockopt(444, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)

futex(0xda3544, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0xda3540, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1

futex(0xda2960, FUTEX_WAKE_PRIVATE, 1)  = 1

select(4, [3], NULL, NULL, NULL)        = 1 (in [3])

fcntl(3, F_SETFL, O_RDWR|O_NONBLOCK)    = 0

accept(3, {sa_family=AF_FILE, path=@""}, [8516025424675864578]) = 70

fcntl(3, F_SETFL, O_RDWR)               = 0

getsockname(70, {sa_family=AF_FILE, path="/var/lib/mysql\1"...}, [8516025424675864604]) = 0

fcntl(70, F_SETFL, O_RDONLY)            = 0

fcntl(70, F_GETFL)                      = 0x2 (flags O_RDWR)

fcntl(70, F_SETFL, O_RDWR|O_NONBLOCK)   = 0

setsockopt(70, SOL_IP, IP_TOS, [8], 4)  = -1 EOPNOTSUPP (Operation not supported)

futex(0xda3544, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0xda3540, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1

futex(0xda2960, FUTEX_WAKE_PRIVATE, 1)  = 1

select(4, [3], NULL, NULL, NULL)        = 1 (in [3])

fcntl(3, F_SETFL, O_RDWR|O_NONBLOCK)    = 0

accept(3, {sa_family=AF_FILE, path=@""}, [8516025424675864578]) = 444

fcntl(3, F_SETFL, O_RDWR)               = 0

getsockname(444, {sa_family=AF_FILE, path="/var/lib/mysql\1"...}, [8516025424675864604]) = 0

fcntl(444, F_SETFL, O_RDONLY)           = 0

fcntl(444, F_GETFL)                     = 0x2 (flags O_RDWR)

fcntl(444, F_SETFL, O_RDWR|O_NONBLOCK)  = 0

setsockopt(444, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)

futex(0xda3544, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0xda3540, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1

futex(0xda2960, FUTEX_WAKE_PRIVATE, 1)  = 0

select(4, [3], NULL, NULL, NULL)        = 1 (in [3])

fcntl(3, F_SETFL, O_RDWR|O_NONBLOCK)    = 0

accept(3, {sa_family=AF_FILE, path=@""}, [8516025424675864578]) = 444

fcntl(3, F_SETFL, O_RDWR)               = 0

getsockname(444, {sa_family=AF_FILE, path="/var/lib/mysql\1"...}, [8516025424675864604]) = 0

fcntl(444, F_SETFL, O_RDONLY)           = 0

fcntl(444, F_GETFL)                     = 0x2 (flags O_RDWR)

fcntl(444, F_SETFL, O_RDWR|O_NONBLOCK)  = 0

My server:

2x Intel Xeon Quad 5405
16GB ram
4x SAS disks raid 10


If any expert have an idea please let me know :)

Thank you

[Gary.]

I seen and done your server not long back and I can honestly say its pushing to the very max, Its one hell of a BIG / Active community with over 200 members per min. Now you have to re-think that you are running an OLD board " 2. Version " with some heavy modifications what require alot of resources. Now I would honestly think about upgrading.

Re-post what your mysql tuner says with your current my.cnf file so we can see what you changed and such to cause issues.

vi /etc/my.cnf

perl mysqltuner.pl

[ASTRAPI]

Its one hell of a BIG / Active community with over 200 members per min.

The problem starts at 300 users per minute as the 300 is my maximum pick time.But it is not in any way a huge forum with 300 users per minute ! :)

Now I would honestly think about upgrading.

IPB 3? Almost double resources? mysqltuner:

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

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.51

[OK] Operating on 64-bit architecture


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

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

[--] Data in MyISAM tables: 1G (Tables: 150)

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

[!!] Total fragmented tables: 17


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

[--] Up for: 22d 8h 47m 17s (189M q [98.267 qps], 11M conn, TX: 9101B, RX: 98B)

[--] Reads / Writes: 69% / 31%

[--] Total buffers: 1.4G global + 288.5M per thread (300 max threads)

[!!] Maximum possible memory usage: 85.9G (547% of installed RAM)

[OK] Slow queries: 0% (100K/189M)

[OK] Highest usage of available connections: 23% (69/300)

[OK] Key buffer size / total MyISAM indexes: 1.0G/501.7M

[OK] Key buffer hit rate: 100.0% (90B cached / 772K reads)

[OK] Query cache efficiency: 42.4% (56M cached / 132M selects)

[!!] Query cache prunes per day: 97140

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

[!!] Joins performed without indexes: 45426

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

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

[!!] Table cache hit rate: 0% (585 open / 93K opened)

[OK] Open file limit used: 17% (702/4K)

[OK] Table locks acquired immediately: 98% (134M immediate / 136M locks)


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

General recommendations:

    Run OPTIMIZE TABLE to defragment tables for better performance

    Reduce your overall MySQL memory footprint for system stability

    Enable the slow query log to troubleshoot bad queries

    Adjust your join queries to always utilize indexes

    Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

  *** MySQL's maximum memory usage is dangerously high ***

  *** Add RAM before increasing MySQL buffer variables ***

    query_cache_size (> 128M)

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

    table_cache (> 1536)

my.cnf:

[mysqld]

skip-innodb

skip-locking

skip-networking

thread_concurrency=16

safe-show-database

tmp_table_size=256M

max_heap_table_size=128M

query_cache_limit=3M

query_cache_size=128M

query_cache_type=1

max_user_connections=300

max_connections=300

interactive_timeout=30

wait_timeout=50

connect_timeout=30

low_priority_updates=1

thread_cache_size=512

key_buffer=2048M

join_buffer_size=256M

max_allowed_packet=64M

table_cache=1024

record_buffer=1M

sort_buffer_size=16M

read_buffer_size=16M

max_connect_errors=10

myisam_sort_buffer_size=512M

concurrent_insert=2


[isamchk]

key_buffer=128M

sort_buffer128M

read_buffer=32M

write_buffer=32M


[myisamchk]

key_buffer=128M

sort_buffer=128M

read_buffer=32M

write_buffer=32M


[mysql]

no-auto-rehash

Also i notice from top command that i have 3,5GB free of ram and the mysqld is using about 220%cpu and 5mb ram about.

On cpanel stats the mysql cpu usage is about 115% on the same time....

My server is:

# 2x Intel Xeon Quad 5405
# 16GB RAM
# 4x SAS disks raid 10

Thank you :)

[Gary.]

I'm going from what your tuner says on this, And I will make some other addjustment what will be marked in red and the ones I added will be marked in blue, The ones I remove will be marked in Black, The main one is join_buffer_size, Thats extremely high.

[mysqld]

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

skip-innodb

skip-locking

skip-networking - Already as default so no need this.

thread_concurrency=16 This does not exist in linux.

safe-show-database This is already default in 5.1 mysql.

tmp_table_size=256M This is quite possible that its to low

max_heap_table_size=128M

query_cache_limit=3M

query_cache_size=128M

query_cache_type=1

max_user_connections=300 Same value as max_default so no need for this.

max_connections=300

interactive_timeout=30

wait_timeout=50

connect_timeout=30

low_priority_updates=1 This is already default in mysql 5.1 so again, No need.

thread_cache_size=512

key_buffer=2048M, Again this value is to high and will consume many memory.

join_buffer_size=256M This is extremely high, Probably why your mysql usuage is high.

max_allowed_packet=64M

table_cache=1024This is to low, You have 16GB memory, around 4086 is reasonable for that.

record_buffer=1M

sort_buffer_size=16M

read_buffer_size=16M

max_connect_errors=10

myisam_sort_buffer_size=512M

concurrent_insert=2

[isamchk]

key_buffer=128M

sort_buffer128M

read_buffer=32M

write_buffer=32M

[myisamchk]

key_buffer=128M

sort_buffer=128M

read_buffer=32M

write_buffer=32M

[mysql]

no-auto-rehash

so you want to use this one below

[mysqld]

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

skip-innodb

tmp_table_size=512M

max_heap_table_size=128M

query_cache_limit=3M

query_cache_size=128M

query_cache_type=1

max_connections=300

interactive_timeout=30

wait_timeout=50

connect_timeout=30

thread_cache_size=512

key_buffer=512M

join_buffer_size=6M

max_allowed_packet=64M

table_cache=4086

record_buffer=1M

sort_buffer_size=16M

read_buffer_size=16M

max_connect_errors=10

myisam_sort_buffer_size=512M

concurrent_insert=2

[isamchk]

key_buffer=128M

sort_buffer128M

read_buffer=32M

write_buffer=32M

[myisamchk]

key_buffer=128M

sort_buffer=128M

read_buffer=32M

write_buffer=32M

[mysql]

no-auto-rehash

Save and restart mysql server:

/etc/init.d/mysql restart

Also IPB is not double the resource. Your community would function on 128MB and run very well, If you set that on your's now you will get memory errors. I have a few big communities on me and with our setup they consume next to nothing. We have over 70IPB boards on us and we run 4GB DDR3. Yet out memory usuage is 20% ish, And thats with shoutcast servers being on the same machine and that has over 100 active streams.

I like IPB compaired to what it was. Now it can only consume to what you give it and how things are installed, Only badly installed scripts will consume all resources.

[ASTRAPI]

Thank you very much for your help i will try them and let you know :)

[ASTRAPI]

What is the difference between

max_user_connections=300
and
max_connections=300

?

What is a good value for both?

If i increase them to 500 it will use mmore memory or cpu or both?

[Gary.]

max_connections should always be higher than max_user_connections.

The value all depends on the server itself, But 300 is way more than enough, I would only use 500 if your planning on hosting eBay or such.

The connections goes of the per based client applications used and called for mysql, Now IPB would go no where near 10 let alone 500.

But also increasing would also allocate more memory to be used, You will probably get a warning saying memory usuage is dangerously high within mysql tuner.

Our values are:

max_user_connections= 50
max_connections=200

Our mysql usuage is around 0.06% with 5.7% memory and currently serving 70 + IPB boards, Plus other applications such as VB, wordpress, Jooluma, Possible more if we looked deeply into it.

Screen shot below is taken at peak time: Current apache connections: 2506