UBB.Developers Forums General Discussion Suggestions and Feedback How we solved 6.4.1 performance issues (long)

I chimed in on a few threads about 6.4.1 performance issues, I won't rehash that here except to say our forums were dead slow off and on. The past few weeks, we had to stop and restart MySQL every 12-18 hrs to clear out the processes as well.

I got great help from Jeremy's thread on Let's Tune MySQL in the MySQL forum here. Rick really stuck it out with us and was very patient and helpful.

Anyway, to get our 6.4.1 forums flying again, here's what I discovered.

First, log-bin was in our my.cnf. This writes every transaction to a flat file on disk - great for restoring in a disaster, terrible for server performance. Once removed, we got an immediate lift in the forums and on the server.

Second, 6.4.1 IMHO is very sensitive to a properly tuned my.cnf. Our 6.3.2 forums screamed w/ a near-stock my-large.cnf. In short, using Jeremy's thread to tweak ours, I solved the locked tables problem and significantly reduced server overhead. It required some experimenting with the key parameters in the file, but in the end it was time well spent. And I learned a lot.

Finally, we have another program bouncing against MySQL, phpAdsnew. The config of this program + bad entries in our my.cnf was causing MySQL to spawn new processes constantly and eventually hang. After reading up on this program and getting admin access, I found it had been configured to use persistent connections and there was no timeout on them. I disabled persistent connections Monday night, restarted MySQL, and our performance is as good as ever, server overhead is very low, and the MySQL stats are in that sweet spot.

So, thanks again Jeremy and especially Rick! and if anyone else is having problems with this version, you really need to use Jeremy's thread and understand your my.cnf and what's happening on your server.

(And now we are on 6.4.2, got our stickies fixed, and couldn't be happier!)

joe

Glad it's zipping for you now

Great news
Are you getting locked files whenever a search is being perfomed? If no, I will be sending you a PM asking what your my.cnf settings are, I think your server specs are close to mine.

We are not seeing any locked tables at all any longer. That was the biggest improvement, obviously.

Here's the top part of our my.cnf, I think I still need to tweak it more, although our performance stats are nearly ideal. 1.8Ghz Intel, 1GB RAM, 60(?)GB HDD, RH7.3, MySQL3.23.55.

*******

skip-locking
set-variable = max_connections=150
set-variable = key_buffer=512M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=4M
set-variable = record_buffer=2M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8

[quote
*******

skip-locking
set-variable = max_connections=150
set-variable = key_buffer=512M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=4M
set-variable = record_buffer=2M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8 [/]

Our servers are really close, I do have 2 gig of RAM also. Do you think I should raise my key_buffer?

Here's what I have right now:

# custom vars
set-variable = max_connections=200
set-variable = join_buffer=1M
set-variable = sort_buffer=4M
set-variable = thread_cache_size=50
set-variable = table_cache=1024
set-variable = key_buffer=64M
set-variable = max_connect_errors=10000
set-variable = max_allowed_packet=1M
set-variable = record_buffer=2M


What is skip-locking ?

That's actually very interesting. I'm glad Jeremy is my host so he can fiddle with all the MySQL stuff for us

Jeremy

I definitely think you should raise your key buffer.

Do you have shell access to your server? If so, running some commands to get data is real easy.

mysqladmin -u <mysqlusername> extended-status

And post results here.

When I run that command, I get:
mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)'
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
-------------------
I can not locate mysqld.sock either so I will have to find out where my host has that info or where I can find it, nothing seems like it is located in a standard location, oh joy.

Here is my current my.cnf settings:

set-variable = max_connections=200
set-variable = join_buffer=1M
set-variable = sort_buffer=4M
set-variable = thread_cache_size=8
set-variable = table_cache=1024
set-variable = key_buffer=512M
set-variable = max_connect_errors=10000
set-variable = max_allowed_packet=1M
set-variable = record_buffer=2M

It is runing quicker than it ever has I think but still getting locked files so not quite there yet, maybe drop table_cache down to 512 and see how it does.

colorado, are you using MySQL 3.x or 4.x?

http://www.mysql.com/doc/en/Binary_log.html

log-bin logs transactions that change, not all transactions.

[]
Running the server with the binary log enabled makes performance about 1% slower.
[/]

I wonder how true that actually is.

I'm pretty sure it is 4.x, I will get a list of all my configuration for hardware/software together.

Arrg, looks like I am only on MYSQL 3.23.55, I will have them upgrade that.


> CPU
dual 800 MHz Pentium III processors

> memory
2GB PC133 RAM

Quantum Atlas IV 18GB SCSI boot and data drive
Maxtor 93073U6 30GB IDE backup drive

> PHP version
4.3.4

> MYSQL version
3.23.55 (though we can now upgrade to v4.0 if you desire)

> Apache version
1.3.27

> Linix version
Linux 2.2.19

you really should upgrade to MySQL 4, there will be tremendous speed increases, as well as new caching features to really speed up larger and active databases.

Will do, and all this time I thought I was on 4.0

We are on the same version of MySQL. I too would like to upgrade. But it's been over a week now and it seems our erformance problems are solved for good. <fingers crossed>

[]Jeremy said:
you really should upgrade to MySQL 4, there will be tremendous speed increases, as well as new caching features to really speed up larger and active databases. [/]

OK, I am now on MYSQL 4

notice any difference right off the bat?

Lot less processes running out of the box, I'm still getting locked queries so will have to play around with the settings.

Welp, nevermind. Ours just completely melted down today, no rhyme or reason.

I see MySQL gobbling up memory. Swap is relatively untouched. CPU cycles from 99% used to 99% idle.

The forums will blaze, then crawl.

Host says MySQL is the culprit. I have been tweaking my.cnf throughout the day, monitoring, and results are erratic.

I am at wits' end with this. We got almost 2 weeks of superb performance, then hit the wall. :sighs:

joe

[]coloradok5 said:

> Linix version
Linux 2.2.19 [/]

ewwww.

You using RedHat 6.2 or something?