Can this include some actual proxy kind of features, ie. caching?
So that this could be used behind a flaky internet connection to keep miners 100% at work, if the flakyness is in the seconds range?
A non-PHP getwork backend is planned to resolve this and other LP issues. PHP is not well-suited to this kind of task.
Check out the join that creates the status display.
LOL! Yeah that would cause some serious issues (first query in admin/index.php) 3rd query is a monstrosity.
Well there is the problem, using dynamic (on the fly created) tables etc.
These queries is almost like SELECT *, wonder if they ever hit any indexes ...
On my database, an EXPLAIN against that query shows zero table scans and very little processing. I spent a lot of time optimizing that query. Note that a few indexes that are required to prevent table scans are not present in the schema; these were added later and I don't have a database migration script just yet, so it's expected that these queries will run a bit slow unless you've manually created the needed indexes.
Well there is the problem, using dynamic (on the fly created) tables etc.
This in particular made me lol. Subqueries can be an effective optimization technique if you know how to use them correctly, and any DBA knows that. In the "last 10 submissions" cases, MySQL creates a plan that executes the LIMIT after the JOIN, which results in a full table scan of work_data/submitted_work. Querying those tables in a subquery with LIMIT forces it to execute the limit first, which results in a very fast join. This was a pain point until I refactored the query to use a subquery to derive the data tables. Please know WTF you are talking about and use EXPLAIN, kthx.
EDIT: I just checked, and interestingly doesn't hit index, which is really wierd. Oh well, i check why at better time.
Exactly. MySQL doesn't use the indexes in this case because it has decided to apply the LIMIT after the joins. So it does a table scan. And you don't need indexes to do a table scan, now do you? Essentially, MySQL's query analyzer sucks, and the subquery is the workaround.
So let's do some investigation:
mysql> SELECT COUNT(*) FROM work_data;
+----------+
| COUNT(*) |
+----------+
| 76422 |
+----------+
1 row in set (0.01 sec)
mysql> SELECT COUNT(*) FROM submitted_work;
+----------+
| COUNT(*) |
+----------+
| 126715 |
+----------+
1 row in set (0.00 sec)
After executing the dashboard status query:
EXPLAIN on the dashboard status query:
+----+-------------+----------------+--------+------------------------------------------------+-------------------------+---------+---------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+--------+------------------------------------------------+-------------------------+---------+---------------------------------+------+----------------------------------------------+
| 1 | PRIMARY | w | ALL | NULL | NULL | NULL | NULL | 3 | Using temporary; Using filesort |
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 1 | |
| 6 | DERIVED | sw | range | dashboard_status_index2 | dashboard_status_index2 | 8 | NULL | 136 | Using where; Using temporary; Using filesort |
| 4 | DERIVED | | ALL | NULL | NULL | NULL | NULL | 2 | Using temporary; Using filesort |
| 4 | DERIVED | sw | ref | dashboard_status_index,dashboard_status_index2 | dashboard_status_index | 13 | sw2.worker_id,sw2.latest | 1 | |
| 4 | DERIVED | p | eq_ref | PRIMARY | PRIMARY | 4 | bitcoin-mining-proxy.sw.pool_id | 1 | |
| 5 | DERIVED | submitted_work | range | NULL | dashboard_status_index | 5 | NULL | 3 | Using where; Using index for group-by |
| 2 | DERIVED | | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | wd | ref | PRIMARY,worker_time | worker_time | 12 | const,const | 1 | |
| 2 | DERIVED | p | eq_ref | PRIMARY | PRIMARY | 4 | bitcoin-mining-proxy.wd.pool_id | 1 | |
| 3 | DERIVED | work_data | range | NULL | worker_time | 4 | NULL | 3 | Using index for group-by |
+----+-------------+----------------+--------+------------------------------------------------+-------------------------+---------+---------------------------------+------+----------------------------------------------+
Sorry, the query is fine. A bit big, but it's attempting to reduce quite a bit of data down to three rows. So meh. You do better and I'll take a patch.
