mysqli added. server load / sql spikes increased

Mr.Rick

Mr.Rick

Active member
anyone else seeing something like this?

CPU load has ran at around 0.85 forever, years. Traffic has increased but with cdn, css styles and optimization this box has been rock steady.

When we went to migrate to xF mysql needed to be recompiled with i ? (not a sys admin).
Since then cpu load has been about 4x normal

Mysql has taken up upto 250% of cpu in spikes which is new.

Slow query log showing nothing. The items that were showing up from a pluggin weve had installed for ages has been removed.
Intially we thought "Enable Delayed Insert SQL Queries" was the issue, so the 4 xF sites have had it disabled.

Thoughts?

specs:
4 quad core cpus - Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
6gb memory
raid drives
httpd 2.2.24 (Unix)
mysql 5.0.96
Server load 8.95 (16 CPUs) as of posting this


NOTE: its using upto 50% cpu at times for this:
/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/web2.xxxxxxx.com.pid --skip-external-locking
 
To be honest, very hard to diagnose something like that, mysqli shouldn't represent the behavior your experiencing.

I would suggest wiping down your configurations and re-compiling them from scratch to ensure everything is correct.
 
Check to see if you have - log_queries_not_using_indexes = 1 - in my.cnf

If you have, comment it out (or remove it) and restart MySQL.

Cheers,
Shaun :D
 
Slavik said:
Also, 6gb memory on a 16 core system is that a typo?
Click to expand...

Hi Slavik, yes, 6 gbs of ram on this box.
This box has been a rock for some time. Since the sys admin recompiled sql for xF it struggles.


Clickfinity, this is how my.cnf appears. That line doesn't appear.

[mysqld]
set-variable = max_connections=500
safe-show-database
skip-innodb
skip-name-resolve
skip-host-cache
#investigate skip-locking
max_connections = 600
key_buffer = 384M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 2M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 3600
connect_timeout = 800
tmp_table_size = 1000M
#tmp_table_size = 48M
max_allowed_packet = 64M
max_connect_errors = 10
bulk_insert_buffer_size = 8M
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
thread_concurrency = 4
tmpdir = /usr/tmp
long_query_time = 5
ft_min_word_len = 2
log-slow-queries=/var/lib/mysql/slow.log
Click to expand...


Last night alone my box notified me of 12high load situations

server-load.webp
L: 1 minute load average
P: number of processes
% : Swap usage
PPS in: Packets per second inbound
PPS out: Packets per second outbound.

Only thing that we have changed is the recompile for mysql. As part of the trouble shooting we went as far as (not in this exact order):
- remove similar threads from the big boards
- removing (not disabling) certain querie intensive pluggins.
- shutting down the most active bb
- shutting off xF sites (low traffic anyways)
- commissioned another box (vps) and moved 10 sites off (low traffic, tons of static files)

I think I may need some sys admin references. The guy I have been working with since 2005 isn't readily available :/
 
Wait, I just noticed "skip-innodb" in there, so I assume you don't even have that running. That's not ideal - all the tables probably are MyISAM. I don't see a huge amount of MyISAM tuning either, which can make really significant differences.

But generally, you want InnoDB these days. Aside from having a lot of better features, it tends to be faster.
 
Hi Mike

I almost wonder if the setup is in place because of the vB3 sites we run on that box??
Now the box is a mix of xF and vB3.

Much of the tuning, changes are above my head and I think I need to find someone available to help out who has an understanding of vB and xF. With the exception of 1 sys admin that I work with (when around), the others arent familiar with the applications.
 
You should enable InnoDB and then convert your XenForo tables to it (apart from the small few which arn't).

Also, installing MariaDB should be of benefit to you if you arn't already using it, which offers much optimised MyISAM and InnoDB Performance.
 
I'd increase key_buffer to 1000M and reduce tmp_table from 1000 to 128M - but as others have said, enable InnoDB and convert the respective XF tables, then come back and ask for tuning help. (y)

Cheers,
Shaun :D
 
I need to be thoughtful of the vB3 installations on the box. Do you foresee these changes causing issues for those installations?

This is what my cpu is living through right now, spikes as high as 250%
cpu.webp
 
Slightly reworked my.cnf also.

Using many of the values you already had, altered a couple, added innoDB stuff. Anyone spot anything I may have missed? Think I got it all there. The innoDB buffer pool I will change once knowing how much memory is available to play with.

Code: 
[mysqld]
 
# GENERAL #
safe-show-database
skip-name-resolve
skip-host-cache
max_connections = 600
wait_timeout = 3600
connect_timeout = 800
max_allowed_packet = 64M
max_connect_errors = 10
bulk_insert_buffer_size = 8M
query_prealloc_size = 16384
query_alloc_block_size = 16384
thread_concurrency = 4
tmpdir = /usr/tmp
long_query_time = 5
ft_min_word_len = 2
log-slow-queries=/var/lib/mysql/slow.log
 
 
# MYISAM STUFF #
myisam_sort_buffer_size = 32M
 
 
# CACHES AND LIMITS #
tmp_table_size = 128M
max_heap_table_size = 32M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
table_open_cache = 1800
thread_cache_size = 384
key_buffer_size = 1024M
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1
 
 
# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size          = 256M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 2G
 
I can say this is one poorly server :D

Just spent 5 hours getting things up to a mostly workable level. A few changes to make tomorow, and some small things to investigate, but, we're getting there!
 
After investigating, it seems the issue is lying with apache and not with mysql. (though ive updated everything to mysql 5.5 with Percona)

Ive also added MPM forks and various other tweaks to see if it will help, but I think the core issue is just voume of traffic on an out the box apache setup.

Anyway, things seem better in terms of the loads arn't spiking for the moment and have sysstat on 10 minute intervals writing out loads to see how it behaves over the next 24 hours.
 
Slavik said:
After investigating, it seems the issue is lying with apache and not with mysql. (though ive updated everything to mysql 5.5 with Percona)

Ive also added MPM forks and various other tweaks to see if it will help, but I think the core issue is just voume of traffic on an out the box apache setup.

Anyway, things seem better in terms of the loads arn't spiking for the moment and have sysstat on 10 minute intervals writing out loads to see how it behaves over the next 24 hours.
Click to expand...
nginx for the world. :D
 
You must log in or register to reply here.

Similar threads

CivilWarTalk
Issues with Server Load, Suggestions...
Replies
10
Views
1K
hellreturn
H
Back
Top Bottom