Sire_merged Posted March 27, 2006 Posted March 27, 2006 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?
Labonte18 Posted March 28, 2006 Posted March 28, 2006 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)
k-mi Posted August 24, 2006 Posted August 24, 2006 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.
Digi Posted August 24, 2006 Posted August 24, 2006 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.
Klass Posted August 24, 2006 Posted August 24, 2006 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
bfarber Posted August 24, 2006 Posted August 24, 2006 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.
pygor_merged Posted August 24, 2006 Posted August 24, 2006 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
Klass Posted August 24, 2006 Posted August 24, 2006 I don't have innob is there something else to change to:http://www.armysystem.com/screenies/inno.gif
Klass Posted August 24, 2006 Posted August 24, 2006 http://www.ipsbeyond.com/forums/index.php?...ic=5287&hl=
pygor_merged Posted August 24, 2006 Posted August 24, 2006 I notice settings need changed in PHPmyadmin, err where do I find this ?
Klass Posted August 24, 2006 Posted August 24, 2006 ask your hostif you have cpanel access there should be a link to phpmyadminbut I would ask your host first.
pygor_merged Posted August 24, 2006 Posted August 24, 2006 ask your hostif you have cpanel access there should be a link to phpmyadminbut 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.
pygor_merged Posted August 24, 2006 Posted August 24, 2006 ask your hostif you have cpanel access there should be a link to phpmyadminbut I would ask your host first.I'm looking in cPanel but there is no sign of a phpmyadmin, Host is IPS.
Klass Posted August 24, 2006 Posted August 24, 2006 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
pygor_merged Posted August 24, 2006 Posted August 24, 2006 Thanks the link did the trick.Unfortunatly when trying to change the setting to "HEAP" this error message is shownErrorSQL 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:
_Diesel_ Posted August 24, 2006 Posted August 24, 2006 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:
Klass Posted August 24, 2006 Posted August 24, 2006 Thanks the link did the trick.Unfortunatly when trying to change the setting to "HEAP" this error message is shownErrorSQL 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
pygor_merged Posted August 24, 2006 Posted August 24, 2006 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.
Fast Lane! Posted August 24, 2006 Posted August 24, 2006 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.73Write ratio 0.04Read ratio 0.00-- Questions -----------------------------------------------------------Total 3.32M 22.16/sSlow 735 0.00/s %Total: 0.02 %DMS: 0.04DMS 1.93M 12.84/s 57.95-- Table Locks ---------------------------------------------------------Waited 31.20k 0.21/s %Total: 1.40Immediate 2.19M 14.62/s-- Tables --------------------------------------------------------------Open 512 of 512 %Cache: 100.00Opened 2.10k 0.01/s-- Connections ---------------------------------------------------------Max used 191 of 300 %Max: 63.67Total 265.96k 1.77/s-- Created Temp --------------------------------------------------------Disk table 4.09k 0.03/sTable 15.99k 0.11/sFile 6.43k 0.04/s
Digi Posted August 24, 2006 Posted August 24, 2006 You will lose full text searching though wont you? :/
Fast Lane! Posted August 24, 2006 Posted August 24, 2006 yeah I would lose that with InnodB... hard choice.
Digi Posted August 25, 2006 Posted August 25, 2006 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.
bfarber Posted August 25, 2006 Posted August 25, 2006 odd I thought brandon said to make that table innodb and another part of the thread said to make the sessions table HEAP/MemorySTOPYou 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.
pygor_merged Posted August 25, 2006 Posted August 25, 2006 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
Fast Lane! Posted August 25, 2006 Posted August 25, 2006 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: 18SELECT * 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: 29SELECT * 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: 31SELECT * 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: 15SELECT * 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: 15SELECT * 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: 0SELECT * FROM crm_topic_markers WHERE marker_forum_id=80 AND marker_member_id=21453;
Recommended Posts
Archived
This topic is now archived and is closed to further replies.