0

i am wondering that percona might can do mapreduce job like mongo, it has a handlersocket implementation isnt it? is it can do a mapreduce job?

actually what i want to do is the i have to process complicated query with mysql and the table is big it almost 100GB with mapreduce solution i think i can resolve the bottleneck problem in database,

enter image description here

enter image description here

so can i do mapreduce job on MySQL percona with handlersocket interface?

the query is here (its a few query if user want long time it will be super long query)

(SELECT 

    a.jdateTime
    , "土" AS 曜日
    ,a.inNum AS 入口番号, a.outNum AS 出口番号, rin.tollgateName AS 入口名, rout.tollgateName AS 出口名, '普通車の' AS 課金車種
    , a.distance AS 料金距離 , '営業補正有' as 営業補正
    , AVG(NULLIF(a.hour0,0)) 
    , AVG(NULLIF(a.hour1,0)) 
    , AVG(NULLIF(a.hour2,0)) 
    , AVG(NULLIF(a.hour3,0)) 
    , AVG(NULLIF(a.hour4,0)), AVG(NULLIF(a.hour5,0)), AVG(NULLIF(a.hour6,0)), AVG(NULLIF(a.hour7,0)), AVG(NULLIF(a.hour8,0)), AVG(NULLIF(a.hour9,0)), AVG(NULLIF(a.hour10,0)), AVG(NULLIF(a.hour11,0)), AVG(NULLIF(a.hour12,0)), AVG(NULLIF(a.hour13,0)), AVG(NULLIF(a.hour14,0)), AVG(NULLIF(a.hour15,0)), AVG(NULLIF(a.hour16,0)), AVG(NULLIF(a.hour17,0)), AVG(NULLIF(a.hour18,0)), AVG(NULLIF(a.hour19,0)), AVG(NULLIF(a.hour20,0)), AVG(NULLIF(a.hour21,0)), AVG(NULLIF(a.hour22,0)), AVG(NULLIF(a.hour23,0))
FROM (

SELECT DATE(DATETIME) AS realDate
        , CONCAT(YEAR(DATETIME), "年", MONTH(DATETIME),"月", DAY(DATETIME), "日") AS jdateTime
        ,
        inNum,outNum,carType,distance
        , CASE WHEN HOUR(DATETIME) = 0 THEN totalNum ELSE 
            0 END AS hour0, CASE WHEN HOUR(DATETIME) = 1 THEN totalNum ELSE 
            0 END AS hour1, CASE WHEN HOUR(DATETIME) = 2 THEN totalNum ELSE 
            0 END AS hour2
        , CASE WHEN HOUR(DATETIME) = 3 THEN totalNum ELSE 
            0 END AS hour3
        , CASE WHEN HOUR(DATETIME) = 4 THEN totalNum ELSE 
            0 END AS hour4
        , CASE WHEN HOUR(DATETIME) = 5 THEN totalNum ELSE 
            0 END AS hour5
        , CASE WHEN HOUR(DATETIME) = 6 THEN totalNum ELSE 
            0 END AS hour6
        , CASE WHEN HOUR(DATETIME) = 7 THEN totalNum ELSE 
            0 END AS hour7
        , CASE WHEN HOUR(DATETIME) = 8 THEN totalNum ELSE 
            0 END AS hour8
        , CASE WHEN HOUR(DATETIME) = 9 THEN totalNum ELSE 
            0 END AS hour9
        , CASE WHEN HOUR(DATETIME) = 10 THEN totalNum ELSE 
            0 END AS hour10
        , CASE WHEN HOUR(DATETIME) = 11 THEN totalNum ELSE 
            0 END AS hour11
        , CASE WHEN HOUR(DATETIME) = 12 THEN totalNum ELSE 
            0 END AS hour12
        , CASE WHEN HOUR(DATETIME) = 13 THEN totalNum ELSE 
            0 END AS hour13
        , CASE WHEN HOUR(DATETIME) = 14 THEN totalNum ELSE 
            0 END AS hour14
        , CASE WHEN HOUR(DATETIME) = 15 THEN totalNum ELSE 
            0 END AS hour15
        , CASE WHEN HOUR(DATETIME) = 16 THEN totalNum ELSE 
            0 END AS hour16
        , CASE WHEN HOUR(DATETIME) = 17 THEN totalNum ELSE 
            0 END AS hour17
        , CASE WHEN HOUR(DATETIME) = 18 THEN totalNum ELSE 
            0 END AS hour18
        , CASE WHEN HOUR(DATETIME) = 19 THEN totalNum ELSE 
            0 END AS hour19
        , CASE WHEN HOUR(DATETIME) = 20 THEN totalNum ELSE 
            0 END AS hour20
        , CASE WHEN HOUR(DATETIME) = 21 THEN totalNum ELSE 
            0 END AS hour21
        , CASE WHEN HOUR(DATETIME) = 22 THEN totalNum ELSE 
            0 END AS hour22
        , CASE WHEN HOUR(DATETIME) = 23 THEN totalNum ELSE 
            0 END AS hour23
FROM table_200901  where  inNum =  015  and   outNUm in ( 267,269,271,273,275,277,279,285,287,289,291,293,295) and date(dateTime) >= date('2009-01-01') and date(dateTime) <= date('2009-03-01')   and carType = 1 



) AS a
LEFT OUTER
JOIN ramp_block AS rin ON rin.tollgateCode = a.inNum
LEFT OUTER
JOIN ramp_block AS rout ON rout.tollgateCode = a.outNum
GROUP BY a.realDate , a.carType
ORDER BY a.realDate) UNION(SELECT 

    a.jdateTime
    , "土" AS 曜日
    ,a.inNum AS 入口番号, a.outNum AS 出口番号, rin.tollgateName AS 入口名, rout.tollgateName AS 出口名, '普通車の' AS 課金車種
    , a.distance AS 料金距離 , '営業補正有' as 営業補正
    , AVG(NULLIF(a.hour0,0)) 
    , AVG(NULLIF(a.hour1,0)) 
    , AVG(NULLIF(a.hour2,0)) 
    , AVG(NULLIF(a.hour3,0)) 
    , AVG(NULLIF(a.hour4,0)), AVG(NULLIF(a.hour5,0)), AVG(NULLIF(a.hour6,0)), AVG(NULLIF(a.hour7,0)), AVG(NULLIF(a.hour8,0)), AVG(NULLIF(a.hour9,0)), AVG(NULLIF(a.hour10,0)), AVG(NULLIF(a.hour11,0)), AVG(NULLIF(a.hour12,0)), AVG(NULLIF(a.hour13,0)), AVG(NULLIF(a.hour14,0)), AVG(NULLIF(a.hour15,0)), AVG(NULLIF(a.hour16,0)), AVG(NULLIF(a.hour17,0)), AVG(NULLIF(a.hour18,0)), AVG(NULLIF(a.hour19,0)), AVG(NULLIF(a.hour20,0)), AVG(NULLIF(a.hour21,0)), AVG(NULLIF(a.hour22,0)), AVG(NULLIF(a.hour23,0))
FROM (

SELECT DATE(DATETIME) AS realDate
        , CONCAT(YEAR(DATETIME), "年", MONTH(DATETIME),"月", DAY(DATETIME), "日") AS jdateTime
        ,
        inNum,outNum,carType,distance
        , CASE WHEN HOUR(DATETIME) = 0 THEN totalNum ELSE 
            0 END AS hour0, CASE WHEN HOUR(DATETIME) = 1 THEN totalNum ELSE 
            0 END AS hour1, CASE WHEN HOUR(DATETIME) = 2 THEN totalNum ELSE 
            0 END AS hour2
        , CASE WHEN HOUR(DATETIME) = 3 THEN totalNum ELSE 
            0 END AS hour3
        , CASE WHEN HOUR(DATETIME) = 4 THEN totalNum ELSE 
            0 END AS hour4
        , CASE WHEN HOUR(DATETIME) = 5 THEN totalNum ELSE 
            0 END AS hour5
        , CASE WHEN HOUR(DATETIME) = 6 THEN totalNum ELSE 
            0 END AS hour6
        , CASE WHEN HOUR(DATETIME) = 7 THEN totalNum ELSE 
            0 END AS hour7
        , CASE WHEN HOUR(DATETIME) = 8 THEN totalNum ELSE 
            0 END AS hour8
        , CASE WHEN HOUR(DATETIME) = 9 THEN totalNum ELSE 
            0 END AS hour9
        , CASE WHEN HOUR(DATETIME) = 10 THEN totalNum ELSE 
            0 END AS hour10
        , CASE WHEN HOUR(DATETIME) = 11 THEN totalNum ELSE 
            0 END AS hour11
        , CASE WHEN HOUR(DATETIME) = 12 THEN totalNum ELSE 
            0 END AS hour12
        , CASE WHEN HOUR(DATETIME) = 13 THEN totalNum ELSE 
            0 END AS hour13
        , CASE WHEN HOUR(DATETIME) = 14 THEN totalNum ELSE 
            0 END AS hour14
        , CASE WHEN HOUR(DATETIME) = 15 THEN totalNum ELSE 
            0 END AS hour15
        , CASE WHEN HOUR(DATETIME) = 16 THEN totalNum ELSE 
            0 END AS hour16
        , CASE WHEN HOUR(DATETIME) = 17 THEN totalNum ELSE 
            0 END AS hour17
        , CASE WHEN HOUR(DATETIME) = 18 THEN totalNum ELSE 
            0 END AS hour18
        , CASE WHEN HOUR(DATETIME) = 19 THEN totalNum ELSE 
            0 END AS hour19
        , CASE WHEN HOUR(DATETIME) = 20 THEN totalNum ELSE 
            0 END AS hour20
        , CASE WHEN HOUR(DATETIME) = 21 THEN totalNum ELSE 
            0 END AS hour21
        , CASE WHEN HOUR(DATETIME) = 22 THEN totalNum ELSE 
            0 END AS hour22
        , CASE WHEN HOUR(DATETIME) = 23 THEN totalNum ELSE 
            0 END AS hour23
FROM table_200902  where  inNum =  015  and   outNUm in ( 267,269,271,273,275,277,279,285,287,289,291,293,295) and date(dateTime) >= date('2009-01-01') and date(dateTime) <= date('2009-03-01')   and carType = 1 



) AS a
LEFT OUTER
JOIN ramp_block AS rin ON rin.tollgateCode = a.inNum
LEFT OUTER
JOIN ramp_block AS rout ON rout.tollgateCode = a.outNum
GROUP BY a.realDate , a.carType
ORDER BY a.realDate) UNION(SELECT 

    a.jdateTime
    , "土" AS 曜日
    ,a.inNum AS 入口番号, a.outNum AS 出口番号, rin.tollgateName AS 入口名, rout.tollgateName AS 出口名, '普通車の' AS 課金車種
    , a.distance AS 料金距離 , '営業補正有' as 営業補正
    , AVG(NULLIF(a.hour0,0)) 
    , AVG(NULLIF(a.hour1,0)) 
    , AVG(NULLIF(a.hour2,0)) 
    , AVG(NULLIF(a.hour3,0)) 
    , AVG(NULLIF(a.hour4,0)), AVG(NULLIF(a.hour5,0)), AVG(NULLIF(a.hour6,0)), AVG(NULLIF(a.hour7,0)), AVG(NULLIF(a.hour8,0)), AVG(NULLIF(a.hour9,0)), AVG(NULLIF(a.hour10,0)), AVG(NULLIF(a.hour11,0)), AVG(NULLIF(a.hour12,0)), AVG(NULLIF(a.hour13,0)), AVG(NULLIF(a.hour14,0)), AVG(NULLIF(a.hour15,0)), AVG(NULLIF(a.hour16,0)), AVG(NULLIF(a.hour17,0)), AVG(NULLIF(a.hour18,0)), AVG(NULLIF(a.hour19,0)), AVG(NULLIF(a.hour20,0)), AVG(NULLIF(a.hour21,0)), AVG(NULLIF(a.hour22,0)), AVG(NULLIF(a.hour23,0))
FROM (

SELECT DATE(DATETIME) AS realDate
        , CONCAT(YEAR(DATETIME), "年", MONTH(DATETIME),"月", DAY(DATETIME), "日") AS jdateTime
        ,
        inNum,outNum,carType,distance
        , CASE WHEN HOUR(DATETIME) = 0 THEN totalNum ELSE 
            0 END AS hour0, CASE WHEN HOUR(DATETIME) = 1 THEN totalNum ELSE 
            0 END AS hour1, CASE WHEN HOUR(DATETIME) = 2 THEN totalNum ELSE 
            0 END AS hour2
        , CASE WHEN HOUR(DATETIME) = 3 THEN totalNum ELSE 
            0 END AS hour3
        , CASE WHEN HOUR(DATETIME) = 4 THEN totalNum ELSE 
            0 END AS hour4
        , CASE WHEN HOUR(DATETIME) = 5 THEN totalNum ELSE 
            0 END AS hour5
        , CASE WHEN HOUR(DATETIME) = 6 THEN totalNum ELSE 
            0 END AS hour6
        , CASE WHEN HOUR(DATETIME) = 7 THEN totalNum ELSE 
            0 END AS hour7
        , CASE WHEN HOUR(DATETIME) = 8 THEN totalNum ELSE 
            0 END AS hour8
        , CASE WHEN HOUR(DATETIME) = 9 THEN totalNum ELSE 
            0 END AS hour9
        , CASE WHEN HOUR(DATETIME) = 10 THEN totalNum ELSE 
            0 END AS hour10
        , CASE WHEN HOUR(DATETIME) = 11 THEN totalNum ELSE 
            0 END AS hour11
        , CASE WHEN HOUR(DATETIME) = 12 THEN totalNum ELSE 
            0 END AS hour12
        , CASE WHEN HOUR(DATETIME) = 13 THEN totalNum ELSE 
            0 END AS hour13
        , CASE WHEN HOUR(DATETIME) = 14 THEN totalNum ELSE 
            0 END AS hour14
        , CASE WHEN HOUR(DATETIME) = 15 THEN totalNum ELSE 
            0 END AS hour15
        , CASE WHEN HOUR(DATETIME) = 16 THEN totalNum ELSE 
            0 END AS hour16
        , CASE WHEN HOUR(DATETIME) = 17 THEN totalNum ELSE 
            0 END AS hour17
        , CASE WHEN HOUR(DATETIME) = 18 THEN totalNum ELSE 
            0 END AS hour18
        , CASE WHEN HOUR(DATETIME) = 19 THEN totalNum ELSE 
            0 END AS hour19
        , CASE WHEN HOUR(DATETIME) = 20 THEN totalNum ELSE 
            0 END AS hour20
        , CASE WHEN HOUR(DATETIME) = 21 THEN totalNum ELSE 
            0 END AS hour21
        , CASE WHEN HOUR(DATETIME) = 22 THEN totalNum ELSE 
            0 END AS hour22
        , CASE WHEN HOUR(DATETIME) = 23 THEN totalNum ELSE 
            0 END AS hour23
FROM table_200903  where  inNum =  015  and   outNUm in ( 267,269,271,273,275,277,279,285,287,289,291,293,295) and date(dateTime) >= date('2009-01-01') and date(dateTime) <= date('2009-03-01')   and carType = 1 



) AS a
LEFT OUTER
JOIN ramp_block AS rin ON rin.tollgateCode = a.inNum
LEFT OUTER
JOIN ramp_block AS rout ON rout.tollgateCode = a.outNum
GROUP BY a.realDate , a.carType
ORDER BY a.realDate)

the table structure i've posted is just 1/100 size of table i've just received only few table, the real table stored in the central server is very big, i cant post all because its too long

the next requirement from client is the, i have to do correction which calculate the total number of totalNum and divided with new value and also splitted based on day , but its not day that has a time each 24:00. the client want to start day each 9:00 morning. and the ratio will be multiplied with the real car that pass the inNum gate , it is impossible if i dont use map reduce, too many query will make it so slow. if i use mapreduce paradigm i think i can do anything like sum all first and then divide with given value, and after i got a ratio i do the query then

3
  • map reduce often just an aggregate function. Mongo has to do it because it doesn't have any group by functions. 100G is not really a large table. What are you trying to do and what is your config? I wager you can tune things and run SQL.
    – eroomydna
    Sep 4, 2014 at 23:18
  • the table i've used is just report table which been splitted. to make query faster, the client want that, not me. i will update the pic here Sep 5, 2014 at 4:53
  • the next requirement from client is the, i have to do correction which calculate the total number of totalNum and divided with new value and the ratio will be multiplied with the real car that pass the inNum gate , it is impossible if i dont use map reduce, too many query will make it so slow. if i use mapreduce paradigm i think i can do anything like sum all first and then divide with given value, and after i got a ratio i do the query then Sep 5, 2014 at 5:01

1 Answer 1

0

Have you considered using InfiniDB with MySQL and Hadoop? It's reviewed here. It uses sophisticated compression as well as being a columnar based storage engine for MySQL (and Open Source to boot!).

You appear to suggest that there's only one 100GB table - would you care to post its structure and the "complicated query"?

If you are considering using a MySQL based option, MySQL itself, MariaDB and InfiniDB come back with options. The Percona site has many links to Hadoop and other NoSQL material, but AFAICS, their server makes no claims to work with Hadoop (whereas the others do).

[EDIT in response to OP's comments]

Take a look here first (but it dates from 2010, and a lot has changed, in particular, InfiniDB is now completely Open Source). Then check out this - if Vadim Tkachenko thinks it's "worth attention" then it is. You also have the actual benchmark here - check to see if it corresponds to your use case.

I think that this MIGHT be good for your use case, but only testing will tell! And maybe you should look at putting all your month tables together - that should increase compression efficiency and speed things up.

3
  • so it cant do anything just like , mongo, Sep 5, 2014 at 5:11
  • with mapreduce solution like hadoop do, i think i can do anything like caching and maybe tuning. Sep 5, 2014 at 5:11
  • how fast mariaDB can do and infiniDB can do? is there any benchmark? i;ve seen this post sql2nosql.wordpress.com/2014/04/14/… and it has no significant differences of speed between em Sep 5, 2014 at 5:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.