I modified my my.cnf and now my site is toast

Hello,

I host www.phillyfuture.org on Linode. Been very happy with the service.

I was trying to improve mysql's performance a few hours ago by modifying my.cnf to better utilize the memory on my system .

Now, no matter what I do, I get many variants of the following error:

Warning: Incorrect information in file: './phillyfuture/cache.frm' query: UPDATE cache SET data = 'N;', created = 1269149472, expire = 0, headers = '' WHERE cid = 'variables' in /var/www/www.phillyfuture.org/web/includes/database.mysql.inc on line 174

Here is my memory configuration:

http://pastebin.com/9jHwrgm0

The database uses Innodb.

Here is the my.cnf file (the commented out innodb section is what I was trying to change)

http://pastebin.com/a9dbHuCA

As of right now, the site is down with the error indicated above.

Line 174 in database.mysql.inc is

triggererror(checkplain(mysqlerror($activedb) ."\nquery: ". $query), EUSERWARNING);

I can login to the database and it appears okay.

/proc/version reports:

Linux version 2.6.18.8-linode22 (root@build.linode.com) (gcc version 4.3.3 (Ubuntu 4.3.3-5ubuntu4) ) #1 SMP Tue Nov 10 16:12:12 UTC 2009

And MySQL reports its version is

Server version: 5.0.32-Debian_7etch8-log Debian etch distribution

Thank you in advance for any help or direction you can provide.

Karl

4 Replies

I have the site working again, but performance is still poor and I believe by modifying my.cnf appropriately, I could better use my hosting resources.

The fix, btw, was reverting what I did from this MySQL forum postin on changing innodblogfile_size:

http://forums.mysql.com/read.php?22,298 … msg-298782">http://forums.mysql.com/read.php?22,298678,298782#msg-298782

I had to revert my ib_log files.

If anyone here has any suggestions on appropriate my.cnf settings, please share.

Thanks,

Karl

There are a lot of things you can do to improve performance, but it is near impossible to tell you specifically what to do. It all depends on many factors:

  • what storage engines do you use?

  • what is your access pattern like?

  • what kind of certainty do you need when a crash occurs?

  • What is the size of your dataset and the size of your active dataset?

  • What other software is running on your system?

etc. etc.

The problem with MySQL tuning is that there are many factors that play a part in it, and it seems to be a matter of change, test, implement (or revert). That said, there are companies who can help you out with that, like Open Query or Percona (disclaimer: i do work for the first one ;) ). You usually give them access to your server and they will change things for you (or with you, dpending on your preference).

Have you tried something as simple as the my-medium.cnf configuration?

What's the output of this query?

show variables like 'innodb%';

The default configurations that come with MySQL are ancient and should not be used. I wish they never existed in teh first place ;)

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct