Getting up to speed with MySQL4 and Threads6.2.1?

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,

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.

[]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.

I'm curious what the search queries look like in 6.4 if they differ at all from easlier versions.