[SOLVED]Need PHP/MYSQL expert to fix my Bitcoin faucet

[ahmedjadoon]

Hello! I have received yet another complaint from my host

Ahmed,
My website is dailyfreebits.com
I have been monitoring the Thunder server and I am still seeing a huge problem with your site using upwards of 300% cpu from your mysql processes. So I need you to hire a developer and have them fix this once and for all. It seems the changes made last time have not resolved the issues in the long run.

I am letting you know ahead of time so we can avoid any service interruption. Please keep us updated on what is being done to correct this.

The last time was in November last year when adding indexes in DB worked. https://bitcointalk.org/index.php?topic=875971.msg9689792#msg9689792

My site uses this script https://gitorious.org/elbandi/minifaucet/ .Indexes already added so suggest another way to fix.

I'm on a Semi-dedicated hosting already https://www.monstermegs.com/enterprise-hosting/ so don't suggest upgrading hosting. If someone could fix this let me know.
I can pay via BTC/Paypal.Let me know the price range for fixing!

[1715167435]

1715167435

[michaeladair]

Checking this when I get home.

[Vortex20000]

I'll quote you .08-9 and a timeframe of a week or so. Busy with many, many projects. PM if needed.

[ahmedjadoon]

Checking this when I get home.

Waiting

[michaeladair]

Checking this when I get home.

Waiting

lol I messaged you bout it...

[icanscript]

I'l take a look.

Lots of PHP and MySQL experience, I will get it fixed and wont charge.

[ahmedjadoon]

Checking this when I get home.

Waiting

lol I messaged you bout it...

I never received any PM from you. Can yoh resend?

[icanscript]

I have just had a quick scan of the code, I don't think there is anything broken as such, just very cumbersome code for what needs to be done and sometimes a roundabout way of doing it.

Firstly you don't want to be performing the following on shared hosting for every user that connects.

Code:

$sql = "SELECT COUNT(*) AS num_addresses, MAX(balance) AS max_balance, SUM(balance) as sum_balance, ";
            $sql .= "MAX(totalbalance) as max_totalbalance, SUM(totalbalance) as sum_totalbalance ";
            $sql .= "FROM balances WHERE email <> 'SERVERBALANCE'";

The math should be done in PHP to take some of the load on the SQL Server.

Also there is 12 calls to the SQL Server in the index.php file alone. let alone any external functions that are called from core.php.

I think the best fix would be to change software or upgrade your hardware.

[ahmedjadoon]

I have just had a quick scan of the code, I don't think there is anything broken as such, just very cumbersome code for what needs to be done and sometimes a roundabout way of doing it.

Firstly you don't want to be performing the following on shared hosting for every user that connects.

Code:

$sql = "SELECT COUNT(*) AS num_addresses, MAX(balance) AS max_balance, SUM(balance) as sum_balance, ";
            $sql .= "MAX(totalbalance) as max_totalbalance, SUM(totalbalance) as sum_totalbalance ";
            $sql .= "FROM balances WHERE email <> 'SERVERBALANCE'";

The math should be done in PHP to take some of the load on the SQL Server.

Also there is 12 calls to the SQL Server in the index.php file alone. let alone any external functions that are called from core.php.

I think the best fix would be to change software or upgrade your hardware.

I am using this script becausr it has a referral system. I also have a custom theme integrated with this theme.I think it will be a lot of work to migrate to new script.
Can you fix this script?

[PotatoPie]

I have just had a quick scan of the code, I don't think there is anything broken as such, just very cumbersome code for what needs to be done and sometimes a roundabout way of doing it.

Firstly you don't want to be performing the following on shared hosting for every user that connects.

Code:

$sql = "SELECT COUNT(*) AS num_addresses, MAX(balance) AS max_balance, SUM(balance) as sum_balance, ";
            $sql .= "MAX(totalbalance) as max_totalbalance, SUM(totalbalance) as sum_totalbalance ";
            $sql .= "FROM balances WHERE email <> 'SERVERBALANCE'";

The math should be done in PHP to take some of the load on the SQL Server.

Also there is 12 calls to the SQL Server in the index.php file alone. let alone any external functions that are called from core.php.

I think the best fix would be to change software or upgrade your hardware.

Do you even SQL bro ? I'll explain what this SQL statement is doing and why you're not correct in saying that this should be done PHP side. This SQL statement is getting
1. The count of all rows in balances (so the amount of users).
2. The maximum balance of any user (so the person with the most $$). To do this one PHP sided, you'd have to retrieve the whole balance row from the table with all of the data and then do it PHP sided. This would use more CPU than the SQL statement would.
3. The sum of the balances is just all of the balances in the table added together. This is the same as above and doing it using MySQL is far less resource intensive then getting the whole table and doing it with PHP.

Your statement about "12 calls to the SQL Server in the index.php" isn't necessarily an issue. You'll find that a lot of PHP files have quite a few more queries.

@OP: Try get information such as what pages are causing this issue with overusage of CPU from your host. I can try later on a VPS and see what the major issue is for you if you want. Are there any cron scripts running by any chance (haven't really looked at the code).

[ahmedjadoon]

Do you even SQL bro ? I'll explain what this SQL statement is doing and why you're not correct in saying that this should be done PHP side. This SQL statement is getting
1. The count of all rows in balances (so the amount of users).
2. The maximum balance of any user (so the person with the most $$). To do this one PHP sided, you'd have to retrieve the whole balance row from the table with all of the data and then do it PHP sided. This would use more CPU than the SQL statement would.
3. The sum of the balances is just all of the balances in the table added together. This is the same as above and doing it using MySQL is far less resource intensive then getting the whole table and doing it with PHP.

Your statement about "12 calls to the SQL Server in the index.php" isn't necessarily an issue. You'll find that a lot of PHP files have quite a few more queries.

@OP: Try get information such as what pages are causing this issue with overusage of CPU from your host. I can try later on a VPS and see what the major issue is for you if you want. Are there any cron scripts running by any chance (haven't really looked at the code).

I just asked my host about this.I would really appreciate if you fix this before my host takes my site down.

[sucksyd]

Checking this when I get home.

Waiting

warning ,this Michael shit is a big cheater and liar.beware.

[doogsan]

Sucksyd is a thief and a liar, don't pay attention to him

[Muhammed Zakir]

I am using this script becausr it has a referral system. I also have a custom theme integrated with this theme.I think it will be a lot of work to migrate to new script.
Can you fix this script?

Have you tried using Nginx? See http://seravo.fi/2013/optimizing-web-server-performance-with-nginx-and-php .

Sucksyd is a thief and a liar, don't pay attention to him

Sockpuppet of michealadair maybe.

   -MZ

[michaeladair]

Sucksyd is a thief and a liar, don't pay attention to him

Lol he's back... Had to block this guy from my messages due to spamming. Anyways, OP idk where to start with this problem... Idk what error you are getting or where its at.

@Muhammed, don't turn this guys thread into an accusation... Pls do that in meta, also if you want to cross reference my IP and his you'll see that they aren't the same. Plus, I live in EST and it was 6:30 on sat when he posted. No way in hell I'm waking up that early for this guy.

[ahmedjadoon]

Sucksyd is a thief and a liar, don't pay attention to him

Lol he's back... Had to block this guy from my messages due to spamming. Anyways, OP idk where to start with this problem... Idk what error you are getting or where its at.

its mysql load issue.

[Ditto]

Sucksyd is a thief and a liar, don't pay attention to him

Lol he's back... Had to block this guy from my messages due to spamming. Anyways, OP idk where to start with this problem... Idk what error you are getting or where its at.

its mysql load issue.

Are you on a VPS or shared?

[Vortex20000]

Sucksyd is a thief and a liar, don't pay attention to him

Lol he's back... Had to block this guy from my messages due to spamming. Anyways, OP idk where to start with this problem... Idk what error you are getting or where its at.

its mysql load issue.

Are you on a VPS or shared?

He already noted in the OP.

[btcminer021]

I was thinking of starting my own faucet using one of the 000's of scripts available. Any suggestions? Seems like a crowded space.

[Ditto]

Sucksyd is a thief and a liar, don't pay attention to him

Lol he's back... Had to block this guy from my messages due to spamming. Anyways, OP idk where to start with this problem... Idk what error you are getting or where its at.

its mysql load issue.

Are you on a VPS or shared?

He already noted in the OP.

I see. I can take a look if you want, OP. PM me.