My SQL Dump

MySQL musings by a self professed MySQL Geek


  • 1
"SELCT shard_col, SUM(`sum(price*qty)`) as `sum(price*qty)`
from ( select shard_col, sum(price*qty) from t1 where shard_col = 1 group by shard_col
UNION ALL
select shard_col, sum(price*qty) from t1 where shard_col = 1 group by shard_col
UNION ALL
select shard_col, sum(price*qty) from t1 where shard_col = 1 group by shard_col
) GROUP BY shard_col;"

should be
"SELCT shard_col, SUM(`sum(price*qty)`) as `sum(price*qty)`
from ( select shard_col, sum(price*qty) from t1 where shard_col = 1 group by shard_col
UNION ALL
select shard_col, sum(price*qty) from t1 where shard_col = 2 group by shard_col
UNION ALL
select shard_col, sum(price*qty) from t1 where shard_col = 3 group by shard_col
) GROUP BY shard_col;"

right ?

Have you considered making this handle multiple queries? We have a Gearman worker that takes multiple queries and runs them in parallel. It would seem to be easy to simply take multiple queries to start with and break them up into even more queries.

Re: Multiple Queries?

Brian:

All you have to do is move main() into a gearman function and make it accept a commandline argument which is the SQL query, instead of reading from a file and executing serially.

Then you can spit off as many parallel queries as you want in your application. You'll need to make main() iterate and return the results.

I need to make a roadmap page. Eventually I want the whole program to be runnable as a gearman task, so that MySQL-proxy or any other tool can kick off parallel-parallel queries. Of course, it can only do as much work as you have workers.

Edited at 2010-05-25 04:25 pm (UTC)

Thanks for this wonderful post.Admiring the time and effort you put into your blog and detailed information you offer..

Thanks for the feedback.

I've made a lot of improvements to Shard-Query since this post.
http://code.google.com/p/shard-query

Looking forward to get involved

(Anonymous)
Hey - I am really happy to find this. great job!

How to Implement Shard Query?

(Anonymous)
Hello Justin,

Having read the wonders of what shard query can do, I am very keen to implement it on my server. However, there isn't much documentation, even on google code webpage http://code.google.com/p/shard-query/. I have downloaded all the files from trunk http://shard-query.googlecode.com/svn/trunk/ and placed those files into my respective html folder and include folder. I have installed Gearman on my computer.

Next, when I open the run_query.php file using my web browser on localhost, nothing happens. I have set ini_set('display_errors','1');
ini_set('display_startup_errors','1');
error_reporting (E_ALL);
with no further errors coming out. What do I need to do to integrate it to run queries on my website on localhost?

Many Thanks.
~Beginner

Re: How to Implement Shard Query?

run_query reads queries from standard input.

echo "select a as a, count(*) cnt from some_table group by 1;" | php run_query.php --ini=myconfig.ini

Re: How to Implement Shard Query?

(Anonymous)
Hi Justin,

Instead of running on command line, how do I invoke shard query on my index.php script which has the following statement:

$sql = "select a as a, count(*) cnt from some_table group by 1";
$result = mysqli_query($cxn, $sql)
or die("couldn't execute query");

I understand that there is a main() function in run_query.php. Problem is how do I make it run and take $sql query as input when I open the page on a web browser?

Sorry that I am just beginning to learn OOP. Have not fully understood the inner workings yet.

Thanks for your time :)
~Beginner

Re: How to Implement Shard Query?

You will need to look at run_query.php as it is the example which shows how to use the ShardQuery->query() method to run queries.

run_query.php and loader.php are example programs that just happen to also be fully usable examples from the command line.

Re: How to Implement Shard Query?

In short you need:
$SQ = new ShardQuery($shards, $params);
$SQ->query($SQL)

All run_query does is split the input into statements using semicolon (;) as a terminator and then it passes the statement into Shard-Query.

Re: How to Implement Shard Query?

(Anonymous)
Thanks for the fast response. It is clearer to me now. So in short, I will need to create a new object and run the query using it's in-built query function.

By the way, I am trying to get a successful run of run_query.php first before integrating shardquery into my index.php script. Wonder if you can help me on the error below.

# echo "select lo_linenumber as a, count(*) cnt from lineorder group by lo_suppkey;" | php /var/www/html/shard/run_query.php --ini=/var/www/html/shard/shards_scaleup.ini
PHP Fatal error: Uncaught Net_Gearman_Exception: Can't connect to server in /usr/share/pear/Net/Gearman/Client.php on line 83
#0 /usr/share/pear/Net/Gearman/Client.php(83): Net_Gearman_Connection::connect('127.0.0.1:7001', 1000)
#1 /var/www/pizza/shard/shard-query.php(90): Net_Gearman_Client->__construct(Array)
#2 /var/www/html/shard/run_query.php(95): ShardQuery->query('select lo_linen...')
#3 /var/www/html/shard/run_query.php(38): main()
#4 {main}
thrown in /usr/share/pear/Net/Gearman/Connection.php on line 160

Thanks for your time.
~Beginner

Re: How to Implement Shard Query?


Re: How to Implement Shard Query?

You need to run copies of worker.php, and you need to run a gearman server. This is how shard-query works in parallel using gearmand.

Re: How to Implement Shard Query?

(Anonymous)
Hi Justin,

There seems to be many things to install on http://gearman.org/index.php?id=download. I have went through some of the installation procedures. I am not sure what I have missed out and whether my installation is successful. How do I know if gearman server is running in the background? I am a bit confused here. Please help me out.

Thanks a lot!

Re: How to Implement Shard Query?

You have to run gearmand then start workers. Please see the example EC2 images:
http://www.mysqlperformanceblog.com/2011/05/11/shard-query-ec2-images-available/

Re: How to Implement Shard Query?

You can get gearmand via CPAN:

cpan> install Server::Gearmand

#start gearmand
$ gearmand -p 7000 -u nobody -d

#start 8 workers
$ php run_workers 8

Re: How to Implement Shard Query?

(Anonymous)
Hi Justin,

Thanks for your patience. I just found out that the problem is due to a wrong port number specified in shards.ini. The port number should be 7003 instead of 7001.

Now everything works fine. Thanks!

Re: How to Implement Shard Query?

Glad to hear you were able to get it to work properly.

Cheers!

Re: How to Implement Shard Query?

(Anonymous)
Hi Justin,

My apologies that I have to seek your help again regarding this issue.

Previously, I did manage to get the shard_query to run successfully the first try by changing the gearman parameter to 127.0.0.1:7003 in the shards.ini file. However, subsequent attempts to run produces the PHP Fatal error "Uncaught Net_Gearman_Exception: Can't connect to server in /usr/share/pear/Net/Gearman/Client.php on line 83" error.

To get it to run again, I tried to use other ports on the gearman parameter. The only time it does run is when I input gearman = 127.0.0.1:80, with the output as follows:

-- INPUT SQL:
select lo_linenumber as a, count(*) cnt from lineorder group by lo_suppkey;
.
.
.
.
-- AGGREGATION SQL:
SELECT `a`,SUM(`cnt`) AS `cnt`
FROM `aggregation_tmp_75257222` GROUP BY 1
ON DUPLICATE KEY UPDATE
`a`=VALUES(`a`),
`cnt`=`cnt` + VALUES(`cnt`)
run_set: received another set of jobs
run_set: Starting another gearman job
run_set: finished set execution. enters: 0, spins: 0, sleeps: 0, total_sleep_time: 0
run_set: received another set of jobs
run_set: Starting another gearman job
PHP Fatal error: Uncaught Net_Gearman_Exception: Connection was reset in /usr/share/pear/Net/Gearman/Client.php on line 207
#0 /usr/share/pear/Net/Gearman/Client.php(207): Net_Gearman_Connection::read(Resource id #25)
#1 /var/www/pizza/shard/shard-query.php(1032): Net_Gearman_Client->runSet(Object(Net_Gearman_Set))
#2 /var/www/pizza/shard/shard-query.php(147): ShardQuery->run_set(Object(Net_Gearman_Set))
#3 /var/www/html/shard/run_query2.php(34): ShardQuery->query('select lo_linen...')
#4 /var/www/html/shard/run_query2.php(9): main()
#5 {main}
thrown in /usr/share/pear/Net/Gearman/Connection.php on line 253

I was wondering if the host parameter host=127.0.0.1 in the shards.ini file is causing the connection error or whether I need a .so file to be installed somewhere to make it work.

Shard Query runs into error when queries involve dates

(Anonymous)
I tried to run a query on my database. It didn't work and gives the following error:

PHP Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /var/www/include/shard/mysql.php on line 46

However, once I take out the line "AND closingDate >= NOW() ORDER BY postDate DESC", shard query runs and gives me the array I want. I suspect it has something to do with dates.

Do you have any idea?

Re: Shard Query runs into error when queries involve dates

Sorry I missed this. Can you please post a bug report on the bug tracker and include the SQL that you are executing?

  • 1
?

Log in