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

[ahmedjadoon]

My host suspended my bitcoin faucet due to mysql abuse although I'm on semi-dedicated hosting. Its based on open source https://gitorious.org/elbandi/minifaucet/ script and custom esigned.
I received this message from hosting "Your account has been hammering the mysql server to the point that it is causing load issues on the server. The problem is not the amount of processes, but rather your script needs to be optimized to work better with mysql. It looks like each of your mysql processes are using 80-100% of the mysql's resources, thus when you have many processing going on at one, it is bogging the down the mysql server and the server itself for others on the server."
If someone can fix it and want to have a look he can PM me. Also quote estimated cost and preferred payment methods.
P.S Host un-suspended my site for just 24 hours so I can solve the issue.

[h0lybyte]

Why not just pick up a kimisurf dedicated server? It just makes more sense to be your own host, if you are already reaching a point where a shared host cant handle the resources being used.

[Bitsky]

It looks like each of your mysql processes are using 80-100% of the mysql's resources, thus when you have many processing going on at one, it is bogging the down the mysql server and the server itself for others on the server.

Take a look at the processlist and see which queries are slowing down the server and log slow queries.
I just scrolled a little through the pages on git and it looks like the faucet isn't the best design. No prepared statements, no real db design. For example, index.php as the query "SELECT dispensed FROM dispenses WHERE email='$address' OR ip='$ip' ORDER BY id DESC LIMIT 1", but dispenses only has a key on id and no index on email and ip. With lots of entries, that will bog down the server. Might also be worth to try a switch from innodb to myisam.
Personally, I'd look at other faucets to be honest.

[ahmedjadoon]

Why not just pick up a kimisurf dedicated server? It just makes more sense to be your own host, if you are already reaching a point where a shared host cant handle the resources being used.

Its almost semi dedicated and I have paid 1 year upfront so not looking to move.

[ahmedjadoon]

It looks like each of your mysql processes are using 80-100% of the mysql's resources, thus when you have many processing going on at one, it is bogging the down the mysql server and the server itself for others on the server.

Take a look at the processlist and see which queries are slowing down the server and log slow queries.
I just scrolled a little through the pages on git and it looks like the faucet isn't the best design. No prepared statements, no real db design. For example, index.php as the query "SELECT dispensed FROM dispenses WHERE email='$address' OR ip='$ip' ORDER BY id DESC LIMIT 1", but dispenses only has a key on id and no index on email and ip. With lots of entries, that will bog down the server. Might also be worth to try a switch from innodb to myisam.
Personally, I'd look at other faucets to be honest.

If you can help to fix it please PM me.It really urgent.

[Martijnvdc]

I'm willing to have a look.
I used to run a bitcoin faucet a long time ago. I suspect the issue is indexing on the database.

EDIT: yeah, the tables have no indexes... I PM'ed you the MySQL commands to create the needed indexes.

[ahmedjadoon]

I'm willing to have a look.
I used to run a bitcoin faucet a long time ago. I suspect the issue is indexing on the database.

EDIT: yeah, the tables have no indexes... I PM'ed you the MySQL commands to create the needed indexes.

It worked. Thanks a lot for help.Really appreciated