One of my admins was just checking a new member who seemed to be trolling, and ran a search for posts made by the IP # which caused a backlog in mysql while the query ran. In the meantime, I created 2 new indexes which removed this long slow query from causing a bottleneck. It wasn't a huge deal before but now that we've got over 1 million posts it causes the server load to spike and mysql processes to wait for the query to finish. I took the board down briefly, and created 2 new indexes which solved this issue for us.

create index ip_address on forums_posts(ip_address);
create index ip_address on forums_members(ip_address);

This worked well for us anyway. Are there any other tips for larger forums where you've been able to optimize the database or code?

That's actually a known one, if you're doing alot of searching for IP addresses on a larger board.

There are few tips for optimizing IPB itself... The best things you can do are to optimize php.ini, my.cnf and httpd.conf (Assuming Apache)

 

There are few tips for optimizing IPB itself... The best things you can do are to optimize php.ini, my.cnf and httpd.conf (Assuming Apache)

Bumping up an old thead I found in a search, hope you don't mind...

Where can I find information about doing the above, as suggested? I tried doing a search here for the file names and get "too few characters" errors.

I've got a very large board and limited knowledge about server-side things. Any help would be appreciated.

Changing the table type of the sessions table to HEAP is also a good speederuper. I believe there is an optimization guide in the wiki, I'll see if I can find it.

 

Changing the table type of the sessions table to HEAP is also a good speederuper. I believe there is an optimization guide in the wiki, I'll see if I can find it.

I got it:
http://www.ipsbeyond.com/forums/index.php?...;showarticle=49

Set the 'Update topic views immediately' setting in the acp to No - this stops blocked queries on the topics table.

I also set topic_markers table type to innodb.

Enable fulltext searching if it's not already enabled.

Does anyone have any tips on getting a bit more speed on your board ?

Quicker refresh, etc, mine seems slow even on adding a new post, in comparision to my old IFree site which used IPB 1.3 or is it just that the latest software is slower.

Any adivce on settings to speed thing up most welcome :thumbs: , Thanks

I don't have innob is there something else to change to:
http://www.armysystem.com/screenies/inno.gif

http://www.ipsbeyond.com/forums/index.php?...ic=5287&hl=

I notice settings need changed in PHPmyadmin, err where do I find this ?

ask your host

if you have cpanel access there should be a link to phpmyadmin

but I would ask your host first.

 

ask your host

if you have cpanel access there should be a link to phpmyadmin

but I would ask your host first.

Thanks, I have cPanel, never used it yet, I'll have a browse and see what I can Break :blink: with that
" Heap" setting.

 

ask your host

if you have cpanel access there should be a link to phpmyadmin

but I would ask your host first.

I'm looking in cPanel but there is no sign of a phpmyadmin, Host is IPS.

try this link:

http://yourdomain:2082/3rdparty/phpMyAdmin/index.php of course you will change yourdomain to "Your Domain"

if not ask your host if you can access php myadmin and how

Thanks the link did the trick.

Unfortunatly when trying to change the setting to "HEAP" this error message is shown

Error
SQL query:

ALTER TABLE `ibf_topic_markers` ENGINE = HEAP

MySQL said:

#1163 - The used table type doesn't support BLOB/TEXT columns


So I'm stuck again :doh:

 

I'm looking in cPanel but there is no sign of a phpmyadmin, Host is IPS.

Go to cPanel, select MySQL, and look for the phpMyAdmin link at the bottom of the page. Be careful in there. :thumbs:

 

Thanks the link did the trick.

Unfortunatly when trying to change the setting to "HEAP" this error message is shown

Error

SQL query:

ALTER TABLE `ibf_topic_markers` ENGINE = HEAP

MySQL said:

#1163 - The used table type doesn't support BLOB/TEXT columns

So I'm stuck again :doh:

odd I thought brandon said to make that table innodb and another part of the thread said to make the sessions table HEAP/Memory

Well as you'll guess I'm not too clued up on this :unsure: nor am I confident enough to start making cahnges which may screw things up, I don't mind following instructions, but it looks like that one does not work, or perhaps I've missed a part.

How difficult is it to make the posts table InnoDb? I heard this will resolve the locked table issues which plague MYISAM tables under heavy load. Are there any draw backs, etc?


I need to reduce my locked tables %. Right now it is getting high and causing the forums to get high load spikes (10-15) several times a day for 3-5 minute periods.

 

MySQL 4.1.21-standard uptime 1 17:39:5 Thu Aug 24 13:51:32 2006

-- Key -----------------------------------------------------------------

Buffer usage 85.18M of 96.00M %Used: 88.73

Write ratio 0.04

Read ratio 0.00

-- Questions -----------------------------------------------------------

Total 3.32M 22.16/s

Slow 735 0.00/s %Total: 0.02 %DMS: 0.04

DMS 1.93M 12.84/s 57.95

-- Table Locks ---------------------------------------------------------

Waited 31.20k 0.21/s %Total: 1.40

Immediate 2.19M 14.62/s

-- Tables --------------------------------------------------------------

Open 512 of 512 %Cache: 100.00

Opened 2.10k 0.01/s

-- Connections ---------------------------------------------------------

Max used 191 of 300 %Max: 63.67

Total 265.96k 1.77/s

-- Created Temp --------------------------------------------------------

Disk table 4.09k 0.03/s

Table 15.99k 0.11/s

File 6.43k 0.04/s

You will lose full text searching though wont you? :/

yeah I would lose that with InnodB... hard choice.

Well, IMO I would choose stability over "speed" when it comes to data like that. Full Text indexing is a good feature, but if MyISAM can't handle the larger size it isn't worth losing everything or forum downtime.

 

odd I thought brandon said to make that table innodb and another part of the thread said to make the sessions table HEAP/Memory

STOP

You do NOT want to make topic_markers a HEAP table type - MySQL 5 does support text/blog columns in a HEAP type, but do not do it for topic_markers!!

I said to do it for the sessions table. If you are not 100% sure on how to do this stuff, I highly recommend leaving well enough alone. ;)

HEAP table type stores the data in memory. So, say you WERE able to do it for topic_markers.

#1) - that's 300,000+ rows (on some sites) stored all in memory. :blink: - You probably don't have enough memory to do this (since there is a config limit for it in mysql config)
#2) If the server is rebooted, all data stored in the table is lost!!!

For sessions, it's not a big deal. Data is recreated easily. For other tables, well, do you want to lose all your data when the server crashes or is rebooted?



Re changing table posts to innodb - I do not recommend it.

MyISAM is the fastest engine in mysql for searching. Since the posts and topics tables are frequently searched, changing to innodb would kill your site most likely, if it were large. Forget the locked queries, searches will bring the server down.

There are very very few queries that actually update the posts table - really only when a post is edited.

forums.televisionwithoutpity.com, while having some extensive hardware, has over 4 million posts and is using myisam for the posts table without issues. myisam is not a bad storage engine for this table.

Thanks on the useful info :thumbs: Think I'll leave the tuning for speed alone, it's just that it takes ages for a post or reply to add, sometimes just get a blank screen with "Connecting to Site"

Most sites seem a lot faster than mine, even my old :doh: InvisionFree runs faster

so you recommend then to change topic_markers to InnoDB? How do I do that if so... 90% of my slow queries are from this. I would be willing to prune this also... can I do that? There are entries in there from the day my forums was upgraded to 2.1.x from 1.3.x. Also 77,576 of the 80,267 rows have the `marker_last_cleared` set equal to 0 (they have never been cleared)... Maybe I am confusing myself but is this a problem?


Example:

 

# Time: 060825 6:56:06

# User@Host: forums_forums2[forums_forums2] @ localhost []

# Query_time: 83 Lock_time: 38 Rows_sent: 18 Rows_examined: 18

SELECT * FROM crm_topic_markers WHERE marker_member_id=21041;

# Time: 060825 6:56:09

# User@Host: forums_forums2[forums_forums2] @ localhost []

# Query_time: 87 Lock_time: 39 Rows_sent: 29 Rows_examined: 29

SELECT * FROM crm_topic_markers WHERE marker_member_id=10840;

# Time: 060825 6:56:23

# User@Host: forums_forums2[forums_forums2] @ localhost []

# Query_time: 100 Lock_time: 38 Rows_sent: 31 Rows_examined: 31

SELECT * FROM crm_topic_markers WHERE marker_member_id=19486;

# User@Host: forums_forums2[forums_forums2] @ localhost []

# Query_time: 31 Lock_time: 0 Rows_sent: 15 Rows_examined: 15

SELECT * FROM crm_topic_markers WHERE marker_member_id=21747;

# User@Host: forums_forums2[forums_forums2] @ localhost []

# Query_time: 31 Lock_time: 0 Rows_sent: 15 Rows_examined: 15

SELECT * FROM crm_topic_markers WHERE marker_member_id=21747;

# Time: 060825 6:55:17

# User@Host: forums_forums2[forums_forums2] @ localhost []

# Query_time: 35 Lock_time: 0 Rows_sent: 1 Rows_examined: 0

SELECT * FROM crm_topic_markers WHERE marker_forum_id=80 AND marker_member_id=21453;