Issue information
-
#035773
-
Fixed
-
3.2.3
-
3.3.0 Beta 1
-
0 - None Assigned
Issue Confirmations
-
Yes (0)No (0)
0
Polls/Voters = voters table joining to million plus rows.
Posted by Alexia Smith on 08 February 2012 - 05:51 PM
The query below from function _generatePollOutput() is fairly obscene on large forums. While $this->settings['poll_allow_public'] prevents the poll output from generating the member information that same setting does not prevent the members table left join from being added which is half of the problem. The members table is only needed when public polls are allowed. Public polls have to be turned off on large sites due to the massive requirements needed to generate that much output.
To fix the issue change the build statement to have a ternary around the add_join.
SELECT v.*,m.* FROM ips_voters v LEFT JOIN ips_members m ON ( m.member_id=v.member_id ) WHERE v.tid=519098
To fix the issue change the build statement to have a ternary around the add_join.
$this->DB->build( array( 'select' => 'v.*', 'from' => array( 'voters' => 'v' ), 'where' => 'v.tid=' . $topicData['tid'], 'add_join' => ($this->settings['poll_allow_public'] ? array( array( 'select' => 'm.*', 'from' => array( 'members' => 'm' ), 'where' => 'm.member_id=v.member_id', 'type' => 'left' ) ) : null) ) );
Have you got some evidence to show this is a slow query? It's using indexes so I can't see why it would be terribly slow.
There's lots of places that join in the member table in much the same way.
There's lots of places that join in the member table in much the same way.
Matt, on 09 February 2012 - 10:21 AM, said:
Have you got some evidence to show this is a slow query? It's using indexes so I can't see why it would be terribly slow.There's lots of places that join in the member table in much the same way.
It is slow because of "lots of places that join in the member table". Lots of table contention creates these sort of slow downs. Having that join in one less place where it is not needed helps to overall speed up the software. I had to redact a lot of the information below, but this demonstrates where the table row contention even when using InnoDB can cause long query times.
| Id | User | Host | db | Command | Time | State | Info | xx | xxxx | xxxx | xx | Query | 39 | Sending data | SELECT v.*,m.* FROM voters v LEFT JOIN members m ON ( m.member_id=v.member_id ) WHERE v.tid=xxxxxx
Can you submit a ticket please? I'd like to investigate further. I get the feeling we're trying to cut calories by blowing the icing sugar from the cup cake.
Just closing this for the sake of organisation. Can reopen when ticket is received.
Matt, on 14 February 2012 - 03:53 AM, said:
Can you submit a ticket please? I'd like to investigate further. I get the feeling we're trying to cut calories by blowing the icing sugar from the cup cake.
I know submitting a ticket will come to, "Please give us access to your servers", which will never happen since VPN access is required to get into the secure areas.(AdminCP, database, etcetera.)
Example: minecraftforum.net - 45,576 users online right now. 112,444 peak users with a typical 70,000 average online. Take 10% of that average, 7,000, all hitting one topic with a poll on it. I will put it this way, the amount of traffic MCF receives dwarfs that of all the sites proudly displayed on IPS' home page. That is all those sites' traffic combined.
A little bit of icing dropped in the gears can cause massive database back ups. We have to get every last bit of performance out of IPB. So joining a 1,106,389 row members table against the 1,044,151 voters table when it is not needed is a very bad idea. Granted, having to join only 7,000 rows at a time, but those data sets are already really large to scan through.
It sounds like in summary that you are making a recommendation (this doesn't seem to be a bug really) that when displaying poll votes publicly is off (poll_allow_public=0) the members table does not need to be joined, and you have noticed an increase in performance when it is not joined.
That summarize it?
That summarize it?
bfarber, on 15 February 2012 - 02:54 PM, said:
It sounds like in summary that you are making a recommendation (this doesn't seem to be a bug really) that when displaying poll votes publicly is off (poll_allow_public=0) the members table does not need to be joined, and you have noticed an increase in performance when it is not joined.That summarize it?
That is the perfect summarization.
That's fair enough. Made the change.
Matt, on 16 February 2012 - 08:21 AM, said:
That's fair enough. Made the change.
Thanks guys, we appreciate it!
1 user(s) are reading this issue
0 members, 1 guests, 0 anonymous users
