mysql spikes and server crashes

[modom]

Hi,

I am having a terrible time with one of my ecommerce stores with over 10,000 customers and has downloadable products.

One of my ecommerce (prestashop) stores has a sale and the server is hit all at once it seems and mysql spikes and server crashes.
I have checked the logs and ran mysqltuner.
When I run mysqltuner it wants me to keep on increasing all the values it recommends in /etc/my.cnf and the problem persists.

This is my.cnf file now after removing the other listings:
[mysqld]
# add skip-innodb to disable InnoDB
skip-innodb
#open_files_limit=26520
local-infile=0
default-storage-engine=MyISAM

After the crash I get several of these emails:
/etc/cron.hourly/modsecparse.pl:

DBI connect('modsec:localhost','modsec',...) failed: Too many connections at /etc/cron.hourly/modsecparse.pl line 19
Unable to connect to mysql database at /etc/cron.hourly/modsecparse.pl line 19.

Server specs are:

Base System - Intel Dual Clovertown (5310) System 1.60GHz
Two Intel Xeon 5310 "Clovertown" Quad Core processors (8-core)
/dev/sda - 250GB HDD - 64-bit
CentOS release 6.4 (Final)

Server information: Linux #1 SMP Tue Apr 23 18:13:20 UTC 2013 i686 64-bit
Server software version: Apache 2.2.24
PHP version: 5.5
MySQL 5.5
Memory limit: 4GB
Max execution time: 120

I have 20 ecommerce stores all using prestashop for the store.

Please help ...

 

[thinkbot]

Re: my.cnf Optimisation - 24GB RAM

Please post result of mysqltuner

 

[modom]

Re: my.cnf Optimisation - 24GB RAM

About an hour ago I changed the my.cnf file to:

[mysqld]
# add skip-innodb to disable InnoDB
skip-innodb

wait_timeout = 30
connect_timeout = 1
local-infile=0

open_files_limit=26520
default-storage-engine=MyISAM

max_connections=500

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes


mysqltuner.pl says:

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 858M (Tables: 4325)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 21

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 2m 47s (363K q [96.481 qps], 1K conn, TX: 458M, RX: 170M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 24.0M global + 2.8M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.4G (74% of installed RAM)
[OK] Slow queries: 3% (11K/363K)
[OK] Highest usage of available connections: 2% (12/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/442.8M
[OK] Key buffer hit rate: 99.5% (23M cached / 126K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 80K sorts)
[!!] Joins performed without indexes: 276
[OK] Temporary tables created on disk: 24% (23K on disk / 95K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 4% (400 open / 8K opened)
[OK] Open file limit used: 2% (792/26K)
[OK] Table locks acquired immediately: 99% (835K immediate / 835K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
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)
join_buffer_size (> 128.0K, or always use indexes with joins)
thread_cache_size (start at 4)
table_cache (> 400)

Also, while running top -c I see this for the CPU and then it goes down:
20285 mysql 20 0 1532m 207m 4532 S 781.3 11.1 60:58.46 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib6
781.3 for %CPU?

 

[thinkbot]

Re: my.cnf Optimisation - 24GB RAM

set it like this

[mysqld]
# add skip-innodb to disable InnoDB
skip-innodb

wait_timeout = 30
connect_timeout = 1
local-infile=0

open_files_limit=26520
default-storage-engine=MyISAM

max_connections=250

key_buffer_size = 1G
max_allowed_packet=20M

query_cache_size=30M
query_cache_limit=1M
tmp_table_size=50M
max_heap_table_size=50M
thread_cache_size=50

table_open_cache = 3000
table_definition_cache = 1000

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes

sort_buffer_size=256K
join_buffer_size=2M
read_rnd_buffer_size=4M

low_priority_updates=1
concurrent_insert=2

 

[modom]

Re: my.cnf Optimisation - 24GB RAM

Thank you so much! I will monitor it today and check during the client's sale.

 

[modom]

Re: my.cnf Optimisation - 24GB RAM

Hi,

My client posted her sale on facebook and the server was running ok until it got several hits over and over again and the /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib6
781.3 for %CPU went up again and the server crashed.

I restarted the server and her store is active again with the load low but don't expect this to last long if it repeats the load increase again.

After it started up I ran mysqltuner again and got this:

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 863M (Tables: 4325)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 22

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 13m 18s (109K q [136.967 qps], 625 conn, TX: 145M, RX: 58M)
[--] Reads / Writes: 96% / 4%
[--] Total buffers: 1.1G global + 6.6M per thread (250 max threads)
[!!] Maximum possible memory usage: 2.7G (147% of installed RAM)
[OK] Slow queries: 1% (1K/109K)
[OK] Highest usage of available connections: 5% (14/250)
[OK] Key buffer size / total MyISAM indexes: 1.0G/444.7M
[OK] Key buffer hit rate: 99.8% (7M cached / 15K reads)
[OK] Query cache efficiency: 72.8% (77K cached / 106K selects)
[!!] Query cache prunes per day: 1299031
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 5K sorts)
[!!] Joins performed without indexes: 25
[OK] Temporary tables created on disk: 25% (810 on disk / 3K total)
[OK] Thread cache hit rate: 97% (14 created / 625 connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 9% (2K/26K)
[OK] Table locks acquired immediately: 99% (64K immediate / 64K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 30M)
join_buffer_size (> 2.0M, or always use indexes with joins)

 

[thinkbot]

Re: my.cnf Optimisation - 24GB RAM

mysqltuner says that you have 2GB RAM, not 4GB as you wrote

here is updated my.cnf, better for 2GB

[mysqld]
# add skip-innodb to disable InnoDB
skip-innodb

wait_timeout = 30
connect_timeout = 1
local-infile=0

open_files_limit=26520
default-storage-engine=MyISAM

max_connections=200

key_buffer_size = 600M
max_allowed_packet=20M

query_cache_size=30M
query_cache_limit=1M
tmp_table_size=50M
max_heap_table_size=50M
thread_cache_size=50

table_open_cache = 3000
table_definition_cache = 1000

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes

sort_buffer_size=256K
join_buffer_size=2M
read_rnd_buffer_size=2M

max_write_lock_count = 10
concurrent_insert=2

remotely we can't check much what happens there,
if mysql went 781.3 for %CPU so you probably had some queries stuck,
they might be too slow, or you might have some cache miss storm (cache refresh of slow query when many people quering at once)

I've added "max_write_lock_count = 10" to my.cnf to prevent too long insert/update lock on MyISAM
you can also convert the tables to InnoDB, but thats all in mysql

remember that you need to have also script optimized (caching etc)

to get info on slow queries you can:
wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest

./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
and in slow.txt you can info on slow queries


Regards

 

[modom]

Re: my.cnf Optimisation - 24GB RAM

Wow I didn't even see what the ram said. I'm suppose to have 4 so will contact the server company.

 

[BillyS]

Re: my.cnf Optimisation - 24GB RAM

key_buffer_size = 600M

[OK] Key buffer size / total MyISAM indexes: 1.0G/444.7M

I'd suggest you save 100M and go with:

key_buffer_size = 500M

[OK] Highest usage of available connections: 5% (14/250)

BTW - If you use the recommendation provided by thinkbot, it'll consume around 1,600 MB of RAM. I'd suggest:


max_connections=100

Unless the 14 is an unusually low number...

 

[modom]

Re: my.cnf Optimisation - 24GB RAM

Thanks so much! This post has been so helpful and saved me from losing a client.

I found out that I do have 4GB ram but the slot alignment was not followed so it was only registering 2GB instead. Even though I have 4GB I still set the settings for 2GB and it looks good.

I figured out the other listings but what exactly does this mean:
Highest usage of available connections: 5% (14/250)
Is that the highest usage is 14 but it was using 250?

If my client's store stays up tonight I'll be happy. It looks very good right now.

 

[thinkbot]

"I'd suggest you save 100M and go with:"

there is nothing to save, couse this mean MAX value that MySQL can use, if it doesn't need it, it won't use it

"[OK] Highest usage of available connections: 5% (14/250)
BTW - If you use the recommendation provided by thinkbot, it'll consume around 1,600 MB of RAM. I'd suggest:"

6,6 * 250 = 1650, of course this is assuming all queries will be running at the same time with each buffer used max per connection, which is impossible

In the next my.cnf, max was lowered to 200, and per connection buffers also smaller, so it will use it worse case scenario (like the one above which is almost impossible) 920 MB


So you can leave those values as I wrote,
BillyS suggestions are purely theoretical calculations, in practise it works a bit different, and depends on load, queries and more

You can post slow.txt and new mysqltuner

 

[cPanelMichael]

Hello

Remember to try to let MySQL run at least 24 hours before each use of the MySQL tuner script to allow for the most accurate results.

Thank you.

 

[modom]

Yes I will post tomorrow.

I am trying to figure out which my.cnf settings (2GB or 4GB) to use though. It seems the load was lower on the 2GB one.

My server only has a total of 4GB RAM. The swap is being used now and is very low.
3921500k is memory and about 3515656k pretty consistently used.

Would the 2GB settings be better? Or should I consider getting more ram?

 

[thinkbot]

Use last one I presented, and post mysqltuner later and/or slow log

 

[BillyS]

BillyS suggestions are purely theoretical calculations, in practise it works a bit different, and depends on load, queries and more.

You're correct, I did take the time to actually calculate the values. You're also correct, I did look at load and ask if the 14 was normal.

Is that the highest usage is 14 but it was using 250?

From the last restart time, you had a maximum of 14 concurrent connections being served. Based on his practical experience, thinkbot thinks the number should be closer to 200, not the 100 I suggested.

 

[modom]

I have used the 2GB settings for my.cnf.

As of this morning the mysqltuner says:

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 892M (Tables: 4325)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 24

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15h 25m 6s (7M q [129.868 qps], 38K conn, TX: 8B, RX: 2B)
[--] Reads / Writes: 96% / 4%
[--] Total buffers: 680.0M global + 4.6M per thread (200 max threads)
[OK] Maximum possible memory usage: 1.6G (41% of installed RAM)
[OK] Slow queries: 0% (35K/7M)
[OK] Highest usage of available connections: 11% (23/200)
[OK] Key buffer size / total MyISAM indexes: 600.0M/454.4M
[OK] Key buffer hit rate: 99.8% (112M cached / 184K reads)
[OK] Query cache efficiency: 78.7% (5M cached / 7M selects)
[!!] Query cache prunes per day: 2144473
[OK] Sorts requiring temporary tables: 0% (330 temp sorts / 250K sorts)
[!!] Joins performed without indexes: 2614
[!!] Temporary tables created on disk: 30% (51K on disk / 170K total)
[OK] Thread cache hit rate: 99% (23 created / 38K connections)
[!!] Table cache hit rate: 15% (3K open / 19K opened)
[OK] Open file limit used: 22% (5K/26K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
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
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 30M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 50M)
max_heap_table_size (> 50M)
table_cache (> 3000)

I have the slow.txt file but it shows all the databases of all my clients.
When you say "info on slow queries" what am I looking for?

 

[BillyS]

You have a lot of tables (over 4,000), and there are a lot of query cache prunes per day. I would suggest the following. See if increasing the query cache size lowers the number of prunes... best to keep watch over time.

table_definition_cache=4096
table_open_cache=6000
query_cache_size=64M

 

[modom]

Ok thanks! I will post the changes this weekend.

 

[modom]

We had a harddrive failure so could not post.

I went back to msqld listings below in my.cnf for now.

In the mysql error log I see these. How do I fix this?

130621 8:44:43 InnoDB: error: space object of table 'horde/mnemo_shares_seq',
InnoDB: space id 256 did not exist in memory. Retrying an open.
130621 8:44:43 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
130621 8:44:43 InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './horde/mnemo_shares_seq.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.

I see these in the horde database:

horde.horde_prefs 1 client is using or hasn't closed the table prope...
horde.turba_shares 1 client is using or hasn't closed the table prope...
horde.turba_shares_seq 1 client is using or hasn't closed the table prope...

I ran the check table, repair table, analyze table, and optimize table and everything says it's ok but not sure.

[mysqld]

wait_timeout = 30
connect_timeout = 1
local-infile=0

open_files_limit=26520
default-storage-engine=MyISAM

max_connections=200

key_buffer_size = 600M
max_allowed_packet=20M

query_cache_size=30M
query_cache_limit=1M
tmp_table_size=50M
max_heap_table_size=50M
thread_cache_size=50

table_open_cache = 3000
table_definition_cache = 1000

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes

sort_buffer_size=256K
join_buffer_size=2M
read_rnd_buffer_size=2M

max_write_lock_count = 10
concurrent_insert=2

 

[modom]

My server specs are:
Two Intel Xeon 5310 "Clovertown" Quad Core processors (8-core)
One 250 GB SATA hard drive
4 GB Fully Buffered DDR2 RAM
Supermicro Motherboard

The slow query log for the one store is attached.

Here's the information from last night.

This is the my.cnf file I have now since a change earlier this morning. It has been running for about 5 hours.

[mysqld]

wait_timeout = 10
connect_timeout = 1
local-infile=0

open_files_limit=26520
default-storage-engine=MyISAM

max_connections=250

key_buffer_size = 1G
max_allowed_packet=20M

query_cache_size=84M #original=30M
query_cache_limit=1M
tmp_table_size=50M
max_heap_table_size=50M
thread_cache_size=50

table_open_cache = 6000
table_definition_cache = 4096

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes

sort_buffer_size=256K
join_buffer_size=2M
read_rnd_buffer_size=4M

low_priority_updates=1
#max_write_lock_count = 10
concurrent_insert=2

The mysqltuner from 8am 6/22/13 is:

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 384M (Tables: 3818)
[--] Data in InnoDB tables: 208K (Tables: 13)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 19

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 27m 2s (346K q [213.750 qps], 782 conn, TX: 454M, RX: 131M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 1.3G global + 6.6M per thread (250 max threads)
[OK] Maximum possible memory usage: 2.9G (77% of installed RAM)
[OK] Slow queries: 0% (1K/346K)
[OK] Highest usage of available connections: 3% (9/250)
[OK] Key buffer size / total MyISAM indexes: 1.0G/228.9M
[OK] Key buffer hit rate: 100.0% (49M cached / 13K reads)
[OK] Query cache efficiency: 92.0% (317K cached / 344K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 9% (773 temp sorts / 7K sorts)
[!!] Joins performed without indexes: 9
[OK] Temporary tables created on disk: 17% (1K on disk / 6K total)
[OK] Thread cache hit rate: 98% (9 created / 782 connections)
[OK] Table cache hit rate: 99% (986 open / 993 opened)
[OK] Open file limit used: 7% (1K/26K)
[OK] Table locks acquired immediately: 99% (70K immediate / 70K locks)
[OK] InnoDB data size / buffer pool: 208.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
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 2.0M, or always use indexes with joins)

I had set the query_cache_size to 64 which did bring the prunes down and last night the mysqltuner said to increase it so I increased it to 84M. Is this too large?

The load still shoots up over 20 when the one store, nobbieneezkids, posts a sale and lots of people come all at once but I would think this server could handle that. This was one reason I set the timeout to 10 instead of 30.

Thanks so much.