Sudden increase in server load at specific times

[abdobasha2004]

Originally Posted by snakes1100

Can you take a snapshot of "top" running when its under heavy load?

Hit 1 after top starts so we can see all the core's.

now load is 40

the cause of this load is mysql

[mysqld]
query_cache_size=256M
table_cache=80000
max_connections=5000
open_files_limit=5400
log-slow-queries="/var/lib/mysql/slow.log"
innodb_file_per_table=1
thread_cache_size=4
interactive_timeout=60
wait_timeout=60
default-storage-engine=MyISAM
max_heap_table_size = 32M
local-infile=0
max_allowed_packet = 16MB


for my.cnf

and this is what mysql tuner says.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 333)
[--] Data in InnoDB tables: 224K (Tables: 14)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 16M (Tables: 111)
[!!] Total fragmented tables: 19

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 20h 0m 46s (32M q [447.256 qps], 2M conn, TX: 1387B, RX: 9B)
[--] Reads / Writes: 63% / 37%
[--] Total buffers: 424.0M global + 2.8M per thread (5000 max threads)
[OK] Maximum possible memory usage: 13.8G (59% of installed RAM)
[OK] Slow queries: 0% (979/32M)
[OK] Highest usage of available connections: 33% (1686/5000)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.1G
[OK] Key buffer hit rate: 99.0% (1B cached / 9M reads)
[OK] Query cache efficiency: 79.3% (16M cached / 21M selects)
[!!] Query cache prunes per day: 11559
[OK] Sorts requiring temporary tables: 0% (279 temp sorts / 1M sorts)
[OK] Temporary tables created on disk: 1% (3K on disk / 217K total)
[OK] Thread cache hit rate: 93% (182K created / 2M connections)
[OK] Table cache hit rate: 92% (15K open / 16K opened)
[OK] Open file limit used: 9% (15K/165K)
[OK] Table locks acquired immediately: 99% (16M immediate / 16M locks)
[OK] InnoDB data size / buffer pool: 224.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
Increasing the query_cache size over 128M may reduce performance
Variables to adjust:
query_cache_size (> 256M) [see warning above]

[Simon Lloyd]

I think you may have a corrupt table as that server right now is hardly under load.

[snakes1100]

Originally Posted by Simon Lloyd

I think you may have a corrupt table as that server right now is hardly under load.

What picture are you looking at?

-----------------------------------------

As your server's CPU is using over 60% on each core & your going defunct on almost every php pid, i would start by removing suPHP & running apache/php as a DSO.

You would also need to optimize that server.

Your my.cnf settings are badly done, if your host is the one doing it, i would put a stop to that, as they dont have a clue on how to optimize a server.

This variable (thread_cache_size=4) isnt even used under the [mysqld] section & its set wrong anyways.

[Simon Lloyd]

Originally Posted by snakes1100

What picture are you looking at?.

His cpu usage and memory usage are fairly low, i suspect the load shown at the top is the entire load of the shared server.

There again i'm not wearing my glasses

--------------- Added [DATE]1381342875[/DATE] at [TIME]1381342875[/TIME] ---------------

I'm also interested to know why you have mixed tables, you have a few InnoDB tables was that by choice, by install, your hosts...etc?

[abdobasha2004]

Originally Posted by snakes1100

What picture are you looking at?

-----------------------------------------

As your server's CPU is using over 60% on each core & your going defunct on almost every php pid, i would start by removing suPHP & running apache/php as a DSO.

You would also need to optimize that server.

Your my.cnf settings are badly done, if your host is the one doing it, i would put a stop to that, as they dont have a clue on how to optimize a server.

This variable (thread_cache_size=4) isnt even used under the [mysqld] section & its set wrong anyways.

Do you have a recommended configuration plz
?

[snakes1100]

Is this a shared server?

Simon, in his first post, he states dedicated.

As far as recommendations, i would start by removing suPHP & running apache/php as a dso, you really need to find out whats going on.

As far as SQL goes, its not as simple as me telling you what to use here, the stats you posted arent enough.

As to mixed tables, there is nothing wrong with that.

[abdobasha2004]

It is dedicated
are these the recommended settings to start with?

[snakes1100]

Start by removing suPHP & running apache/php as a DSO.

Then you can start checking your server logs for more details as to whats going on.

There is really no way to give you recommendations, as you havent provided much to work with in terms of settings on the server, apache, php, mysql & the OS settings for things like open file limits or if your running any caching programs etc.

Tuning a server entails much more than just simple data posted in the forums, as there are many things to look at.

Besides the fact that your server is using all its RAM & states only about 200MB free

table_cache=80000 <-- Thats way to high
max_connections=5000 <-- Way to high
open_files_limit=5400 <-- Doesnt matter unless the OS allows that many
thread_cache_size=4 <-- Wrong section & set wrong

Edit to add:
If your host is setting your server details, i would fire them on the spot for poor workmanship.......

--------------- Added [DATE]1381349605[/DATE] at [TIME]1381349605[/TIME] ---------------

Looks like you down now for server load.

Run this cmd in ssh when this happens & post the results:
netstat -plan|grep :80|awk {'print $5'}|cut -d: -f 1|sort|uniq -c|sort -nk 1

[abdobasha2004]

For all thanks for your help
It was discovered that the problem was a ddos attack + flooding
We used cloudflare business and problem solved

Thanks God