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

ahmedjadoon

legendary

Activity: 1414

Merit: 1000

Quote from: Martijnvdc on November 29, 2014, 08:49:08 AM

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

Martijnvdc

sr. member

Activity: 322

Merit: 250

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

legendary

Activity: 1414

Merit: 1000

Quote from: Bitsky on November 28, 2014, 04:40:52 AM

Quote from: ahmedjadoon on November 27, 2014, 11:03:49 PM

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.

ahmedjadoon

legendary

Activity: 1414

Merit: 1000

Quote from: h0lybyte on November 28, 2014, 04:00:26 AM

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.

Bitsky

hero member

Activity: 576

Merit: 514

Quote from: ahmedjadoon on November 27, 2014, 11:03:49 PM

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.

h0lybyte

hero member

Activity: 924

Merit: 511

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.

ahmedjadoon

legendary

Activity: 1414

Merit: 1000

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.

Topic: [Closed] Need PHP/MYSQL expert to fix my Bitcoin faucet (Read 1677 times)