Mysql – can i use percona to do the mapreduce job

MySQLpercona

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

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

Best Answer

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.

Best Answer

Related Solutions

MySQL optimization – year column grouping – using temporary table, filesort

Mysql – selecting the month day and year from a transaction table

Related Question