Web site is going down at 300 visits - i have strong server.

Hello,


I use dedicated server from web tropia:




Whit centos 6 and centos web panel.

I have only one web site on this server.
When I reach 300 visitors in sametime at my website, i have big big load on my server, and website is going down.

Before this server, i was use godaddy webhosting whitout any problems.


But, when my website is down, cwp administration is still working fine.
Here are images from pacage that i use and from user account:

Account: http://prntscr.com/cqygem
Pacage: http://prntscr.com/cqygs5

What can make big load, and why website is going down ?
Can anyone help me please ?

As web server i use apache only.

Thank You



edit load;;;;;;;;

http://prntscr.com/cqyhrp

Hi,
its very difficult to figure out whats wrong with just looking at the printcreen. I hope you have root access to the server. If so, please provide the output of those files:
/usr/local/apache/logs/
/var/log/suphp.log
/var/log/mysqld.log
also provide us the time when you had most load on the server so we can investigate.
On a side note, you can install htop so you can better view what process is using most of your resources:
as root type: yum install htop  and when its finished you can type htop in the terminal to see how much memory and cpu you have at the moment, also you can see the top processes and how much memory/cpu are those using on the system. When you are done with viewing just press q and you will be dumped on the command line again.

Pozdrav
Bojan

TOP: http://prntscr.com/cqyzy5
ATOP: http://prntscr.com/cqz06u
HTOP: http://prntscr.com/cqz0gl


I will update this post whit logs, just to come home

Now i was install memcached.

How i can check is it working ?
Thanks

Now site if working much better,
Here is what i do:

- Add website to cloudflare
- Install fastcgi
- Install memcached
- Install wordpress plugin "wp super cache"

But still when i have about 300 visitors, cpu usage going to 90%

Also on screanschot i see that mysql use a lot of CPU.

I see that a lot of members here have own hosting company's .

Can you please help me, how i can optimaze database (i use matiadb), or what i can install because it use a lot lot of cpu.
Also, is there anything else what i can use to make better performace at my server (for now this cache tehnology's are helping) but it must be something more.

I know that one guy, have same site as this, and he host it on VPS whit 3 gb of ram and 2 cpu core.

Thank you alll

Sorry for my bad english.

Memcashe can help you but try to optimize it for dynamic content.
Also can you give us the output of the file my.cnf
in terminal type : cat /etc/my.cnf
As default you will have something like this :

Code: [Select]

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
My recommendation is that you install mysqltuner :
yum install mysqltuner
and when you run it (just type mysqltuner in terminal) you will get something like this:

Code: [Select]

>>  MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2K (Tables: 19)
[--] Data in InnoDB tables: 224K (Tables: 14)
[!!] Total fragmented tables: 14

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15d 17h 44m 2s (153K q [0.113 qps], 66K conn, TX: 9M, RX: 8M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 449.2M (23% of installed RAM)
[OK] Slow queries: 0% (0/153K)
[OK] Highest usage of available connections: 5% (8/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/122.0K
[!!] Key buffer hit rate: 75.0% (20 cached / 5 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 120 sorts)
[!!] Temporary tables created on disk: 36% (1K on disk / 3K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 11% (64 open / 552 opened)
[OK] Open file limit used: 5% (55/1K)
[OK] Table locks acquired immediately: 100% (14K immediate / 14K locks)
[OK] InnoDB data size / buffer pool: 224.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    >>  MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2K (Tables: 19)
[--] Data in InnoDB tables: 224K (Tables: 14)
[!!] Total fragmented tables: 14

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15d 17h 44m 2s (153K q [0.113 qps], 66K conn, TX: 9M, RX: 8M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 449.2M (23% of installed RAM)
[OK] Slow queries: 0% (0/153K)
[OK] Highest usage of available connections: 5% (8/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/122.0K
[!!] Key buffer hit rate: 75.0% (20 cached / 5 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 120 sorts)
[!!] Temporary tables created on disk: 36% (1K on disk / 3K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 11% (64 open / 552 opened)
[OK] Open file limit used: 5% (55/1K)
[OK] Table locks acquired immediately: 100% (14K immediate / 14K locks)
[OK] InnoDB data size / buffer pool: 224.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64) (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)As you can see its recommendation is to change query_cacshe_size :
so we edit /etc/my.cnf and add  query_cache_size like this under mysqld section:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
query_cache_size = 128M

restart mysqld with command: service mysqld restart

Hi,

Here is output:

Code: [Select]

[root@ve280 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d



Here is mysql tuner output:

Code: [Select]

[root@ve280 ~]# mysqltuner

 >>  MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[!!] Your MySQL version 10.1.18-MariaDB is EOL software!  Upgrade soon!
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 341M (Tables: 72)
[--] Data in CSV tables: 0B (Tables: 2)
[--] Data in InnoDB tables: 3M (Tables: 18)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 4

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
Use of uninitialized value in addition (+) at /usr/bin/mysqltuner line 547 (#1)
    (W uninitialized) An undefined value was used as if it were already
    defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
    To suppress this warning assign a defined value to your variables.

    To help you figure out what was undefined, perl will try to tell you the
    name of the variable (if any) that was undefined. In some cases it cannot
    do this, so it also tells you what operation you used the undefined value
    in.  Note, however, that perl optimizes your program and the operation
    displayed in the warning may not necessarily appear literally in your
    program.  For example, "that $foo" is usually optimized into "that "
    . $foo, and the warning will refer to the concatenation (.) operator,
    even though there is no . in your program.

Use of uninitialized value $myvar{"have_innodb"} in string eq at
        /usr/bin/mysqltuner line 665 (#1)

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 21h 22m 34s (132M q [810.836 qps], 987K conn, TX: 1065B, RX: 18B)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 297.0M global + 545.0K per thread (151 max threads)
[OK] Maximum possible memory usage: 377.4M (1% of installed RAM)
[OK] Slow queries: 0% (0/132M)
[!!] Highest connection usage: 100%  (152/151)
[OK] Key buffer size / total MyISAM indexes: 128.0M/72.3M
[OK] Key buffer hit rate: 100.0% (46B cached / 50K reads)
[OK] Sorts requiring temporary tables: 0% (41 temp sorts / 48M sorts)
[!!] Joins performed without indexes: 1341897
[!!] Temporary tables created on disk: 34% (8M on disk / 23M total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 22% (940/4K)
[OK] Table locks acquired immediately: 99% (150M immediate / 150M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce or eliminate persistent connections to reduce connection usage
    Upgrade MySQL to version 4+ to utilize query caching
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)



As you can see its recommendation is to change query_cacshe_size :
so we edit /etc/my.cnf and add  query_cache_size like this under mysqld section:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
query_cache_size = 128M

My cnf file is empty, i put code above,
I can just paste this code that you give to me ?

Hvala mnogo Bojane, tesko bi ovo sve sam nasao, jos jednom hvala.
Mozda je neka greska u my.cnf fajlu, posto je prazan ?
LP

E nema na čemu. Sad će da izvalimo o čemu se radi:
Vidiš tu liniju koja kaže
!includedir /etc/my.cnf.d
pošto koristiš MariaDB a ne MySQL verovatno su mu tamo glavni konfiguracioni fajlovi. Aj polgedaj šta tamo stoji u tom direktorijumu my.cnf.d verovatno ti i tamo stoji jedan my.cnf gde bi bilo pametnije da ubacimo ove preporuke za max_connections itd.
Takođe, veoma često se dešava da recimo neki plugin za CMS zabada sql batu

Imam folder: /etc/my.cnf.d


I tu imam ove fajlove:
enable_encryption.preset
mysql-clients.cnf
server.cnf
tokudb.cnf



Jedino u server.cnf imam ova podesavanja:

Code: [Select]

These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.1 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.1]


Sada trenutno imam 450 posjeta na sajtu, i velik load, sta mislis da bi sem baze moglo jos da je lose optimizovano pa dovodi do loada ?


Hvala ti puno

Ok,
koliko vidim tvoj sql server koristi server.cnf kao glavni konfiguracioni fajl.
tako ćemo da stavimo u sekciju [mysqld] ono što je preporučeno od mysqltunera:
max_connections = 250
tmp_table_size = 64M
max_heap_table_size = 64M
key_buffer = 256M

naravno posle ovoga restartuj sql server pa javi kako je prošlo i output od mysqltunera.
čisto da vidimo kako će da se ponaša
inače max_connections je , kao što i sama opcija kaže, maksimalni broj konekcija na bazu u istom trenutku.

P.S kad imaš veliki broj konekcija u principu sve treba da se izmeni po malo. I apache, i php i mysql . Šta tačno treba da se izmeni/optimizuje zavisi od toga koji su zahtevi servera. Ponekad žrtvuješ brzinu za heavy load na primer .

Hvala

Evo ga output:

Code: [Select]


[root@ve280 ~]# cat /etc/my.cnf.d/server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.1 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.1]


max_connections = 250
tmp_table_size = 64M
max_heap_table_size = 64M
key_buffer = 256M


E sada malo me brine ovo max_connections=250.
Ponekad znam imati i po 1500 istovremenih posjeta na portalu, mada obicno se krece izmedju 250-450.
Da nece ovo praviti problem, mozda bih trebao staviti malo vise od 250, sta mislis ?

Lijep Pozdrav

Slobodno stavi 250 pa ćemo da vidimo kako će da se ponaša server.

Vazi, hvala !!

Sta mislis bili se moglo jos st uraditi da se sacuva cpu, optimizacija kes memorije je dosta pomogla, installiranje memcached i dodavanje sajta na cloudflare.


Medjutim wpp super cache plugin mi je zeznuo sajt totallno, okusavam da ocistim sav taj njegov kes ali tesko ide, nece da izlaze novi postovi na pocetnoj strani


uuglavnom, sve u svemu hvala brate puno. Naravno javim kako se ponasa server.

za CPU bi mogao bi recimo da proveriš da slučajno nemaš neke query koji se sporo izvršava. To može da napravi problem.
http://dba.stackexchange.com/questions/98732/how-to-enable-queries-logs-in-mariadb

#Slow Query Log
log-slow-queries
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mysql_slow.log
long_query_time         = 2
log-queries-not-using-indexes

First, try using Apache + nginx proxy.. then use php7 instead php5.x.. it could saves you tons of resources.

NB: keep using english.