SecondSight Posted September 29, 2010 Share Posted September 29, 2010 Hello ! :) I've been using IPB since 2003 and now my database is 8 gigabytes big. My server is powerful so I now don't have too many problems having my board running, but sometimes, I get these errors :Error: 1317 - Query execution was interrupted Error: 2006 - MySQL server has gone away I was told that this was due to the size of my database, that it takes time to search the tables... I don't know if this is true and if there is a solution. I'm just asking if it will be possible in the future to use mutiple databases or any solution which would avoid having one huge database causing problems. Thank you ! :) Link to comment Share on other sites More sharing options...
iozay Posted September 29, 2010 Share Posted September 29, 2010 Have you tried this Admin -> Support -> SQL Toolbox Sometimes it can be handy to repear/optimize it as it may prevent certain errors. You can also use the toolbox to analyse/check your database. Link to comment Share on other sites More sharing options...
SecondSight Posted September 29, 2010 Author Share Posted September 29, 2010 Hello ! :) My database is optimized and checked regularly. I have installed XCache, Memcached, and have had the server optimized. But this is not the topic. :) What I would like to see is a system which would avoid having one big database... Link to comment Share on other sites More sharing options...
iozay Posted September 30, 2010 Share Posted September 30, 2010 That should be to prune old topics/posts(But in this case you will lose some data). Link to comment Share on other sites More sharing options...
Gary. Posted October 6, 2010 Share Posted October 6, 2010 I've been using IPB since 2003 and now my database is 8 gigabytes big. Well holy c**p ! That is some database LOL. I'm sure you can reduce this with some cleanups such as templates and orphaned avaters, I have a client which I transfered from a hosting provider on me and he had an IPB databse just over 862MB We moved, Installed as we offer, Yet within his acp panel we done a system cleanup and he actually have over 400MB of unused templates and css. Then we done other things such as cleanup, removed old mods idling the database and in the end we got this down to an average 170mb. With no supprise he saw a huge increase with speeds within his community so you be quite supprised what can be actually cleaned up. Link to comment Share on other sites More sharing options...
SecondSight Posted October 7, 2010 Author Share Posted October 7, 2010 Hello ! :) Thank you ! I will have a look at this soon, when I'm not working. :) Anyway, is it not possible for IPB to have for instance the ibf_posts table splitted into many posts tables ? Is it not a good idea ? Thak you ! :) Link to comment Share on other sites More sharing options...
yacenty Posted October 7, 2010 Share Posted October 7, 2010 would be nice to have a possibilty to place db on 2 servers and do Mysql mirroring but one guy that was hired to speed up my service told me IPB db is not prepared for such mechanism Link to comment Share on other sites More sharing options...
Aurélio "Baboo" Posted October 8, 2010 Share Posted October 8, 2010 Our database also has ~8GB (~3 million posts by ~500,000 registered users) and we use SQL Server 2008 R2 without any performance problem. The key is having LOTS or RAM: our server has 72GB RAM and it can handle >20,000 concurrent users. Link to comment Share on other sites More sharing options...
SecondSight Posted October 8, 2010 Author Share Posted October 8, 2010 Well, I haven't noticed performance problems (the server load is between 0.5 and 1.2)... I have 16 GB RAM only... Is it not sufficient ? I'm using MySQL, should I change to something else ? Thank you ! :) Link to comment Share on other sites More sharing options...
Mat B Posted October 9, 2010 Share Posted October 9, 2010 You could partition your tables (this is a new feature in MySQL 5.1). You can see the documentation on this at http://dev.mysql.com/doc/refman/5.1/en/partitioning-types.html MSSQL users can also use partitioning. The advantage of this is that although you still only have one table, the DBMS will not need to scan the entire table to get data. In fact, they can usually optimise it down to one single partition, resulting in vastly improved query times. Link to comment Share on other sites More sharing options...
yacenty Posted October 9, 2010 Share Posted October 9, 2010 Mat could You give some more info how to use partitionig to get better performance? Thanks in advance BR YacentY Link to comment Share on other sites More sharing options...
Mat B Posted October 12, 2010 Share Posted October 12, 2010 If you're using MSSQL, yes. I'm not as familiar with MySQL so I would simply direct you to the documentation I linked in my previous post. Link to comment Share on other sites More sharing options...
yacenty Posted October 12, 2010 Share Posted October 12, 2010 Is there anybody from IPS who could help us with MySQL? Regards YacentY Link to comment Share on other sites More sharing options...
yacenty Posted October 12, 2010 Share Posted October 12, 2010 offical IPS statement in this topic: Hello, Unfortunately this kind of request falls beyond the scope of our standard support service offering. I can only suggest you to wait for someone to reply you on our forum or contact your host if they offer help with such kind of setup. so what for do You tell about such feature which is not supported? Link to comment Share on other sites More sharing options...
teraßyte Posted October 12, 2010 Share Posted October 12, 2010 It is not that IP.Board can't support it, simply we don't offer help in setting up such kind of setup since it is something related to your server(s) itself rather than IP.Board. Link to comment Share on other sites More sharing options...
yacenty Posted October 12, 2010 Share Posted October 12, 2010 I'm not requesting setting uop my server. I'm expecting some general info similiar to this one about MSSQL. If I have general info I can decide to enable this partitioning or not, but anyway without such info we can not :( Link to comment Share on other sites More sharing options...
Mat B Posted October 13, 2010 Share Posted October 13, 2010 You need to follow the link I provided to the MySQL partitioning documentation. Any MSSQL documentation, for reference, would be on MSDN. Link to comment Share on other sites More sharing options...
blair Posted October 14, 2010 Share Posted October 14, 2010 I really don't think database size is the issue, I'd recommend focusing your efforts elsewhere. I just checked, I have an IPB / MySQL database that's 15.4GB. The posts table alone is 11GB. It's running great on a single server with 4GB of RAM. All tables are MyISAM except 'sessions', is memory (formerly called heap). IPB 3.x has done great things "out-of-the-box" to support larger sites. MySQL scales very well. There really isn't much work that needs to be done. However, if you have a forum approaching 1 million posts, I'd consider Sphinx search a must have (replace full text). Next, optimize your MySQL configuration (my.cnf), there are some good posts in this forum. Finally, log your slow queries so that you can identify which queries are responsible for the errors. Only then can you fix them. Link to comment Share on other sites More sharing options...
SecondSight Posted October 14, 2010 Author Share Posted October 14, 2010 Hello ! :) So the problem comes from my server configuration.... At the moment, I always have a low server load. Here is what MysqlTuner returns :[--] Up for: 4d 17h 12m 51s (31M q [77.785 qps], 1M conn, TX: 513B, RX: 49B) [--] Reads / Writes: 45% / 55% [--] Total buffers: 5.8G global + 38.2M per thread (200 max threads) [OK] Maximum possible memory usage: 13.2G (84% of installed RAM) [OK] Slow queries: 0% (31/31M) [OK] Highest usage of available connections: 49% (99/200) [OK] Key buffer size / total MyISAM indexes: 4.0G/1.9G [OK] Key buffer hit rate: 99.9% (2B cached / 2M reads) [OK] Query cache efficiency: 48.9% (8M cached / 17M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 244K sorts) [OK] Temporary tables created on disk: 16% (4K on disk / 27K total) [OK] Thread cache hit rate: 99% (99 created / 1M connections) [OK] Table cache hit rate: 35% (898 open / 2K opened) [OK] Open file limit used: 14% (1K/8K) [OK] Table locks acquired immediately: 95% (32M immediate / 34M locks) What do you think of it ? The Table cache hit rate is low. I have more than 4 500 000 posts and Sphinx is installed. XCache and Memcached are installed too and seem to be running fine. The errors I get seem to be always related to a search : Date: Wed, 13 Oct 2010 13:10:50 +0000 Error: 2006 - MySQL server has gone away So it may be possible Sphinx isn't working well ? Anyway, I'm going to delete posts to reduce the size of the database and play around with the my.cnf values. Thank you for your help ! :) Link to comment Share on other sites More sharing options...
blair Posted October 15, 2010 Share Posted October 15, 2010 The errors I get seem to be always related to a search : So it may be possible Sphinx isn't working well ? Looks like you answered your own question. Sphinx isn't installed correctly. Are you running 3.x? I wouldn't recommend deleting any posts, but that's your call. Link to comment Share on other sites More sharing options...
SecondSight Posted October 15, 2010 Author Share Posted October 15, 2010 Yes, I'm using IPB 3.1.2. :) Link to comment Share on other sites More sharing options...
thompsone Posted October 16, 2010 Share Posted October 16, 2010 Sphinx has error logs and warning logs you should check those if you suspect sphinx to be an issue.[--] Total buffers: 5.8G global + 38.2M per thread (200 max threads) Tuning a LAMP box isn't a simple "here" are the steps with the values you need, as I'm sure you're aware. What kind of apache load are you seeing, what's allocated per apache thread, how many apache servers, forks, and children are you allowing that combined with your mysql tuning will get you started but that's a task best left to system administrators like yourself or to someone you hire for that purpose. Personally if MySQL is becoming a resource hog I'd cluster up and dump apache and php to a less powerful machine and give the resources to the more critical applications. Error: 2006 - MySQL server has gone away MySQL may not be the problem at all. This error message could be generated from mysqld being restarted which doesn't appear to be the case since your tuner reported an uptime of 4days +. I'd be likely to suspect the cron job in the ACP that kills mysql processes to be the culprit. The mysql kill processes task is located in the scheduler in the ACP, just switch that task off and see if your problem goes away. That was at least my fix for this same "where did my mysql go" problem. Personally I think it's something that needs to be removed permanently. There is more running on my machines than just Invision Software and to have IPB just close mysql processes unilaterally across the daemon could be better thought out, IMO. Link to comment Share on other sites More sharing options...
-Seven- Posted October 17, 2010 Share Posted October 17, 2010 if sphinx is working fine you wont get Mysql search errors so check what is theissue with sphinx set up in IPB Link to comment Share on other sites More sharing options...
Bono Posted October 17, 2010 Share Posted October 17, 2010 Hello ! :) I've been using IPB since 2003 and now my database is 8 gigabytes big. My server is powerful so I now don't have too many problems having my board running, but sometimes, I get these errors : [size="2"]Error: 1317 - Query execution was interrupted Error: 2006 - MySQL server has gone away I was told that this was due to the size of my database, that it takes time to search the tables... I don't know if this is true and if there is a solution. I'm just asking if it will be possible in the future to use mutiple databases or any solution which would avoid having one huge database causing problems. Thank you ! :) [/size] You are in luck, your problem is not hard to fix. First start logging slow queries Second message MySQL server has gone away happens when users sets "wait_timeout" value in config too low and mysql doesn't have time to execute that command and it interrupts that operation. So just raise limit and it should be fine, I got that value set to 28800. Link to comment Share on other sites More sharing options...
SecondSight Posted October 31, 2010 Author Share Posted October 31, 2010 28800 seems too big, isn't it ? I've tried 900 and it didn't change anything. Here are some of the errors I got last friday:Date: Fri, 29 Oct 2010 15:45:50 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: INSERT INTO ibf_cache_store (`cs_key`,`cs_value`) VALUES('mail_processing',0) ON DUPLICATE KEY UPDATE cs_key=VALUES(cs_key),cs_value=VALUES(cs_value) Date: Fri, 29 Oct 2010 15:51:34 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: SELECT * FROM ibf_attachments WHERE attach_rel_module='post' AND ( attach_rel_id IN (813599,793228,793063,747075,716783,542968,537537,495129,479756,474485,379347) ) Date: Fri, 29 Oct 2010 15:51:34 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: INSERT INTO ibf_cache_store (`cs_key`,`cs_value`) VALUES('mail_processing',0) ON DUPLICATE KEY UPDATE cs_key=VALUES(cs_key),cs_value=VALUES(cs_value) Date: Fri, 29 Oct 2010 15:54:22 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: SELECT * FROM ibf_attachments WHERE attach_rel_module='post' AND ( attach_rel_id IN (472964,239582,237577,29148) ) Date: Fri, 29 Oct 2010 15:54:22 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: INSERT INTO ibf_cache_store (`cs_key`,`cs_value`) VALUES('mail_processing',0) ON DUPLICATE KEY UPDATE cs_key=VALUES(cs_key),cs_value=VALUES(cs_value) Date: Fri, 29 Oct 2010 15:54:22 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: SELECT * FROM ibf_attachments WHERE attach_rel_module='post' AND ( attach_rel_id IN (813599,793228,793063,747075,716783,542968,537537,495129,479756,474485,379347) ) Date: Fri, 29 Oct 2010 15:54:22 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: INSERT INTO ibf_cache_store (`cs_key`,`cs_value`) VALUES('mail_processing',0) ON DUPLICATE KEY UPDATE cs_key=VALUES(cs_key),cs_value=VALUES(cs_value) Date: Fri, 29 Oct 2010 15:54:22 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: SELECT * FROM ibf_attachments WHERE attach_rel_module='post' AND ( attach_rel_id IN (1193254,1049854,980321,769481) ) Date: Fri, 29 Oct 2010 15:54:22 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: INSERT INTO ibf_cache_store (`cs_key`,`cs_value`) VALUES('mail_processing',0) ON DUPLICATE KEY UPDATE cs_key=VALUES(cs_key),cs_value=VALUES(cs_value) ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Date: Fri, 29 Oct 2010 15:54:23 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: SELECT * FROM ibf_attachments WHERE attach_rel_module='post' AND ( attach_rel_id IN (1969181,1918057,1645458,1642666,1553759,1391748,1317753,1266661,984409,780797) ) Date: Fri, 29 Oct 2010 15:54:23 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: INSERT INTO ibf_cache_store (`cs_key`,`cs_value`) VALUES('mail_processing',0) ON DUPLICATE KEY UPDATE cs_key=VALUES(cs_key),cs_value=VALUES(cs_value) Date: Fri, 29 Oct 2010 15:54:23 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: SELECT * FROM ibf_attachments WHERE attach_rel_module='post' AND ( attach_rel_id IN (2632872,2387965,2362456,2123621,1974741,1671924) ) Date: Fri, 29 Oct 2010 15:54:23 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: INSERT INTO ibf_cache_store (`cs_key`,`cs_value`) VALUES('mail_processing',0) ON DUPLICATE KEY UPDATE cs_key=VALUES(cs_key),cs_value=VALUES(cs_value) Date: Fri, 29 Oct 2010 15:54:23 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: SELECT * FROM ibf_attachments WHERE attach_rel_module='post' AND ( attach_rel_id IN (3249745,3078601,2908548,2762372,2436689,1686268) ) Date: Fri, 29 Oct 2010 15:54:23 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: INSERT INTO ibf_cache_store (`cs_key`,`cs_value`) VALUES('mail_processing',0) ON DUPLICATE KEY UPDATE cs_key=VALUES(cs_key),cs_value=VALUES(cs_value) Date: Fri, 29 Oct 2010 19:39:22 +0000 Error: 1317 - Query execution was interrupted ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: SELECT p.*,t.*,m.member_id, m.members_display_name, m.members_seo_name,cca.*,ccb.cache_content as cache_content_sig, ccb.cache_updated as cache_updated_sig FROM ibf_posts p LEFT JOIN ibf_topics t ON ( t.tid=p.topic_id ) LEFT JOIN ibf_members m ON ( m.member_id=p.author_id ) LEFT JOIN ibf_content_cache_posts cca ON ( cca.cache_content_id=p.pid ) LEFT JOIN ibf_content_cache_sigs ccb ON ( ccb.cache_content_id=p.author_id ) WHERE p.pid IN( 4732594,4747464,127340,4694082,4830935,4824185,4824015,4814011,4811895,4715716,4788570,4773915,3969819,4664360,4759162,4760000,4747309,4732078,3167733,4737619,4721479,4721205,4611389,4682314,4676080) Date: Fri, 29 Oct 2010 19:39:22 +0000 Error: 2006 - MySQL server has gone away ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: INSERT INTO ibf_cache_store (`cs_key`,`cs_value`) VALUES('mail_processing',0) ON DUPLICATE KEY UPDATE cs_key=VALUES(cs_key),cs_value=VALUES(cs_value) Since the I have MySQL server has one away errors at different times, I think my board itself has nothing to do with it. I alos had a look a the Sphinx logs and in fact it's doing very well... The slow queries log file for the same day is here :slowquerieslog.txt Can you tell me what you think of it ? Thank you ! Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.