Advertisement:

Author Topic: Table to large, strategies?  (Read 4011 times)

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,699
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: Table to large, strategies?
« Reply #20 on: May 05, 2019, 02:01:43 PM »
That's fine -- I backup all my databases regularly, but I swear I followed everything you suggested to a T. I even provided screenshots. Anyway, I don't mind hiring someone to do this, as I don't think I'm in the position to do this correctly either. Disappointing though, since it has worked flawlessly for several years

Online vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 21,495
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: Table to large, strategies?
« Reply #21 on: May 05, 2019, 02:20:05 PM »
Optimize your mysql config and or add more resources such as more ram, faster cpu. You want to be able to fit that whole table into memory.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Offline SpacePhoenix

  • Semi-Newbie
  • *
  • Posts: 90
Re: Table to large, strategies?
« Reply #22 on: May 05, 2019, 03:40:01 PM »
Have you looked to see if there's a 2.0.x mod that gives you the same functionality?

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,699
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: Table to large, strategies?
« Reply #23 on: May 05, 2019, 08:44:11 PM »
Optimize your mysql config and or add more resources such as more ram, faster cpu. You want to be able to fit that whole table into memory.

Sometimes no amount of optimization could remedy a bad query. I tried doing that, adding the following components to mysqld with limited improvement:

Code: [Select]
tmp_table_size = 256M
max_heap_table_size = 256M
innodb_buffer_pool_size = 1000M
innodb_stats_on_metadata = OFF

query_cache_type=0
query_cache_size=0

I think it's best to optimize the query. Could any of you recommend a coding genius to help me rewrite that function?

Check these numbers out, it's about to BLOW!
« Last Edit: May 05, 2019, 09:03:32 PM by Study Force »

Online vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 21,495
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: Table to large, strategies?
« Reply #24 on: May 05, 2019, 08:48:30 PM »
I do these as well
performance_schema=OFF
innodb_flush_log_at_trx_commit=2


The second is more important less disk usage.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,699
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: Table to large, strategies?
« Reply #25 on: May 05, 2019, 09:07:29 PM »
Unfortunately, no major improvement. Without the function running, the CPU hovers between 0% and 150%. With the function, it jumps to >300%. Remember that the function runs on every thread load, hence it's best to be optimized than anything... Thoughts on optimizing the function?

Offline SpacePhoenix

  • Semi-Newbie
  • *
  • Posts: 90
Re: Table to large, strategies?
« Reply #26 on: May 05, 2019, 09:35:49 PM »
What's the name of the mod that this query is a part of?

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,699
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: Table to large, strategies?
« Reply #27 on: May 05, 2019, 09:45:39 PM »
What's the name of the mod that this query is a part of?

Here it is: https://custom.simplemachines.org/mods/index.php?mod=189

Initially I thought it was really well-written, but now I'm second guessing that assertion.

Have you looked to see if there's a 2.0.x mod that gives you the same functionality?

I have it installed on another website running SMF 2.x, but given that community is a lot smaller ~700 000 threads, I see the problem appear here and there.

Offline SpacePhoenix

  • Semi-Newbie
  • *
  • Posts: 90
Re: Table to large, strategies?
« Reply #28 on: May 06, 2019, 05:13:54 AM »
Don't know what version's database this was intended for but might be worth investigating (it's in related_topics.php)

Code: [Select]
// Check that all indexes are in and correct
foreach ($table['indexes'] as $id => $index)
{
$exists = false;

foreach ($currentTable['indexes'] as $index2)
{
// Primary is special case
if ($index['type'] == 'primary' && $index2['type'] == 'primary')
{
$exists = true;

if ($index['columns'] !== $index2['columns'])
{
$smcFunc['db_remove_index']('{db_prefix}' . $table_name, 'primary');
$smcFunc['db_add_index']('{db_prefix}' . $table_name, $index);
}

break;
}
// Make sure index is correct
elseif (isset($index['name']) && isset($index2['name']) && $index['name'] == $index2['name'])
{
$exists = true;

// Need to be changed?
if ($index['type'] != $index2['type'] || $index['columns'] !== $index2['columns'])
{
$smcFunc['db_remove_index']('{db_prefix}' . $table_name, $index['name']);
$smcFunc['db_add_index']('{db_prefix}' . $table_name, $index);
}

break;
}
}

if (!$exists)
$smcFunc['db_add_index']('{db_prefix}' . $table_name, $index);
}

// Remove unnecassary indexes
foreach ($currentTable['indexes'] as $index)
{
$exists = false;

foreach ($table['indexes'] as $index2)
{
// Primary is special case
if ($index['type'] == 'primary' && $index2['type'] == 'primary')
$exists = true;
// Make sure index is correct
elseif (isset($index['name']) && isset($index2['name']) && $index['name'] == $index2['name'])
$exists = true;
}

if (!$exists)
{
if (isset($table['upgrade']['indexes']))
{
foreach ($table['upgrade']['indexes'] as $index2)
{
if ($index['type'] == 'primary' && $index2['type'] == 'primary' && $index['columns'] === $index2['columns'])
$smcFunc['db_remove_index']('{db_prefix}' . $table_name, 'primary');
elseif (isset($index['name']) && isset($index2['name']) && $index['name'] == $index2['name'] && $index['type'] == $index2['type'] && $index['columns'] === $index2['columns'])
$smcFunc['db_remove_index']('{db_prefix}' . $table_name, $index['name']);
elseif (!empty($db_show_debug))
$log[] = $table_name . ' has Unneeded index ' . var_dump($index);
}
}
elseif (!empty($db_show_debug))
$log[] = $table_name . ' has Unneeded index ' . var_dump($index);
}

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,699
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: Table to large, strategies?
« Reply #29 on: May 06, 2019, 08:34:08 AM »
That file I believe is ran once during installation or when you manually build the index. It has little or nothing to do with the query being run on every page load.

Offline SpacePhoenix

  • Semi-Newbie
  • *
  • Posts: 90
Re: Table to large, strategies?
« Reply #30 on: May 06, 2019, 09:21:11 AM »
Have you tried installing the newer version of the mod as the new version doesn't have any of the mysql_ calls that the version that you're using has?

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,699
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: Table to large, strategies?
« Reply #31 on: May 06, 2019, 09:52:55 AM »
Have you tried installing the newer version of the mod as the new version doesn't have any of the mysql_ calls that the version that you're using has?

As mentioned previously, I have the latest version installed on my 2.x website, "but given that community is a lot smaller ~700 000 threads, I see the problem appear here and there."

Unless you found a newer version elsewhere, that's the extent of what I've done.

Offline SpacePhoenix

  • Semi-Newbie
  • *
  • Posts: 90
Re: Table to large, strategies?
« Reply #32 on: May 06, 2019, 12:26:49 PM »

I created the non-unique index respecting the column order as mentioned earlier. See the attachment for details. Unfortunately it's still eating up my CPU >300% :(

Here's what the function looks like in its raw from:

Code: [Select]
function loadRelated($topic)
{
global $modSettings, $context, $db_prefix, $user_info;

$result = db_query("
SELECT IF(rt.id_topic_first = $topic, rt.id_topic_second, rt.id_topic_first) AS ID_TOPIC
FROM {$db_prefix}related_topics AS rt
JOIN {$db_prefix}topics AS t ON (t.ID_TOPIC = IF(rt.id_topic_first = $topic, rt.id_topic_second, rt.id_topic_first))
JOIN {$db_prefix}boards AS b ON (b.ID_BOARD = t.ID_BOARD)
WHERE (id_topic_first = $topic OR id_topic_second = $topic)
AND $user_info[query_see_board]
ORDER BY rt.score DESC
LIMIT $modSettings[relatedTopicsCount]", __FILE__, __LINE__);

if (mysql_num_rows($result) == 0)
return false;

$topics_ids = array();
while ($row = mysql_fetch_assoc($result))
$topics_ids[] = $row['ID_TOPIC'];

mysql_free_result($result);

return prepareTopicArray($topics_ids);
}

You'll notice that it's designed for SMF 1.x. Important to mention that on my 2.x community, I use the same mod except its adapted for 2.x; the code is identical and I get the same sort of choke up. I'm assuming the mod author never had >1 mil rows in mind when developing it.

How can we improve the query?
Have you tried installing the newer version of the mod as the new version doesn't have any of the mysql_ calls that the version that you're using has?

As mentioned previously, I have the latest version installed on my 2.x website, "but given that community is a lot smaller ~700 000 threads, I see the problem appear here and there."

Unless you found a newer version elsewhere, that's the extent of what I've done.

In that code snippet that you posted there's the use of some of the mysql_* functions. In the current version of the mod, them functions aren't used

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,699
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: Table to large, strategies?
« Reply #33 on: May 06, 2019, 01:10:06 PM »
SMF 2.x replaces that the 2.x version. I believe under the hood they're the same thing. It's the main query that's problematic.

Online shawnb61

  • Developer
  • Sophist Member
  • *
  • Posts: 1,333
    • sbulen on GitHub
Re: Table to large, strategies?
« Reply #34 on: May 06, 2019, 06:18:39 PM »
I'm pretty sure that query needs an index on id_topic_second.  It will be of type INDEX, pretty sure it's not unique. 

Not sure you tried that yet?   I did a quick test & it yielded a 10x improvement on that query. 

Do an explain plan on that query before & after adding an index there.  You want to see that ALL go away under "type"... 

Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,699
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: Table to large, strategies?
« Reply #35 on: May 06, 2019, 11:59:16 PM »
That did it!

@shawnb61: I *really* appreciate that, thank you. And to think I had to rewrite the query

If I notice anything wonky, I'll return...

Online shawnb61

  • Developer
  • Sophist Member
  • *
  • Posts: 1,333
    • sbulen on GitHub
Re: Table to large, strategies?
« Reply #36 on: May 07, 2019, 09:30:44 AM »
I'm sure much more could/should be done.  But I think this finding & discussion really belongs in the mod support thread, where other mod users can benefit & help.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline Study Force

  • SMF Hero
  • ******
  • Posts: 3,699
    • StudyForcePS on Facebook
    • @studyforceps on Twitter
Re: Table to large, strategies?
« Reply #37 on: May 07, 2019, 12:15:21 PM »
I agree, can a moderator merge it?

BTW, what else do you think can be done?

To add, I think this belongs in the big board discussion forum too

Online vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 21,495
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: Table to large, strategies?
« Reply #38 on: May 07, 2019, 12:48:59 PM »
Can you run and post back show create table smf_releated_topics;
With the change made now.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Online shawnb61

  • Developer
  • Sophist Member
  • *
  • Posts: 1,333
    • sbulen on GitHub
Re: Table to large, strategies?
« Reply #39 on: May 07, 2019, 01:33:24 PM »
I agree, can a moderator merge it?
...
To add, I think this belongs in the big board discussion forum too
Most of this thread doesn't really apply; I don't think we should spam those threads. 

I think a simple helpful post on that board with a link here would suffice. 


BTW, what else do you think can be done?

I'm really not familiar with this mod, so I really can't help much more.  The mod support thread is the best place. 

Tuning takes time, patience, knowledge, & most won't admit it, but more than a little trial & error...   I would suggest reading up more.  These two articles are reasonably good introductions, and will give you ideas where to look next:
https://code.tutsplus.com/tutorials/profiling-mysql-queries-with-phpmyadmin--net-12687
https://dzone.com/articles/how-to-optimize-mysql-queries-for-speed-and-perfor

Like most tech articles out there, you can argue lots of points, but as I said, they are reasonably good introductions.  Not too technical, & a good fly-over of what to look for. 

EDIT:  I'm going to flag this thread as solved for now...
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp