Mysql – “Site Offline” MySQL server failing to start and stop

innodbMySQL

I'm hosting a few sites on linode and have encountered a strange problem. For the last two years the site has been running perfectly fine, and now randomly all the websites on the server go to the "site offline check the settings.php" page. No changes have been made to the website at all recently.

when i try go do mysqld stop it says busy for a while then finally says failed, while doing start results in an instant fail.

Many of the threads I googled suggest the hostname is off in the settings.php but since the website has been up for two years I don't think this can be the case.

I haven't had to troubleshoot mySQL before, but if i remember correctly the log file I should be looking at is Hostname.err. In there, it has a large chain of errors concerning InnoDB. The one listed as fatal error says:

InnoDB: Fatal error: cannot allocate memory for the buffer pool

I would appreciate any suggestions, and if there are log files that would help let me know.

Edit: Requested information.
1) When I examine the CNF, it appears that all lines involving innoDB are commented with #. This means that it has always been this way as I have not modified it.

2)mysql Ver 14.14 Distrib 5.5.14, for Linux (i686) using readline 5.1

3) It looks like there is a giant jump in the IO rate every time I try to start the database / before the websites die. How do i go about clearing those cache tables if they have become too unmanageable and is there any risk of losing anything?

Third Edit (Now with datadir):

How much RAM is on the VM ? 512MB

How much space does datadir have ?

Size: 20G Used: 19G Available 0 Use Percent: 100%

What is the size of ibdata1 ( ls -lh /var/lib/mysql/ibdata1)? 114M

FYI: If anyone looks here with the same problem in the future, My issue was the the binary log files had consumed my entire disk space. See here http://systembash.com/content/mysql-binary-log-file-size-huge/

Best Answer

OMG !!! Look at the output of df -h

Size: 20G Used: 19G Available 0 Use Percent: 100%

YOU HAVE NO DISK SPACE !!!

Get more diskspace freed up or have linode increase diskspace.

According to MySQL 5.0 Certification Study Guide

enter image description here Page 408,409 Section 29.2 Bulletpoint 11 says:

If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.

In reality, mysqld is not dead. It is waiting for free diskspace to continue whatever it normally does. Even during startup, if mysqld makes a temp table, it is a MyISAM table. No space left on the disk? No problem. MyISAM just patiently waits for you to make free space.

If you have an error file like /var/log/mysql/log, clear its contents like this:

echo -n > /var/log/mysqld.log

If the error file in /var/lib/mysql/my-machine.err then do this:

echo -n > /var/lib/mysql/my-machine.err

Give it a Try !!!

Related Solutions

MySQL Replication: Insert generating VERY huge temporary tables

Algorithmically, it is doing what your colleague says. But, do you see what it is doing ???

It is generating 10,000 temp tables each containing 1 row after traversing 317 million rows through in the InnoDB internal index. Each temp table is a complete regeneration of the rowids in sernumbers_results_2009 table along with executing handler_read_prev commands internally to sort the data by an index scan from the back of the internal rowid index. Also, please remember you are dealing with InnoDB. Who knows what Multiversioning (via MVCC) is going on so that the INSERT is completed without interference and with rollback capabilities.

Is there any reason why this query wouldn't work for you ???

INSERT INTO sernumbers_results_2009
SELECT * FROM sernumbers_results
ORDER BY rowid DESC LIMIT 10000;

This will definitely generate one temp table.

Give it a Try !!!

Mysql – Determining max database and table size supported and present size

Here are some nice queries I have used for years:

This reports the amount of data and indexes for each storage engine:

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

This reports the amount of data and indexes for each database:

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,index_length XSize,data_length+index_length TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA GROUP BY DB WITH ROLLUP) AA,
(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

This reports the amount of data and indexes for each database by storage engine:

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size" FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,IF(ISNULL(engine)=1,10,0) engine_score,IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') DataSize,CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

Please notice at the end of each query there is an inline query (SELECT 3 pw). The pw is simply an exponent used as a power of 1024. Here is how to use this:

(SELECT 0 pw) reports in bytes
(SELECT 1 pw) reports in kilobytes
(SELECT 2 pw) reports in megabytes
(SELECT 3 pw) reports in gigabytes
(SELECT 4 pw) reports in terabytes
(SELECT 5 pw) reports in petabytes (If you got this much data, write me or at least post the display in DBA.SE. I'm sure we'd all love to see that)

Give it a Try !!!

As for table limits, here are those limits for MyISAM and InnoDB.

Best Answer

Related Solutions

MySQL Replication: Insert generating VERY huge temporary tables

Mysql – Determining max database and table size supported and present size

Related Question