Previous Thread
Next Thread
Print Thread
Rate Thread
Joined: Sep 1999
Posts: 339
Kahuna
Kahuna
Offline
Joined: Sep 1999
Posts: 339
Hello Everyone,

I've recently moved to a double server setup. The webserver is an AMD Duron 1.3Ghz processor with 1 gig of Ram and a 40gig IDE harddrive.

The DBServer is an AMD Duron 1.3 Ghz with 512megs of Ram and a 36GB SCSI harddrive.

Forum has almost 400,000 posts and typically 90-180 users online.

The forum runs brilliantly fast in most situations. But has exposed a gaping hole in the shining armor. The speed of the search queries is unbearably slow. To top it off, several had been making very large temporary tables which were making the disk thrash and hampering the ability of the site to run on it's own.

A few notes: I've modified the tables to include a w3t_SearchPosts w3t_SearchUsers and w3t_SearchBoards to split the queries off of the main tables and keep the site running swift when long queries take place. This is obviously less than ideal.

Tonight we've disabled forum-wide searches (although we used to have them and they worked slowly but did not hamper the rest of the site) and I've worked with a partner to alter some of the queries to get them to work better with the keys.

My my.cnf:
Code
 # my.cnf file, basic configuration. <br /> <br /># Client configuration (not server!) <br />[client] <br />port = 3306 <br /># NOTE: Make sure this is correct, I have no goddamn clue where this <br /># file is on redhat. <br /># socket = /var/lib/mysql/mysql.sock <br />socket = /var/lib/mysql/mysql.sock <br />[mysqld] <br />datadir=/var/lib/mysql <br />socket=/var/lib/mysql/mysql.sock <br /> <br />[mysql.server] <br />user=mysql <br />basedir=/var/lib <br /> <br />[safe_mysqld] <br />err-log=/var/log/mysqld.log <br />pid-file=/var/run/mysqld/mysqld.pid <br /> <br />[mysqld] <br /># Adjust if this is not correct. <br />user = mysql <br />pid-file = /var/lib/mysql/server1.imcuniverse.com.pid <br />socket          = /var/lib/mysql/mysql.sock <br />port            = 3306 <br />#log = /var/log/mysql/mysql.log <br />log-slow-queries = /var/log/mysql/slow.log <br />basedir         = / <br />datadir         = /var/lib/mysql <br />tmpdir          = /tmp <br />language        = /usr/share/mysql/english <br /># Minor speedups and security. Make sure you're accessing it over the <br /># unix domain socket and not via network. <br />skip-locking <br />skip-innodb <br />#skip-networking <br /># Rest are variable options. Only one yo ushould touch is max_connections <br /># if there are connection troubles. <br />set-variable = join_buffer_size=262144 <br />set-variable = key_buffer_size=24M <br />set-variable = max_connections=150 <br />set-variable = table_cache=512 <br />set-variable = thread_cache_size=30 <br />set-variable = long_query_time=10 <br />set-variable = query_cache_size=24M <br />set-variable = query_cache_limit=10M <br />set-variable = tmp_table_size=128M <br /> <br />[mysqldump] <br />quick <br />set-variable    = max_allowed_packet=1M <br /> <br />[isamchk] <br />set-variable    = key_buffer=16M <br />


I am wondering if anyone has any ideas on ways to tweak the queries or suggestions on configuration changes. I will give you some examples of the slow queries:

Code
 # Time: 040214 23:49:39 <br /># User@Host: tractorb[tractorb] @ webserv1 [10.225.23.5] <br /># Query_time: 22  Lock_time: 0  Rows_sent: 2  Rows_examined: 45184 <br />SELECT STRAIGHT_JOIN t1.B_Number,t1.B_Main,t3.U_Username,t1.B_Subject,t1.B_Posted,t1.B_Board,t3.U_Color,t1.B_Reged,t1.B_Icon,t2.Bo_Title,t2.Bo_Read_Perm,t2.Bo_Cat,t1.B_Posterid,t1.B_Status,t1.B_AnonName <br />      FROM   w3t_SearchPosts AS t1, <br />             w3t_SearchBoards AS t2, <br />                                 w3t_SearchUsers AS t3 <br />      WHERE B_Approved = 'yes' <br />      AND   t1.B_Board = t2.Bo_Keyword <br />                AND   t1.B_Posterid = t3.U_Number <br />      AND   t1.B_Status <> 'M' <br /> <br />   AND ( (B_Body Like '%grantmo%')OR (B_Subject LIKE '%grantmo%') ) AND B_Board = 'off' <br />ORDER BY B_Posted DESC <br /> <br />LIMIT 26; 


Code
 # Time: 040214 23:34:10 <br /># User@Host: tractorb[tractorb] @ webserv1 [10.225.23.5] <br /># Query_time: 11  Lock_time: 0  Rows_sent: 26  Rows_examined: 32924 <br />SELECT STRAIGHT_JOIN t1.B_Number,t1.B_Main,t3.U_Username,t1.B_Subject,t1.B_Posted,t1.B_Board,t3.U_Color,t1.B_Reged,t1.B_Icon,t2.Bo_Title,t2.Bo_Read_Perm,t2.Bo_Cat,t1.B_Posterid,t1.B_Status,t1.B_AnonName <br />      FROM   w3t_SearchPosts AS t1, <br />             w3t_SearchBoards AS t2, <br />                                 w3t_SearchUsers AS t3 <br />      WHERE B_Approved = 'yes' <br />      AND   t1.B_Board = t2.Bo_Keyword <br />                AND   t1.B_Posterid = t3.U_Number <br />      AND   t1.B_Status <> 'M' <br /> <br />   AND ( (B_Body Like '%the%')OR (B_Subject LIKE '%the%') ) AND B_Board = 'rural' <br />ORDER BY B_Posted DESC <br /> <br />LIMIT 26; 


And lastly, an example of the slow queries before turning off all-forum-searching and adding the STRAIGHT_JOIN:

Code
 # Time: 040214 23:05:51 <br /># User@Host: root[root] @ localhost [] <br /># Query_time: 51  Lock_time: 0  Rows_sent: 26  Rows_examined: 374195 <br />SELECT STRAIGHT_JOIN t1.B_Number,t1.B_Main,t3.U_Username,t1.B_Subject,t1.B_Posted,t1.B_Board,t3.U_Color,t1.B_Reged,t1.B_Icon,t2.Bo_Title,t2.Bo_Read_Perm,t2.Bo_Cat,t1.B_Posterid,t1.B_Status,t1.B_AnonName <br />      FROM   w3t_SearchPosts AS t1, <br />             w3t_SearchBoards AS t2, <br />                                 w3t_SearchUsers AS t3 <br />      WHERE B_Approved = 'yes' <br />      AND   t1.B_Board = t2.Bo_Keyword <br />                AND   t1.B_Posterid = t3.U_Number <br />      AND   t1.B_Status <> 'M' <br />   AND ( (B_Body Like '%kubota%')OR (B_Subject LIKE '%kubota%') ) <br />AND B_Posted > 1045270355 <br />AND ( (B_Board = 'buykubota') OR (B_Board = 'owning') OR (B_Board = 'implement') OR (B_Board = 'off') OR (B_Board = 'rural') OR (B_Board = 'buyjd') OR (B_Board = 'jdown') OR (B_Board = 'buynh') OR (B_Board = 'nhown') OR (B_Board = 'lawn') OR (B_Board = 'construction') OR (B_Board = 'support') OR (B_Board = 'other') OR (B_Board = 'news') OR (B_Board = 'greym') OR (B_Board = 'oil') OR (B_Board = 'ag') OR (B_Board = 'photos') OR (B_Board = 'projects') OR (B_Board = 'safety') OR (B_Board = 'custom') OR (B_Board = 'book') OR (B_Board = 'caseih') OR (B_Board = 'cubcadet') OR (B_Board = 'massey') OR (B_Board = 'kioti') OR (B_Board = 'testing') OR (B_Board = 'vintage') OR (B_Board = 'china') OR (B_Board = 'genbuy') OR (B_Board = 'genoperating') OR (B_Board = 'parts') OR (B_Board = 'yanmar') OR (B_Board = 'moderated') OR (B_Board = 'moderators') OR (B_Board = 'mitsubishi') OR (B_Board = 'buildit') OR (B_Board = 'powertrac') OR (B_Board = 'iseki') OR (B_Board = 'mahindra') OR (B_Board = 'century') OR (B_Board = 'atv') ) <br />ORDER BY B_Posted DESC <br />LIMIT 26; <br /> 


As you can see from the rows examined... it is reading a lot of rows, and limiting it has eliminated MOST (but not all, unfortunately) of the temporary table creations.

What I am looking for is is advice on how to get better performance out of this setup.

Further files, stats, and the like can be provided if you need it. But I hope I've provided enough information.

Thanks,


Muhammad
Sponsored Links
Joined: Jan 2000
Posts: 5,073
Admin Emeritus
Admin Emeritus
Joined: Jan 2000
Posts: 5,073
You don't have nearly enough server for that much database...

Get a new box. Athlon or P4 of your choice, 2.0 GHz minimum, at LEAST a gig and a half of memory.


UBB.classic: Love it or hate it, it was mine.
Joined: Sep 1999
Posts: 339
Kahuna
Kahuna
Offline
Joined: Sep 1999
Posts: 339
[]Charles_Capps said:
You don't have nearly enough server for that much database...

Get a new box. Athlon or P4 of your choice, 2.0 GHz minimum, at LEAST a gig and a half of memory. [/]

As I said the RAM should be sufficient seeing as other sites with more posts (and simultaneous users) run on less ram, and this is a dedicated MySQL server.

Since posting we've resolved the issue with the individual forum searches and are working on a fix for all forums.


Muhammad
Joined: Mar 2003
Posts: 215
Junior Member
Junior Member
Offline
Joined: Mar 2003
Posts: 215
I'm curious what the search queries look like in 6.4 if they differ at all from easlier versions.


Link Copied to Clipboard
Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with and recommend the following Web Hosts:
Stable Host
bluehost
InterServer
Visit us on Facebook
Member Spotlight
Posts: 70
Joined: January 2007
Forum Statistics
Forums63
Topics37,573
Posts293,925
Members13,849
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,833
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 82
Gizmo 20
Brett 7
Morgan 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2024 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20221218)