Flexible mining proxy

[kjj]

So, I got sick of MySQL's buggy time zone handling and made an improvement to my script.  For this to work, you need to add apc_port as an INTEGER DEFAULT 0 column to the worker table.  Set the port to toggle when reboot is requested, or leave at zero to ignore that worker.

Code: (improved reboot script)

#!/bin/php
<?php

$threshold=15*60;

$con=mysql_connect("__DB_HOST__","__DB_USER__","__DB_PASS__");
mysql_select_db("__DATABASE__",$con);

$restarts=array();

$q="SELECT * FROM worker WHERE apc_port!=0";
$r=mysql_query($q);
while($row=mysql_fetch_assoc($r)){
 $restarts[$row['id']]=$row['apc_port'];
}

reset($restarts);
while(list($key,$val)=each($restarts)){
 $q="SELECT * FROM work_data WHERE worker_id=".$key." ORDER BY time_requested DESC LIMIT 1";
 $r=mysql_query($q);
 if(0!=mysql_num_rows($r)){
  $row=mysql_fetch_assoc($r);
  $lastts=strtotime($row['time_requested']." GMT");
  if((time()-$lastts)>$threshold){
   $url="http://__CGI_HOST__/cgi-bin/apc_restart.cgi?".$val;
   $junk=file($url);
  }
 }
}

?>

[1714939992]

1714939992

[1714939992]

1714939992

[1714939992]

1714939992

[1714939992]

1714939992

[1714939992]

1714939992

[error]

There was also a bug in Phoenix with LP urls that used query strings. Not sure if that may also make a difference.

http://forum.bitcoin.org/index.php?topic=6458.msg152263#msg152263

Good catch.  Yeah, that would cause problems.  I might work around this by using PATH_INFO to convey the required data.

Thanks. It was because of this proxy that I caught it.

[twmz]

I do, I definitely want to see the code.

My alternate implementation in ASP.NET, as it exists today is now published here: http://code.google.com/p/btcproxy/.  It has been functioning well for me over the past 4-5 days, but I have only tested it with phoenix miner, poclbm, and the pools shown in the screenshot.

Right now, I have not written any documentation on how to install it, so you'll need to be comfortable with ASP.NET development and SQL to get it deployed.  I'll work on a brief readme.txt over the next couple days that spells out the prerequisites and general installation steps.

As I make additional improvements or refinements, I'll update the code on that website and not hijack this thread any further.

[burtyb]

I'm not using PHP > 5.3 yet (CentOS 5.x) so I had to make a little change to get it to run without the DateTime class.

Basically I've replaced (format_date function in common.inc.php)

Code:

$obj = new DateTime($date, new DateTimeZone('UTC'));
$obj->setTimezone(new DateTimeZone($BTC_PROXY['timezone']));
return $obj->format($BTC_PROXY['date_format']);

with

Code:

return date($BTC_PROXY['date_format'],strtotime($date));

I didn't bother with the timezone as it looks like it's only used to display but that might be fixed with something like

Code:

date_default_timezone_set($BTC_PROXY['timezone']);

ChrisB.

[ciuciu]

Hi,

I try to setup the proxy but I can't connect to it using the phoenix miner. I did setup the pools and workers.
I'm using start /D C:\phoenix phoenix -u http://user:pass@mywebspace.com:80/ -k poclbm device=1 VECTORS BFI_INT FASTLOOP AGGRESSION=7 without success.

Can somebody show me an example of the phoenix command line for connecting it correctly to the web proxy?

Thanks.

[ciuciu]

Tried now and still not working.

Thanks.

[btcLeger]

nice idea but im getting lots of blank pages when handling the backend. This happens when
a) editing a pool
b) saving worker-pool managemant goes to worker-pool.php seems missing parameters (would be nice to jump back to actual woker overview page e.g. ?id=1&action=index

xampp with PHP 5.2.4

[cdhowie]

nice idea but im getting lots of blank pages when handling the backend.

xampp with PHP 5.2.4

I have not tested with many versions, but others have reported that at least PHP 5.3 is required to use the proxy.  I will add this to the readme at some point.

[pwnyboy]

nice idea but im getting lots of blank pages when handling the backend.

xampp with PHP 5.2.4

I have not tested with many versions, but others have reported that at least PHP 5.3 is required to use the proxy.  I will add this to the readme at some point.

I can also confirm many blank pages and strangeness under Cent 5.x with the default PHP version 5.1.x.  A stated dependency on PHP 5.3 would go a long way I'm sure.

[carlo]

Hello everybody,

i love your proxy its nice to have a central control unit.

There are some minor problems:
It seems a bit slow for me, often my miners (poclbm) report this:  warning: job finished, miner is idle

Ideas:
Maybe i need to pimp the apache.conf a bit. Not sure right now, my System: Debian 5 / apache
- Apache/2.2.9 (Debian) PHP/5.2.6-1+lenny9 with Suhosin-Patch mod_python/3.3.1 Python/2.5.2 mod_ssl/2.2.9 OpenSSL/0.9.8g mod_wsgi/2.5

Theres a new problem with they nice proxy, it happens with different pools, this results in a lot of failed work submissions:

Code:

02/06/2011 08:56:10, Unexpected error:
Traceback (most recent call last):
  File "BitcoinMiner.pyo", line 165, in mine
  File "BitcoinMiner.pyo", line 208, in getwork
  File "BitcoinMiner.pyo", line 235, in request
TypeError: string indices must be integers

Maybe its all apaches fault

I hope i will figure it out soon, thanks for your time spending in this project.

[burtyb]

I have not tested with many versions, but others have reported that at least PHP 5.3 is required to use the proxy.  I will add this to the readme at some point.

Using my changes above I've been using it with PHP 5.1.6 with no apparent problems, do you know of any other reasons why it requires PHP 5.3?

ChrisB.

[kjj]

This join makes me sad.

Code: ("sad join")

SELECT
                w.name AS worker,
                w.id AS worker_id,

                worked.pool_name AS active_pool,
                worked.latest - INTERVAL 5 HOUR AS active_time,

                submitted.pool_name AS last_accepted_pool,
                submitted.latest - INTERVAL 5 HOUR AS last_accepted_time,

                w.last_reboot AS last_reboot

            FROM worker w

            LEFT OUTER JOIN (
                SELECT
                    wd.worker_id AS worker_id,
                    wd.time_requested AS latest,
                    p.name AS pool_name

                FROM work_data wd

                INNER JOIN (
                    SELECT
                        worker_id,
                        MAX(time_requested) AS latest

                    FROM work_data

                    GROUP BY worker_id
                ) wd2
                    ON wd.worker_id = wd2.worker_id
                   AND wd.time_requested = wd2.latest

                INNER JOIN pool p
                    ON p.id = wd.pool_id

                GROUP BY wd.worker_id
            ) worked

            ON worked.worker_id = w.id

            LEFT OUTER JOIN (
                SELECT
                    sw.worker_id AS worker_id,
                    sw.time AS latest,
                    p.name AS pool_name

                FROM submitted_work sw

                INNER JOIN (
                    SELECT
                        worker_id,
                        MAX(time) AS latest

                    FROM submitted_work

                    WHERE result = 1

                    GROUP BY worker_id
                ) sw2
                    ON sw.worker_id = sw2.worker_id
                   AND sw.result = 1
                   AND sw.time = sw2.latest

                INNER JOIN pool p
                    ON p.id = sw.pool_id

                GROUP BY sw.worker_id
            ) submitted

            ON submitted.worker_id = w.id

            ORDER BY w.name

Solution?

Code: ("delta.sql")

ALTER TABLE work_data DROP PRIMARY KEY;
ALTER TABLE work_data ADD id INT NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST;
ALTER TABLE work_data ADD KEY (`worker_id`, `data`);

CREATE TABLE `work_data_history` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `worker_id` int(11) NOT NULL,
  `pool_id` int(11) NOT NULL,
  `data` char(152) character set ascii collate ascii_bin NOT NULL,
  `time_requested` datetime NOT NULL,
  KEY  (`worker_id`,`data`),
  KEY `worker_time` (`worker_id`,`time_requested`)
);

CREATE TABLE `submitted_work_history` (
  `id` int(11) NOT NULL auto_increment,
  `worker_id` int(11) NOT NULL,
  `pool_id` int(11) NOT NULL,
  `result` tinyint(1) NOT NULL,
  `time` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `dashboard_status_index` (`worker_id`,`result`,`time`)
);

Code: ("new cron job, hourly")

<?php

$con=mysql_connect("__DB_HOST__","__DB_USERNAME__","__DB_PASSWORD__");
mysql_select_db("__DB_DATABASE__",$con);

$q="SELECT MAX(id) AS mid FROM work_data_history";
$r=mysql_query($q);
if(mysql_errno()!=0)echo mysql_error()."\n";
$row=mysql_fetch_assoc($r);
$mid=$row['mid'];

$q="INSERT INTO work_data_history SELECT * FROM work_data WHERE id>".$mid;
mysql_query($q);
if(mysql_errno()!=0)echo mysql_error()."\n";

$q="DELETE FROM work_data WHERE time_requested < (NOW() - INTERVAL 12 HOUR)";
mysql_query($q);
if(mysql_errno()!=0)echo mysql_error()."\n";

$q="SELECT MAX(id) AS mid FROM submitted_work_history";
$r=mysql_query($q);
if(mysql_errno()!=0)echo mysql_error()."\n";
$row=mysql_fetch_assoc($r);
$mid=$row['mid'];

$q="INSERT INTO submitted_work_history SELECT * FROM submitted_work WHERE id>".$mid;
mysql_query($q);
if(mysql_errno()!=0)echo mysql_error()."\n";

$q="DELETE FROM submitted_work WHERE time < (NOW() - INTERVAL 12 HOUR)";
mysql_query($q);
if(mysql_errno()!=0)echo mysql_error()."\n";
?>

[pwnyboy]

Any news on the high rejection rate?  That's the only thing keeping me from using this software, the idea behind it is fantastic!

You're saying the long polling apparatus is still problematic, even with the latest code?

[Disposition]

everything is running fine except after awhile it just times out for all my clients, feels like there's some apache config problem.

I noticed earlier post you mentioned something about safe mode, and that's off by default so I'm a bit confused to why this is happening,

appreciated if you can look into it

edit: it seems like this only applies to btcguild for some reason.

[gigabytecoin]

Keep up the great work OP!

[pwnyboy]

everything is running fine except after awhile it just times out for all my clients, feels like there's some apache config problem.

I noticed earlier post you mentioned something about safe mode, and that's off by default so I'm a bit confused to why this is happening,

appreciated if you can look into it

edit: it seems like this only applies to btcguild for some reason.

For long polling to work correctly through the proxy, you'd need (at minimum) to have a long execution time in php.ini.  Something like this:

Code:

max_execution_time=7500

That assumes it would take 2 hours (probably a maximum of maximums) to find a block, so I added an extra 5 minutes for additional padding.  In actuality I've never seen it take more than 40 or 50 minutes to find a block though, so 7500 should be perfectly fine.  Note that modifying php.ini is a global setting, so you might be able to/might want to modify this only for the virtualhost that runs your proxy, depending on your specific OS and apache/php implementation.  Don't forget to restart httpd to make the setting take effect.

Also there might be other settings that need to be modified, as it's really not normal to have an http connection open for 2 hours.  But this one is the most obvious.

[Disposition]

@pwnyboy

Thanks, will stress test n run this for a bit.

[Disposition]

much better now, except I'm still getting a weird unable to auth error some times (I assume due to the time out between proxy and server)

the proxy(NJ based), btcmine(NY) based, Me(NYC) is really close, the ping is great so I think it's something with this script.

pwnyboy any other php.ini configuration I should know about it?

[btcLeger]

Ok with php 5.3.5 things work better but there is still a blank page when editing a worker. Changed name or password is not saved.

I've let it run for 2 hours with 4 workers and 1 Ghash/s total and got 3% rejected shares with guiminer using poclbm no matter which pool I join. The rate of stale shares is 10 times higher on deepbit when using the proxy.

Besides of that I like the idea and the concept. I will use the proxy and donate if the stale shares problem gets solved.

Features I'd love to have:
- autorefresh for dashboard
- workerstatus with configureable timeout display (green/red row background)

[Disposition]

bump, seriously if this technical problem is solved and timing-out auth issue is fixed, I put a 5btc bounty on it.

edit: nvm I fixed it.