Previous Thread
Next Thread
Print Thread
Rate Thread
Joined: Oct 2001
Posts: 213
Member
Member
Offline
Joined: Oct 2001
Posts: 213
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

Sponsored Links
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
Glad it's zipping for you now


- Allen wavey
- What Drives You?
Joined: Jul 2001
Posts: 442
Enthusiast
Enthusiast
Offline
Joined: Jul 2001
Posts: 442
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.

Joined: Oct 2001
Posts: 213
Member
Member
Offline
Joined: Oct 2001
Posts: 213
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

Joined: Jul 2001
Posts: 442
Enthusiast
Enthusiast
Offline
Joined: Jul 2001
Posts: 442
[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 ?

Sponsored Links
Joined: Jun 2002
Posts: 670
Code Monkey
Code Monkey
Offline
Joined: Jun 2002
Posts: 670
That's actually very interesting. I'm glad Jeremy is my host so he can fiddle with all the MySQL stuff for us

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
Jeremy

Joined: Oct 2001
Posts: 213
Member
Member
Offline
Joined: Oct 2001
Posts: 213
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.

Joined: Jul 2001
Posts: 442
Enthusiast
Enthusiast
Offline
Joined: Jul 2001
Posts: 442
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.

Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
colorado, are you using MySQL 3.x or 4.x?

Sponsored Links
Joined: Mar 2003
Posts: 215
Junior Member
Junior Member
Offline
Joined: Mar 2003
Posts: 215
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.

Joined: Jul 2001
Posts: 442
Enthusiast
Enthusiast
Offline
Joined: Jul 2001
Posts: 442
I'm pretty sure it is 4.x, I will get a list of all my configuration for hardware/software together.

Joined: Jul 2001
Posts: 442
Enthusiast
Enthusiast
Offline
Joined: Jul 2001
Posts: 442
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

Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
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.

Joined: Jul 2001
Posts: 442
Enthusiast
Enthusiast
Offline
Joined: Jul 2001
Posts: 442
Will do, and all this time I thought I was on 4.0

Joined: Oct 2001
Posts: 213
Member
Member
Offline
Joined: Oct 2001
Posts: 213
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>

Joined: Jul 2001
Posts: 442
Enthusiast
Enthusiast
Offline
Joined: Jul 2001
Posts: 442
[]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

Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
notice any difference right off the bat?

Joined: Jul 2001
Posts: 442
Enthusiast
Enthusiast
Offline
Joined: Jul 2001
Posts: 442
Lot less processes running out of the box, I'm still getting locked queries so will have to play around with the settings.

Joined: Oct 2001
Posts: 213
Member
Member
Offline
Joined: Oct 2001
Posts: 213
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

Joined: Oct 2000
Posts: 2,223
Veteran
Veteran
Offline
Joined: Oct 2000
Posts: 2,223
[]coloradok5 said:

> Linix version
Linux 2.2.19 [/]

ewwww.

You using RedHat 6.2 or something?


Picture perfect penmanship here.

Link Copied to Clipboard
Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with and recommend the following Web Hosts:
Stable Host
bluehost
InterServer
Visit us on Facebook
Member Spotlight
Posts: 70
Joined: January 2007
Forum Statistics
Forums63
Topics37,573
Posts293,925
Members13,849
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,833
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 82
Gizmo 20
Brett 7
Morgan 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2024 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20221218)