Mysql crash after some hours even i have 8gb Ram

[tomer628]

This is first step for tuning your server. Swap is needed for extreme traffic. (For a moment mostly.) At normal usage, there is nothing to do with swap. You have to tune your mysql for your needs.

OK...
how can i do that ?

mysqltuner.com is an very useful tool to tune your MySQL server.

After 24+ hours it's happened again - crash..

So I did the mysql tuner. Com....
And this the result :

Code: Select all

# perl mysqltuner.pl
 >>  MySQLTuner 1.6.1 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/ >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.46
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 17M (Tables: 111)
[--] Data in InnoDB tables: 832M (Tables: 74)
[!!] Total fragmented tables: 9

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[!!] User 'admin_your555@%' hasn't specific host restriction.
[--] There is 605 basic passwords in the list.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 9h 39m 11s (1M q [12.014 qps], 393K conn, TX: 4B, RX: 270M)[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Total buffers: 168.0M global + 2.8M per thread (200 max threads)
[OK] Maximum reached memory usage: 396.2M (5.16% of installed RAM)
[OK] Maximum possible memory usage: 718.0M (9.35% of installed RAM)
[OK] Slow queries: 0% (175/1M)
[OK] Highest usage of available connections: 41% (83/200)
[!!] Aborted connections: 85.65%  (336708/393111)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 595K sorts)
[!!] Temporary tables created on disk: 91% (336K on disk / 366K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 98% (384 open / 391 opened)
[OK] Open file limit used: 32% (335/1K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 41.9% (3M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/4.7M
[OK] Read Key buffer hit rate: 100.0% (385M cached / 1K reads)
[OK] Write Key buffer hit rate: 100.0% (76M cached / 4K writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/832.2M
[OK] InnoDB buffer pool instances: 1
[OK] InnoDB Used buffer: 100.00% (8192 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 100.00% (550897368 hits/ 550921331 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 13792 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate unclosed connections and network issues
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    innodb_buffer_pool_size (>= 832M) if possible.
[root@host.. ~]# chmod +x mysqltuner.pl

So What I need to do now after this running?

[tjebbeke]

Code: Select all

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate unclosed connections and network issues
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    innodb_buffer_pool_size (>= 832M) if possible.

Edit /etc/my.cnf and edit:
query_cache_size (>= 8M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
innodb_buffer_pool_size (>= 832M) if possible.

[tomer628]

Code: Select all

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate unclosed connections and network issues
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    innodb_buffer_pool_size (>= 832M) if possible.

Edit /etc/my.cnf and edit:
query_cache_size (>= 8M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
innodb_buffer_pool_size (>= 832M) if possible.

OK..
I added this values and now this what I have:

Code: Select all

 nano 2.0.9            File: /etc/my.cnf                                GNU nano 2.0.9            File: /etc/my.cnf                              
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
max_connections=200
max_user_connections=30
wait_timeout=30
interactive_timeout=50
long_query_time=5
#log-queries-not-using-indexes
#log-slow-queries=/var/log/mysql/log-slow-queries.log

query_cache_size=8M
tmp_table_size=20M
max_heap_table_size=20M
thread_cache_size=4
innodb_buffer_pool_size=832M

#innodb_use_native_aio = 0
innodb_file_per_table

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[tjebbeke]

Did you restart MySQL? Hopefully, it works better.

[tomer628]

Did you restart MySQL? Hopefully, it works better.

Yes, I did.
I'm hope too..

[tomer628]

Now this crashed again..
I got on my website http 500 Internal error..
And I saw in Server tab that Httpd took more than 22,000MB..
So may be because of this I can't access to my website at this moment..

why this happened.
How can I fix that?

[Joop]

I have the same problem like you. Have you an solution how to fix this problem?

I have an vps from OVH with 2 gb of RAM but my HTTP server took 2200 mb so I think the database server had to less RAM must be closed.

[tomer628]

WISH I KNOW

[Joop]

Maybe I have found the reason for the problem.

Before my Mysql server stops, I see a very high amount of query's.



Do you have this too?

[tomer628]

yes,
but wich plugins you use for wordpress ?
maybe i will found same problems...

and how much is your sql in MB ?