Author Topic: Slow queries, need help badly {Urgent!!}{Live627 This is Your Code I need Help!} (Read 6124 times)

Groovystar · « **on:** August 17, 2012, 06:39:56 PM »

I am getting slow queries on my site:

Quote

| 40728076 | warrior_acorn | 192.168.1.1:60201 | warrior_smf | Query | 4858 | Sending data | SELECT COUNT(mem.id_member)
FROM smf_members AS mem
LEFT JOIN smf_subaccounts AS sub ON (sub.id |
| 40728689 | warrior_acorn | 192.168.1.1:34790 | warrior_smf | Query | 4833 | Sending data | SELECT COUNT(mem.id_member)
FROM smf_members AS mem
LEFT JOIN smf_subaccounts AS sub ON (sub.id |
| 40728800 | warrior_acorn | 192.168.1.1:35292 | warrior_smf | Query | 4827 | Sending data | SELECT COUNT(mem.id_member)

This seems to be problems with a mod, the subaccount mod that was custom modified by Live.

We have had problems with this mod for a long time. Would anyone be able to give it a little tweak or show me how I could?

live627 · « **Reply #1 on:** August 17, 2012, 08:00:38 PM »

Which file?

Groovystar · « **Reply #2 on:** August 17, 2012, 08:38:49 PM »

I am not sure, just know that this slowness required a restart of mySQL. I found these in the Cpanel error area earlier.

Subaccounts have often caused slow queries on my site, they are extremely popular hit with the members so they get heavily used.

live627 · « **Reply #3 on:** August 17, 2012, 09:54:44 PM »

Yep, yep, could be near a million 'subaccounts.

Do the errors include the URL? That, alone, would tell me the file.

Groovystar · « **Reply #4 on:** August 22, 2012, 06:59:45 PM »

Unfortunately they do not.

This has happened again tonight. Site crashed, slow queries, same ones.

Okay--Would it help to first list all the files that are involved with subaccounts?

We can then at least narrow it.

pixelated · « **Reply #5 on:** August 22, 2012, 07:28:17 PM »

Hey Live,

I'm currently doing quite a bit of programming for Groovystar, if it helps, I can probably help you get some information. Just let me know what you need and what area it might be in.

~Pixelated

Groovystar · « **Reply #6 on:** August 23, 2012, 07:46:45 PM »

Really need some help here. The problem is happening just about every day now.

Groovystar · « **Reply #7 on:** August 24, 2012, 05:40:50 PM »

live627 · « **Reply #8 on:** August 25, 2012, 01:54:10 AM »

Quote

Okay--Would it help to first list all the files that are involved with subaccounts?

We can then at least narrow it.

Profile-Trade.php
Profile-SubGroups.php
Profile-Parent.php
Profile-Money.php

Those are the three files that have that particular query snippet.

live627 · « **Reply #9 on:** August 25, 2012, 02:36:04 AM »

Wow... I wrote some really bad queries. I have an idea to change them from creating some Cartesian coordinate to a known subset. In laymen's terms, they were probably evaluating thousands of members times the subaccount total... possibly millions of rows! The functions for loading members already have the subs' IDs loaded, so I'll use that. Known subset is from algebraic maths - {1,2,4,8} - same principle applies in SQL. Now, it only evaluates you and your subaccounts - 30-odd for you, 3 for me.

Please try My Money in the profile. That section uses the updated queries. The others haven't been changed yet because I'm waiting on feedback.

pixelated · « **Reply #10 on:** August 25, 2012, 09:02:12 AM »

Hey Live,

Thanks for getting back to us!

When trying to access "My Money" the following error is shown.

Whoa! We got a goof here.
Database error, given array of integer values is empty. (subaccounts)

~Pixelated

live627 · « **Reply #11 on:** August 25, 2012, 12:52:11 PM »

Hmm, it worked for me. Oh, I know what the problem is! You tested it on an account that has no subs.

pixelated · « **Reply #12 on:** August 25, 2012, 01:04:36 PM »

I sure did.

Does whatever that function is have to run, or can you only do it if a person has a sub account?

~Pixelated

Groovystar · « **Reply #13 on:** August 25, 2012, 02:57:55 PM »

Thank you so much for getting back to me. We're still having the slow queries almost every day now bringing the site down. So it's moved into "fix it asap" territory.

I suspect that the subaccount mod works fine on smaller forums but is not built to handle a situation where you have literally tens of thousands of members using them.

Adding info here: Quoting posts seems to take a really, really long time to get to the quoting page after you press the quote button. It takes forever.

Groovystar · « **Reply #14 on:** August 26, 2012, 09:16:05 PM »

Groovystar · « **Reply #15 on:** August 27, 2012, 09:30:21 PM »

BUMP. I really need help with this, it's urgent.

Groovystar · « **Reply #16 on:** August 28, 2012, 04:15:55 PM »

please!!! Live, I need your help, this is your code and so you are the one who would know it best. It is causing our site to crash, please help.

Groovystar · « **Reply #17 on:** August 29, 2012, 03:44:48 PM »

Please help.

butch2k · « **Reply #18 on:** August 30, 2012, 05:14:57 AM »

Please capture the full query and run it against the database using EXPLAIN and post the results here.

butch2k · « **Reply #19 on:** August 30, 2012, 05:24:26 AM »

Please run the following SQL:
CREATE INDEX idx_id_member ON smf_subaccounts (id_member)

News:

Author Topic: Slow queries, need help badly {Urgent!!}{Live627 This is Your Code I need Help!} (Read 6124 times)