That sounds about right for loading CSV directly on a 5-8 node cluster. As Gabriel/James mentioned in another thread, CSVBulkLoadTool with pre-split table might offer significantly better performance for large datasets.
On Tue, Aug 23, 2016 at 2:17 PM, John Leach <jle...@splicemachine.com> wrote: > So to load a TB of data would take around 2 days? Does that seem right to > you? > > Regards, > John > > > On Aug 23, 2016, at 3:07 PM, Mujtaba Chohan <mujt...@apache.org> wrote: > > > > Since there are 100 files on which this 600M row data is split. 5 > separate > > psql script running in parallel on single machine ran that loaded data > from > > files 1-20, 21-40, 41-60, 61-80, 81-100. Performance get affected as keys > > are in sequence in these files which lead to hot-spotting of RS, for this > > should also try out Salted <https://phoenix.apache.org/salted.html> > Phoenix > > table to get the best write performance. Higher batch size might offer > > better performance too. > > > > I've also just started data load from single psql client as loading from > > single machine can get bottled-necked on network I/O. I'll update on its > > result tomorrow and I don't think its time would be too off from loading > > using multiple clients. > > > > > > On Tue, Aug 23, 2016 at 12:49 PM, John Leach <jle...@splicemachine.com> > > wrote: > > > >> Mujtaba, > >> > >> Not following the import process. > >> > >> The 5 parallel psql clients means that you manually split the data into > 5 > >> buckets/files/directories and then run 5 import scripts simultaneously? > >> > >> If we wanted to benchmark import performance, what would be the right > >> model for that? > >> > >> Thanks this is very helpful... > >> > >> Regards, > >> John > >> > >> > >> > >>> On Aug 23, 2016, at 2:28 PM, Mujtaba Chohan <mujt...@apache.org> > wrote: > >>> > >>> FYI re-loaded with Phoenix 4.8/HBase 0.98.20 on a 8 node cluster with > 64G > >>> total/12G HBase heap. > >>> > >>> *Data Load* > >>> * 5.5 hours for 600M rows > >>> * Method: Direct CSV load using psql.py script > >>> * # client machines: 1 > >>> * Batch size 1K > >>> * Key order: *Sequential* > >>> * 5 parallel psql clients > >>> * No missing rows due to data load > >>> > >>> *Schema* > >>> CREATE TABLE LINEITEM_MULTICF (L_ORDERKEY INTEGER not null, A.L_PARTKEY > >>> INTEGER, B.L_SUPPKEY INTEGER , L_LINENUMBER INTEGER not null, > >>> B.L_QUANTITY DECIMAL(15,2), B.L_EXTENDEDPRICE DECIMAL(15,2), > C.L_DISCOUNT > >>> DECIMAL(15,2), C.L_TAX DECIMAL(15,2), C.L_RETURNFLAG CHAR(1), > >>> C.L_LINESTATUS CHAR(1), C.L_SHIPDATE DATE, C.L_COMMITDATE DATE, > >>> C.L_RECEIPTDATE DATE, C.L_SHIPINSTRUCT CHAR(25), C.L_SHIPMODE CHAR(10), > >>> C.L_COMMENT VARCHAR(44) constraint pk primary key(l_orderkey, > >>> l_linenumber)); > >>> > >>> * 100M guidepost width > >>> * ~42GB data > >>> * Uncompressed/default fast-diff encoded > >>> > >>> *Performance with default 128 threadpool* > >>> select count(*) from lineitem_multicf; > >>> +------------+ > >>> | COUNT(1) | > >>> +------------+ > >>> | 600037902 | > >>> +------------+ > >>> 1 row selected (*24.1** seconds*) > >>> > >>> select l_returnflag, l_linestatus,sum(l_quantity) as > >>> sum_qty,sum(l_extendedprice) as sum_base_price,sum(l_extendedprice * > (1 > >> - > >>> l_discount)) as sum_disc_price,sum(l_extendedprice * (1 - l_discount) > * > >> (1 > >>> + l_tax)) as sum_charge,avg(l_quantity) as avg_qty,avg(l_extendedprice) > >> as > >>> avg_price,avg(l_discount) as avg_disc,count(*) as count_order from > >> lineitem > >>> where l_shipdate <= current_date()- 90 group by l_returnflag, > >> l_linestatus > >>> order by l_returnflag, l_linestatus; > >>> 4 row selected (*185.2** seconds*) > >>> > >>> *Data* > >>> do curl -O http://static.druid.io/data/benchmarks/tpch/100/lineitem. > >> tbl.$i.gz > >>> ; done > >>> > >>> > >>> > >>> On Mon, Aug 22, 2016 at 7:48 AM, John Leach <jle...@splicemachine.com> > >>> wrote: > >>> > >>>> It looks like you guys already have most of the TPCH queries running > >> based > >>>> on Enis’s talk in Ireland this year. Very cool. > >>>> > >>>> (Slide 20: Phoenix can execute most of the TPC-H queries!) > >>>> > >>>> Regards, > >>>> John Leach > >>>> > >>>> > >>>>> On Aug 19, 2016, at 8:28 PM, Nick Dimiduk <ndimi...@gmail.com> > wrote: > >>>>> > >>>>> It's TPC-DS, not -H, but this is what I was using way back when to > run > >>>> perf > >>>>> tests over Phoenix and the query server while I was developing on it. > >> The > >>>>> first project generates, loads the data via mapreduce and the second > >> tool > >>>>> wraps up use of jmeter to run queries in parallel. > >>>>> > >>>>> https://github.com/ndimiduk/tpcds-gen > >>>>> https://github.com/ndimiduk/phoenix-performance > >>>>> > >>>>> Probably there's dust and bit-rot to brush off of both projects, but > >>>> maybe > >>>>> it'll help someone looking for a starting point? > >>>>> > >>>>> Apologies, but I haven't had time to see what the speakers have > shared > >>>>> about their setup. > >>>>> > >>>>> -n > >>>>> > >>>>> On Friday, August 19, 2016, Andrew Purtell <apurt...@apache.org> > >> wrote: > >>>>> > >>>>>>> Maybe there's such a test harness that already exists for TPC? > >>>>>> > >>>>>> TPC provides tooling but it's all proprietary. The generated data > can > >> be > >>>>>> kept separately (Druid does it at least - > >>>>>> http://druid.io/blog/2014/03/17/benchmarking-druid.html > >>>>>> ). > >>>>>> > >>>>>> I'd say there would be one time setup: generation of data sets of > >>>> various > >>>>>> sizes, conversion to compressed CSV, and upload to somewhere public > >>>> (S3?). > >>>>>> Not strictly necessary, but it would save everyone a lot of time and > >>>> hassle > >>>>>> to not have to download the TPC data generators and munge the output > >>>> every > >>>>>> time. For this one could use the TPC tools. > >>>>>> > >>>>>> Then, the most sensible avenue I think would be implementation of > new > >>>>>> Phoenix integration tests that consume that data and run uniquely > >>>> tweaked > >>>>>> queries (yeah - every datastore vendor must do that with TPC). > Phoenix > >>>> can > >>>>>> use hbase-it and get the cluster and chaos tooling such as it is for > >>>> free, > >>>>>> but the upsert/initialization/bulk load and query tooling would be > all > >>>>>> Phoenix based: the CSV loader, the JDBC driver. > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>> On Fri, Aug 19, 2016 at 5:31 PM, James Taylor < > jamestay...@apache.org > >>>>>> <javascript:;>> > >>>>>> wrote: > >>>>>> > >>>>>>> On Fri, Aug 19, 2016 at 3:01 PM, Andrew Purtell < > apurt...@apache.org > >>>>>> <javascript:;>> > >>>>>>> wrote: > >>>>>>> > >>>>>>>>> I have a long interest in 'canned' loadings. Interesting ones are > >>>>>> hard > >>>>>>> to > >>>>>>>>> come by. If Phoenix ran any or a subset of TPCs, I'd like to try > >> it. > >>>>>>>> > >>>>>>>> Likewise > >>>>>>>> > >>>>>>>>> But I don't want to be the first to try it. I am not a Phoenix > >>>>>> expert. > >>>>>>>> > >>>>>>>> Same here, I'd just email dev@phoenix with a report that TPC > query > >>>> XYZ > >>>>>>>> didn't work and that would be as far as I could get. > >>>>>>>> > >>>>>>>> I don't think the first phase would require Phoenix experience. > It's > >>>>>> more > >>>>>>> around the automation for running each TPC benchmark so the process > >> is > >>>>>>> repeatable: > >>>>>>> - pulling in the data > >>>>>>> - scripting the jobs > >>>>>>> - having a test harness they run inside > >>>>>>> - identifying the queries that don't work (ideally you wouldn't > stop > >> at > >>>>>> the > >>>>>>> first error) > >>>>>>> - filing JIRAs for these > >>>>>>> > >>>>>>> The entire framework could be built and tested using standard JDBC > >>>> APIs, > >>>>>>> and then initially run using MySQL or some other RDBMS before > trying > >> it > >>>>>>> with Phoenix. Maybe there's such a test harness that already exists > >> for > >>>>>>> TPC? > >>>>>>> > >>>>>>> Then I think the next phase would require more Phoenix & HBase > >>>>>> experience: > >>>>>>> - tweaking queries where possible given any limitations in Phoenix > >>>>>>> - adding missing syntax (or potentially using the calcite branch > >> which > >>>>>>> supports more) > >>>>>>> - tweaking Phoenix schema declarations to optimize > >>>>>>> - tweaking Phoenix & HBase configs to optimize > >>>>>>> - determining which secondary indexes to add (though I think > there's > >> an > >>>>>>> academic paper on this, I can't seem to find it) > >>>>>>> > >>>>>>> Both phases would require a significant amount of time and effort. > >> Each > >>>>>>> benchmark would likely require unique tweaks. > >>>>>>> > >>>>>>> Thanks, > >>>>>>> James > >>>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>> -- > >>>>>> Best regards, > >>>>>> > >>>>>> - Andy > >>>>>> > >>>>>> Problems worthy of attack prove their worth by hitting back. - Piet > >> Hein > >>>>>> (via Tom White) > >>>>>> > >>>> > >>>> > >> > >> > >
