New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
MySQL/mariadb overloading server
I have openVZ VPS with 1gb ram, 1gb swap and 3 cpu.
It is running ubuntu with Nginx, PHP, MySQL (that I replaced with mariadb).
This is TOP:
3934 www 20 0 30908 10m 4152 S 2 1.0 0:27.26 /usr/local/php/bin/php-cgi --fpm --fpm-config /usr/local/php/etc/php-fpm.conf
3946 www 20 0 30664 9m 4176 S 1 1.0 0:25.86 /usr/local/php/bin/php-cgi --fpm --fpm-config /usr/local/php/etc/php-fpm.conf
3948 www 20 0 30856 10m 4228 S 1 1.0 0:23.16 /usr/local/php/bin/php-cgi --fpm --fpm-config /usr/local/php/etc/php-fpm.conf
3947 www 20 0 28548 7064 3392 S 0 0.7 1:01.48 /usr/local/php/bin/php-cgi --fpm --fpm-config /usr/local/php/etc/php-fpm.conf
1 root 20 0 3360 1744 1276 S 0 0.2 0:00.84 init
2 root 20 0 0 0 0 S 0 0.0 0:00.00 [kthreadd/7615]
3 root 20 0 0 0 0 S 0 0.0 0:00.00 [khelper/7615]
95 root 20 0 2788 700 532 S 0 0.1 0:00.00 upstart-udev-bridge --daemon
114 root 20 0 2796 1016 736 S 0 0.1 0:00.06 /sbin/udevd --daemon
143 root 20 0 2792 652 364 S 0 0.1 0:00.01 /sbin/udevd --daemon
146 root 20 0 2792 652 364 S 0 0.1 0:00.00 /sbin/udevd --daemon
185 root 20 0 2800 516 372 S 0 0.0 0:00.00 upstart-socket-bridge --daemon
229 root 20 0 9144 6456 1968 S 0 0.6 0:02.22 /usr/sbin/munin-node
308 root 20 0 6636 2328 1896 S 0 0.2 0:00.11 /usr/sbin/sshd -D
533 root 20 0 2516 988 832 S 0 0.1 0:00.00 /usr/sbin/xinetd -dontfork -pidfile /var/run/xinetd.pid -stayalive -inetd_compat -inetd_i
546 root 20 0 2572 920 732 S 0 0.1 0:00.16 cron
607 syslog 20 0 2356 724 584 S 0 0.1 0:00.13 /sbin/syslogd -u syslog
649 root 20 0 3412 1572 1260 S 0 0.1 0:00.28 /bin/bash /usr/bin/mysqld_safe
1581 mysql 20 0 994m 234m 8720 S 0 22.9 56:13.67 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plug
1582 root 20 0 2104 572 500 S 0 0.1 0:00.28 logger -t mysqld -p daemon.error
1793 root 20 0 12944 892 376 S 0 0.1 0:00.00 /usr/sbin/saslauthd -a pam -c -m /var/run/saslauthd -n 2
1795 root 20 0 12944 572 56 S 0 0.1 0:00.00 /usr/sbin/saslauthd -a pam -c -m /var/run/saslauthd -n 2
1799 root 20 0 13728 7364 1324 S 0 0.7 0:09.54 /usr/bin/python /usr/bin/supervisord
1860 root 20 0 15120 1864 580 S 0 0.2 0:01.42 sendmail: MTA: accepting connections
2392 root 20 0 9596 3068 2444 S 0 0.3 0:00.21 sshd: root@pts/0
2408 root 20 0 3440 1788 1432 S 0 0.2 0:00.00 -bash
3170 root 20 0 2672 1180 964 T 0 0.1 0:00.30 top
3926 root 20 0 5772 776 280 S 0 0.1 0:00.00 nginx: master process /usr/local/nginx/sbin/nginx -c /usr/local/nginx/conf/nginx.conf
3927 www 20 0 16232 11m 868 S 0 1.1 0:01.65 nginx: worker process
3932 root 20 0 28356 4904 1520 S 0 0.5 0:15.85 /usr/local/php/bin/php-cgi --fpm --fpm-config /usr/
It is working fine when I reboot server, but after 30min I can't login to phpmyadmin for example. It is just loading and never load.
Here is my.cnf:
# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 20M
max_allowed_packet = 1M
table_open_cache = 64
max_connections = 30
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 1M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
open_files_limit=10192
query_cache_size=150M
join_buffer_size=128K
thread_cache_size=14
table_cache=64
tmp_table_size=16M
max_heap_table_size=16M
innodb_buffer_pool_size=0
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
# binary logging format - mixed recommended
binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/var
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/var
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
nginx.conf:
user www www;
worker_processes 1;
error_log /home/wwwlogs/nginx_error.log crit;
pid /usr/local/nginx/logs/nginx.pid;
#Specifies the value for maximum file descriptors that can be opened by this process.
worker_rlimit_nofile 51200;
events
{
use epoll;
worker_connections 51200;
}
http
{
include mime.types;
default_type application/octet-stream;
server_names_hash_bucket_size 128;
client_header_buffer_size 32k;
large_client_header_buffers 4 32k;
client_max_body_size 50m;
sendfile on;
tcp_nopush on;
keepalive_timeout 600;
tcp_nodelay on;
fastcgi_connect_timeout 9000;
fastcgi_send_timeout 9000;
fastcgi_read_timeout 9000;
fastcgi_buffer_size 64k;
fastcgi_buffers 4 64k;
fastcgi_busy_buffers_size 128k;
fastcgi_temp_file_write_size 256k;
gzip on;
gzip_min_length 1k;
gzip_buffers 4 16k;
gzip_http_version 1.0;
gzip_comp_level 2;
gzip_types text/plain application/x-javascript text/css application/xml;
gzip_vary on;
#limit_zone crawler $binary_remote_addr 10m;
#log format
log_format access '$remote_addr - $remote_user [$time_local] "$request" '
'$status $body_bytes_sent "$http_referer" '
'"$http_user_agent" $http_x_forwarded_for';
server
{
listen 80;
server_name my-server.com;
index index.html index.htm index.php;
root /home/wwwroot;
location ~ .*\.(php|php5)?$
{
try_files $uri =404;
fastcgi_pass unix:/tmp/php-cgi.sock;
fastcgi_index index.php;
include fcgi.conf;
fastcgi_connect_timeout 9000;
fastcgi_send_timeout 9000;
fastcgi_read_timeout 9000;
fastcgi_buffer_size 64k;
fastcgi_buffers 4 64k;
fastcgi_busy_buffers_size 128k;
fastcgi_temp_file_write_size 256k;
}
location /status {
stub_status on;
access_log off;
}
location ~ .*\.(gif|jpg|jpeg|png|bmp|swf)$
{
expires 30d;
}
location ~ .*\.(js|css)?$
{
expires 12h;
}
location /munin/ {
alias /var/cache/munin/www/;
index index.html index.htm index.php;
}
access_log /home/wwwlogs/access.log access;
}
include vhost/*.conf;
}
mysqltuner shows this:
------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[!!] Your MySQL version 10.0.8-MariaDB-1~precise-log is EOL software! Upgrade soon!
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 44M (Tables: 27)
[--] Data in InnoDB tables: 48K (Tables: 3)
[--] Data in CSV tables: 0B (Tables: 2)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 1
Use of uninitialized value in addition (+) at /usr/bin/mysqltuner line 515, <>
line 2 (#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 633, <> line 2 (#1)
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 1m 21s (10K q [1.402 qps], 738 conn, TX: 1M, RX: 491K)
[--] Reads / Writes: 8% / 92%
[--] Total buffers: 496.0M global + 416.0K per thread (100 max threads)
[OK] Maximum possible memory usage: 536.6M (52% of installed RAM)
[OK] Slow queries: 1% (178/10K)
[OK] Highest usage of available connections: 7% (7/100)
[OK] Key buffer size / total MyISAM indexes: 128.0M/11.3M
[!!] Key buffer hit rate: 79.5% (71K cached / 14K reads)
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 337 sorts)
[!!] Temporary tables created on disk: 39% (496 on disk / 1K total)
[OK] Thread cache hit rate: 99% (7 created / 738 connections)
[OK] Table cache hit rate: 72% (89 open / 122 opened)
[OK] Open file limit used: 0% (69/10K)
[!!] Table locks acquired immediately: 88%
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Upgrade MySQL to version 4+ to utilize query caching
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
tmp_table_size (> 32M)
max_heap_table_size (> 32M)
Does anybody have idea what is wrong in server configuration.
I'm running few very simple scripts on it that I usually run on shared managed hosting without any issue.
Comments
Check your system log, is MySQL getting killed by OOM killer?
I will agree with Adduc, but when you have mess like yours, just make backup and reinstall mysql server.
Good luck!
If you don't mind switching to CentOS, you could setup your VPS with http://centminmod.com/
What actual queries are running when it gets loaded heavy and do your access logs imply any odd activity? Reinstall doesn't fix the problem.
Centos won't fix the issue, more likely your server is getting searched by bing bot and it is causing load issues, watch your server logs via tail -f and see what it is happening.
You stated that you've replaced mysql with mariaDB, do you still have this issue with the default mysql configuration?
is there a reason you switched to mariaDB?
Note: I do not know much about databases but this might be the issue.
What version of MariaDB? You know that InnoDB/XtraDB is the default engine, right? Which isn't configured at all in your config.
Also this: myisam_sort_buffer_size = 8M should be more like 1M if you're short on RAM.
Maybe I'm misreading something here but it looks like 4% of your CPU is going to php-fpm and that's pretty much all your usage. What is overloading?
Your'e doing 1.4 qps over 2 hours -- that's nothing.
There's something buggered with your installation IMO.
Once you're back up and running you can then look into doing my.cnf optimizations.
@sleddog: is there anything wrong with mariadb? I think that Oracle is letting mysql die a slow death.
Besides the point... just digressing here.
As for the OP, you seem to be using configuration for rather large server. I see you've got 3 InnoDB tables there. Is there a reason for that? Could they be converted to MyISAM? If yes, you could disabled InnoDB for now.
You may also use try with my-small.configuration file as your starting point. I'm running a Wordpress site on 512 MB VPS with lighttpd/php/mysql and it's humming along nicely with a bunch of concurrent users accessing the site.
Here's what I got in my.cnf file:
Well that's interesting. MySQLTuner says 100 max connections; your cnf says 30. ...
Not as far as I know. What I was suggesting is that it's generally safer to stick with your distro's packages. They've been extensively tested for compatibility and stability. The only reason (that I'm aware) for replacing the stock mysql with mariadb is (possible) performance improvements. But when you're doing only 1 qps, that's irrelevant. A few tweaks to the stock mysql and it'll be perfectly fine.
Edited the .cnf but hasn't restarted mysql maybe...