Large message boards and MySQL logs of slow response

[Agelmar]

I notice you are using ANALYZE TABLE - why not use OPTIMIZE TABLE? OPTIMIZE TABLE (assuming that you are running MyISAM tables) will do a lot more - It reclaims unused space left by deleted records (i.e. if you delete / modify a post), updates indexes, repairs the table after rows have been deleted or split, sorts the index pages if they're not sorted, and updates statistics..... also defragments the datafile.

You can simply replace ANALYZE TABLE with OPTIMIZE TABLE in the previously posted code snippet and your board should be even happier . (OPTIMIZE TABLE takes a bit longer than ANALYZE TABLE, but not much. I did it table by table, and on my board, the largest table (messages), with about 25,000 messages, only took 2.5 seconds. Entire process took less than ten seconds for all tables. Note that the board will be unavailable while you do OPTIMIZE TABLE, as it does place a lock on the table while OPTIMIZE TABLE is running, but it should only take a few seconds.)

[Jeff Lewis]

They do different things...optimise.php is also included in this zip that you can find in this thread.

[Agelmar]

Yes, OPTIMIZE TABLE does more than ANALYZE TABLE (it performs the same function as ANALYZE TABLE and then some). If I am not mistaken, OPTIMIZE TABLE (on a MyISAM table) is the same thing as doing myisamchk --quick --check-only-changed --sort-index --analyze whereas ANAYLZE TABLE is the same as myisamchk -a. (for those who don't know, -a is the same as --analyze on myisamchk)... as such, I would think that there would be no real reason not to go with OPTIMIZE TABLE rather than ANALYZE TABLE.... at the most it will tack on another few seconds...

[mediman]

correct, but don´t use to often optimize cause mysql will change then everytime the statistics (mysql use with join table not the keys but the statistics and optimize clear this everytime)!

if you will for you weekly optimizing use table optimize then you have to add to the join select queries the "use key" statement to have a good speed! remember the probs with any queries in 130, this queries have had keys but it was a join select querie so it was very slow!

mediman

[groundup]

mediman - you just confused the hell out of me

[groundup]

can we have this moved to FAQ or somewhere, where it is more noticealbe?

[Stud Muffin]

When using the convert php i get this?

Fatal error: Call to undefined function: mysql_get_server_info() in /files/local/home/earthdome/forum/convert.php on line 10

I'm running version  3.22.32 of MySql

I have since read that i need version 3.23.xx.
Time to hassle my host.

[Shaun]

Holy Moly!!

I just ran the scripts on my 1.4.0 board with 40000+ posts - 4000+ topics - 1488 members.


IT IS BLAZING FAST NOW!!!!!

 

[Michele]

For Boardnation I built the optimization into a new page I added to the Admin center.  Maybe the code to optimize the db should be called each time someone visits the Admin center or a new page created that deals with db utility stuff, backing up, optimizing . . .

Just added a page to my YaPP setup too.

[Michele]

Be careful with these scripts... I'm guessing the scripts place a temporary high load on the SQL Servers because 5 minutes after I ran it, my site went down due to excess bandwidth.

I've got an email into them checking to see if that (load on SQL server) was the reason for the bandwidth, as I've used less than 10% of monthly bandwidth so far this month.

I'll let you know what they say as soon as I hear from my host.

Argh...

EDIT: I heard from my host... dang buggy CPanel software. It only showed 300MB of bandwidth used, when in actually, one of my sub-domains had used 4.5GB themselves and I exceeded the 5GB that way. Why their CPanel can't keep track of it on a timely basis, I dunno... the webalizer and analog web stats don't show my sub-domains either, so it was hard to track down what happened.

So, it was just weird karma that the Optimize bit threw me over the bandwidth for the month and closed down my site.

Argh... Moya

Oh, BTW, I'm back up again... had to buy more bandwidth though to make it through the month.

[Michele]

Well, I'm going back down for a bit again.

My host (dzones.com) decided that it was time to upgrade me to the new servers (RedHat 7.2 from 6.2) to hopefully get rid of the buglets regarding my CPanel and bandwidth display problems. I'll have a new IP and it will take a little time to fix up the domain to point to the new IP.

Any gotchas I need to look out for?

BTW, do most host sites give you a static IP like mine does?

Thanks, MM

[Daniel Hofverberg]

BTW, do most host sites give you a static IP like mine does?

Static IP: Yes. Unique IP: No. In my experience, tje majority of web hosts have one static IP shared for all domains/subdomains on the server (name-based hosting via HTTP_HOST).

[oldford]

Well, add another one to the "works great for me" category.  

I just did this on my board and it's a very nice improvement in speed. My board was getting fairly large and really slowed down in the last few weeks. I had optimized all tables, but it still wasn't helping. After following the instructions in this post I'm VERY happy with the results. FYI, my board stats are:

Members: 1619   Posts: 51896   Topics: 7418

Thanks!!!

[Michele]

BTW, do most host sites give you a static IP like mine does?

Static IP: Yes. Unique IP: No. In my experience, tje majority of web hosts have one static IP shared for all domains/subdomains on the server (name-based hosting via HTTP_HOST).

Actually, when I run a tracert to my IP address, it resolves to my domain name when it gets there... so maybe I do have a unique IP? Either that, or since I'm on a new server, maybe I'm just the only one using it yet.

Have fun, MM

[djMostly]

well, i used this query and i see no noticable speed increase.  the host i'm using is *fast* and i've made sure that it wasn't just normal lag.  anyone else not get anything out of this?