News:

Wondering if this will always be free?  See why free is better.

Main Menu

Table to large, strategies?

Started by Biology Forums, May 04, 2019, 11:23:56 PM

Previous topic - Next topic

Biology Forums

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

vbgamer45

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

SpacePhoenix

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

Biology Forums

#23
Quote from: vbgamer45 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.

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

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!

vbgamer45

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

Biology Forums

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?

SpacePhoenix

What's the name of the mod that this query is a part of?

Biology Forums

Quote from: SpacePhoenix on May 05, 2019, 09:35:49 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.

Quote from: SpacePhoenix 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?

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.

SpacePhoenix

Don't know what version's database this was intended for but might be worth investigating (it's in related_topics.php)

// 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);
}

Biology Forums

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.

SpacePhoenix

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?

Biology Forums

Quote from: SpacePhoenix 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?

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.

SpacePhoenix


Quote from: Study Force on May 05, 2019, 01:38:09 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:

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?
Quote from: Study Force on May 06, 2019, 09:52:55 AM
Quote from: SpacePhoenix 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?

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

Biology Forums

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.

shawnb61

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

Biology Forums

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

shawnb61

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

Biology Forums

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

vbgamer45

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

shawnb61

Quote from: Study Force on May 07, 2019, 12:15:21 PMI 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. 


Quote from: Study Force on May 07, 2019, 12:15:21 PMBTW, 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

Advertisement: