News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

MySQL 5.1 vs MySQL 5.5

Started by ashish101, April 13, 2012, 10:48:36 AM

Previous topic - Next topic

ashish101

Hi all

I recently upgraded WHM/cpanel to latest build which now offers MySQL 5.5 as an option. So:-

1. Could this cause an issue for a SMF2.x site?
2. Are they any performance hits or benefits?
3. What's the main benefit of MySQL 5.5 vs MySQL5.1 for SMF?

Thanks for your time.

Cheers

ashish101


LiroyvH

No it should not cause any issues, only improvements :)

It's always recommended to make a backup of your database prior to upgrading though. Hope you did that ;)
It should not post any issues at all, so thats good. Remember that cPanel's structure does demand you recompile your apache for the support packages. Just tell it to rebuild previous saved config and nothing will change.
((U + C + I)x(10 − S)) / 20xAx1 / (1 − sin(F / 10))
President/CEO of Simple Machines - Server Manager
Please do not PM for support - anything else is usually OK.

Roph

I just did this last night, Upgrade MySQL option from within WHM. It does take a while. Everything runs fine afterward, I didn't need the database backup I'd made. You can peek at my status.php if you'd like.

ashish101

Thanks for the feedback guys. I will give it a shot today.

Colin

Awesome, let us know if you have any issues. *Topic marked as solved
"If everybody is thinking alike, then somebody is not thinking." - Gen. George S. Patton Jr.

Colin

ashish101

Upgraded to MySQL 5.5, yay :). So far all looks stable.

What variables should I change in my.cnf? This is my current my.cnf:-
Quote[mysqld]
read_rnd_buffer_size=1M
innodb_flush_method=O_DIRECT
thread_cache_size=60
query_cache_min_res_unit=1K
query_cache_size=80M
tmp_table_size=64M
max_connections=250
table_open_cache=2048
table_definition_cache=1024
join_buffer_size=512K
query_cache_limit=8M
innodb_buffer_pool_size=1000M
innodb_flush_log_at_trx_commit=2
low_priority_updates=1
max_heap_table_size=64M
key_buffer_size=70M
read_buffer_size=1M
long_query_time=3
wait_timeout=18000
concurrent_insert=2
default-storage-engine=MyISAM
innodb_file_per_table=1

Roph

We can't help you with that until we know the specs of your server and what kind of resources you have available, along with what you'll be running on it. Though if that site in your sig is the only thing you're hosting there, a 1GB buffer pool size should last you quite a while. I'm running a ~450k post, ~200 online forum on a 192M innodb buffer pool.

This is assuming you're using InnoDB of course. Have you converted your forum to InnoDB? this topic explains most of it. Though your site is fast for me, I'd say if it ain't broke, don't fix it :)

ashish101

Quote from: Roph on April 16, 2012, 09:12:37 AM
We can't help you with that until we know the specs of your server and what kind of resources you have available, along with what you'll be running on it. Though if that site in your sig is the only thing you're hosting there, a 1GB buffer pool size should last you quite a while. I'm running a ~450k post, ~200 online forum on a 192M innodb buffer pool.

This is assuming you're using InnoDB of course. Have you converted your forum to InnoDB? this topic explains most of it. Though your site is fast for me, I'd say if it ain't broke, don't fix it :)
Fair enough, below is the info requested:-
1. Dedicated server: Intel Q9400 2.66GHz (quad-core) with 4Gb RAM.
2. Running only SMF sites mainly www.sharetermpapers.com.
3. Atleast 120 users online all the time. 130k+ posts, 170k+ registered users. 5k+ logins everyday.
4. SMF already optimized as per the suggestions in the "24 things for SMF".
5. Using nginx for caching static files
6. It's not broken but I believe in optimizing for best performance and re-visiting it every 3-6months, depending on the traffic, load etc.

Question:-
1. What new variables can I use with MySQL 5.5. What should be their values for SMF site of above size and activity?
2. Is there any value I should change in my current my.cnf?
3. Is there anything else I should do for my growing forum?

Many thanks for your valuable input and help.

Cheers
Ashish

Roph

If you place the status.php script in your forum root it may give some clues as to where you could tweak things.

ashish101

Yes, it's already there.

http://www.sharetermpapers.com/status.php

Although, it looks good, it's quite similar to pre-upgrade as nothing has changed in my.cnf. And I just wanted to know what are the new variables which I should change in my.cnf to improve MySQL performance.

Roph

From my perspective you don't need any MySQL performance improving though. What you have is good, and works well. I browsed around your forum and it was consistently speedy. :)

And with what you have now, I'd imagine you'll be fine for quite a while. Are you experiencing any slowness or locking?

ashish101

Quote from: Roph on April 18, 2012, 04:01:50 AM
From my perspective you don't need any MySQL performance improving though. What you have is good, and works well. I browsed around your forum and it was consistently speedy. :)

And with what you have now, I'd imagine you'll be fine for quite a while. Are you experiencing any slowness or locking?
Good to hear your positive feedback.

No slowness or locking, everything is fine, just exploring if anything else can be done.

Thanks for your time :).

Cheers

JosephGreen

I've no idea but i'm try and  replay as soon as possible.

ashish101

I am trying a few things myself, let's see how it goes.

Advertisement: