Would these errors cause server to crash?

[DaveL]

This is my.cnf - Is there any optimisation that could be done here to help?

[mysqld]
local-infile=0
key_buffer = 124M
max_allowed_packet = 64M
max_connections = 500
max_user_connections = 50
wait_timeout=40
connect_timeout=10
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_limit = 10M
query_cache_size = 32M
join_buffer_size = 128M
tmp_table_size = 256M
max_heap_table_size = 256M
thread_concurrency = 4
server-id = 1

open_files_limit=9838
[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

 

[DaveL]

And these are only 17 people browsing your site?

30 at the moment. 19 guests and 11 members.

There seems to be no rhyme or reason. At the moment the loads are 0.52 , 2.87, 5.33

But all of a sudden things and can beserk.

 

[MattW]

This is my.cnf - Is there any optimisation that could be done here to help?

There is quite a bit that can be done with that. Are your database tables InnoDB?

 

[DaveL]

There is quite a bit that can be done with that. Are your database tables InnoDB?

ive just logged into phpmyadmin to check and its showing "Type - InnoDB", however scrolling down, some appear to be MyISAM and MEMORY, however the majority appear to be InnoDB

 

[MattW]

That's good then. How much RAM do you have (512mb?)

Edit: just seen you have 1gb.

Even so, the standard my.cnf should be ok with the number of online guests and visitors you have.

 

[DaveL]

That's good then. How much RAM do you have (512mb?)

I have 1024mb

 

[MattW]

I have 1024mb

You might want to check that with your host, as the output from top is only showing 512

 

[DaveL]

Is that at the top of the screenshot I posted yesterday? If so i mentioned this and its since been upgraded.

 

[MattW]

Ah, my apologies, I was looking at the screen shot from yesterday.

Let me boot up my laptop, and I'll post some InnoDB settings you can try

 

[DaveL]

Ah, my apologies, I was looking at the screen shot from yesterday.

Let me boot up my laptop, and I'll post some InnoDB settings you can try

Thanks Matt. Currently running at 13.24 at the moment! Took another screenshot whilst it was in it way up.

 

[MattW]

If you wouldn't mind, could you PM me access and I can take a look for you to try and see what is happening?

 

[MattW]

Well you can try adding these into my.cnf (make a backup of it first)

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size          = 64M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 640M

You will then need to restart MySQL to make them take effect.

 

[WSWD]

Please post a screenshot of iostat and iotop as well. There is nothing in your top screenshot that is terribly out of the ordinary or that should be causing these loads.

 

[Luke F]

Might be worth installing/watching nmon, it's handy for this kind of thing

 

[Floren]

Please stop posting screenshots, paste the real text from terminal.
What is the disk usage? The idle jumps from 89% to 28%, that is not normal.
Example of stats for a site with 800 users online:

# uptime
00:16:15 up 43 days, 13:18,  1 user,  load average: 0.27, 0.21, 0.18

DISKS
# iostat -dkx
Linux 2.6.18-348.4.1.el5 (haze.xxxxxxxx.com)  06/09/2013
Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
sda               9.97     7.10  9.40  3.70   897.53    85.91   150.19     0.13    9.60   3.71   4.86
sda1              0.00     0.00  0.00  0.00     0.32     0.00   280.63     0.00    2.43   1.08   0.00
sda2              0.12     0.02  0.11  0.01    13.43     0.13   232.59     0.00    3.86   2.96   0.03
sda3              9.85     7.07  9.29  3.69   883.78    85.78   149.43     0.13    9.65   3.73   4.84
sdb              13.70     7.10  5.36  3.70   895.74    85.91   216.66     0.09   10.22   4.41   4.00
sdb1              0.00     0.00  0.00  0.00     0.32     0.00   785.76     0.00   15.09   4.21   0.00
sdb2              0.19     0.02  0.04  0.01    13.44     0.13   607.38     0.00   15.72   7.46   0.03
sdb3             13.51     7.07  5.33  3.69   881.98    85.78   214.67     0.09   10.20   4.41   3.97
md2               0.00     0.00 14.06 10.02   235.62    82.79    26.44     0.00    0.00   0.00   0.00
md1               0.00     0.00  0.03  0.03     0.13     0.11     8.00     0.00    0.00   0.00   0.00
md0               0.00     0.00  0.00  0.00     0.00     0.00     3.90     0.00    0.00   0.00   0.00

TOP10 RESOURCES
# ps axo stime,pid,ppid,user,pcpu,pmem,command | sort -rk 5 | head -n 11
STIME  PID  PPID USER    %CPU %MEM COMMAND
May03  1792  1537 mysql    1.5 56.0 /usr/libexec/mysqld --defaults-file=/etc/mysql/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --extra-port=3307 --log-error=/var/log/mysql/mysqld.log --open-files-limit=16384 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock
00:12 30027  3292 php-fpm  0.5  0.2 php-fpm: pool www
00:11 30021  3292 php-fpm  0.5  0.2 php-fpm: pool www
00:13 30048  3292 php-fpm  0.4  0.3 php-fpm: pool www
00:11 30020  3292 php-fpm  0.4  0.3 php-fpm: pool www
00:11 30019  3292 php-fpm  0.4  0.3 php-fpm: pool www
00:10 30013  3292 php-fpm  0.4  0.3 php-fpm: pool www
00:04 29918  3292 php-fpm  0.4  0.3 php-fpm: pool www
00:14 30050  3292 php-fpm  0.4  0.2 php-fpm: pool www
00:13 30046  3292 php-fpm  0.4  0.2 php-fpm: pool www

Look for %util, those numbers should always be small. Anything above 15% will generate load, which I'm pretty sure is your case. Solution? Get a dedicated server, VPS is not the way to go if you want a responsive site.

 

[WSWD]

VPS is not the way to go if you want a responsive site.

Huh??? Where in the world do you come up with that?

 

[TPerry]

Huh??? Where in the world do you come up with that?

Really. If your site is not that large, there is no NEED for a dedicated server. I have mine running on one just because I want it - not because I need it. There is no noticeable difference in speed between when I had it on the VPS and now (the VPS was a CKVM-E3 with 2GB memory and 4 CPU core @ 3.3Ghz, 75GB SSD RAID 10 drives).

 

[Floren]

Huh??? Where in the world do you come up with that?

What are you talking about? Indeed, is your choice to run a VPS, if you cannot "afford" an extra $20/month to have a decent system. For people who say their site does not justifies the cost: Please, stop going to McDonalds every day and quit smoking, then you will be able to get 2 dedicated servers.

 

[DaveL]

What are you talking about? Indeed, is your choice to run a VPS, if you cannot "afford" an extra $20/month to have a decent system. For people who say their site does not justifies the cost: Please, stop going to McDonalds every day and quit smoking, then you will be able to get 2 dedicated servers.

Little bit harsh in my opinion. In the UK your looking at roughly an additional £60+ per month for a dedicated server.

I also dont visit McDonalds, KFC, Burger King or any other fast rood restaurant and I dont smoke.

 

[WSWD]

What are you talking about? Indeed, is your choice to run a VPS, if you cannot "afford" an extra $20/month to have a decent system. For people who say their site does not justifies the cost: Please, stop going to McDonalds every day and quit smoking, then you will be able to get 2 dedicated servers.

If the dedicated server you're putting together is only $20/mo. more than a VPS, I can guarantee you the specs are nowhere close to being the same. You'll probably have a single hard drive or two instead of RAID10, a crappy processor, etc.

A lot of people don't understand that there is often a performance DECREASE when going from a VPS to a dedicated. You aren't going to get dual processor machines with 8+ drive RAID configurations for $20/mo. more than a VPS. If you can, I'd love to know where you're shopping, because I'll buy 100 of those servers today.