You are at the newest post.
Click here to check if anything new just came in.

## August112015

16:58

### DBpedia Usage Report, August 2015

We recently published the latest DBpedia Usage Report, covering v3.3 (released July, 2009) to v3.10 (sometimes called "DBpedia 2014"; released September, 2014).

The new report has usage data through July 31, 2015, and brought a few surprises to our eyes. What do you think?

## August052015

14:02

### Beauty behind the scenes

Good things can often go unnoticed, especially if they’re not immediately visible. Last month the government of Sweden, through Vinnova, released a revamped version of their open data portal, Öppnadata.se. The portal still runs on CKAN, the open data management system. It even has the same visual feeling but the principles behind the portal are completely different. The main idea behind the new version of Öppnadata.se is automation. Open Knowledge teamed up with the Swedish company Metasolutions to build and deliver an automated open data portal.

## Responsive design

In modern web development, one aspect of website automation called responsive design has become very popular. With this technique the website automatically adjusts the presentation depending on the screen size. That is, it knows how best to present the content given different screen sizes. Öppnadata.se got a slight facelift in terms of tweaks to its appearance, but the big news on that front is that it now has a responsive design. The portal looks different if you access it on mobile phones or if you visit it on desktops, but the content is still the same.

These changes were contributed to CKAN. They are now a part of the CKAN core web application as of version 2.3. This means everyone can now have responsive data portals as long as they use a recent version of CKAN .

## Data catalogs

Perhaps the biggest innovation of Öppnadata.se is how the automation process works for adding new datasets to the catalog. Normally with CKAN, data publishers log in and create or update their datasets on the CKAN site. CKAN has for a long time also supported something called harvesting, where an instance of CKAN goes out and fetches new datasets and makes them available. That’s a form of automation, but it’s dependent on specific software being used or special harvesters for each source. So harvesting from one CKAN instance to another is simple. Harvesting from a specific geospatial data source is simple. Automatically harvesting from something you don’t know and doesn’t exist yet is hard.

That’s the reality which Öppnadata.se faces. Only a minority of public organisations and municipalities in Sweden publish open data at the moment. So a decision hasn’t been made by a majority of the public entities for what software or solution will be used to publish open data.

To tackle this problem, Öppnadata.se relies on an open standard from the World Wide Web Consortium called DCAT (Data Catalog Vocabulary). The open standard describes how to publish a list of datasets and it allows Swedish public bodies to pick whatever solution they like to publish datasets, as long as one of its outputs conforms with DCAT.

Öppnadata.se actually uses a DCAT application profile which was specially created for Sweden by Metasolutions and defines in more detail what to expect, for example that Öppnadata.se expects to find dataset classifications according the Eurovoc classification system.

Thanks to this effort significant improvements have been made to CKAN’s support for RDF and DCAT. They include application profiles (like the Swedish one) for harvesting and exposing DCAT metadata in different formats. So a CKAN instance can now automatically harvest datasets from a range of DCAT sources, which is exactly what Öppnadata.se does. For Öppnadata.se, the CKAN support also makes it easy for Swedish public bodies who use CKAN to automatically expose their datasets correctly so that they can be automatically harvested by Öppnadata.se. For more information have a look at the CKAN DCAT extension documentation.

## Dead or alive

The Web is decentralised and always changing. A link to a webpage that worked yesterday might not work today because the page was moved. When automatically adding external links, for example, links to resources for a dataset, you run into the risk of adding links to resources that no longer exist.

These are only the automation highlights of the new Öppnadata.se. Other changes were made that have little to do with automation but are still not immediately visible, so a lot of Öppnadata.se’s beauty happens behind the scenes. That’s also the case for other open data portals. You might just visit your open data portal to get some open data, but you might not realise the amount of effort and coordination it takes to get that data to you.

## August032015

09:49

### How the PoolParty Semantic Suite is learning to speak 40+ languages

Business is becoming more and more globalised, and enterprises and organisations are acting in several different regions and thus facing more challenges of different cultural aspects as well as respective language barriers. Looking at the European market, we even see 24 working languages in EU28, which make cross-border services considerably complicated. As a result, powerful language technology is needed, and intense efforts have already been taken in the EU to deal with this situation and enable the vision of a multilingual digital single market (a priority area of the European Commission this year, see: http://ec.europa.eu/priorities/digital-single-market/).

Here at the Semantic Web Company we also witness fast-growing demands for language-independent and/or specific-language and cross-language solutions to enable business cases like cross-lingual search or multilingual data management approaches. To provide such solutions, a multilingual metadata and data management approach is needed, and this is where PoolParty Semantic Suite comes into the game: as PoolParty follows W3C semantic web standards like SKOS, we have language-independent-based technologies in place and our customers already benefit from them. However, as regards text analysis and text extraction, the ability to process multilingual information and data is key for success – which means that the systems need to speak as many languages as possible.

Our new cooperation with K Dictionaries (KD) is enabling the PoolParty Semantic Suite to continuously “learn to speak” more and more languages, by making use of KD’s rich monolingual, bilingual and multilingual content and its long-time experience in lexicography as a base for improved multi-language text analysis and processing.

KD (http://kdictionaries.com/ http://kdictionaries-online.com/) is a technology-oriented content and data creator that is based in Tel Aviv and cooperates with publishing partners, ICT firms, the academe and professional associations worldwide. It deals with nearly 50 languages, offering quality monolingual, bilingual and multilingual lexical datasets, morphological word forms, phonetic transcription, etc.

As a result of this cooperation, PoolParty now provides language bundles in the following languages, which can be licensed together with all types of PoolParty servers:

• English
• French
• German
• Italian
• Japanese
• Korean
• Russian
• Slovak
• Spanish

Additional language bundles are in preparation and will be in place soon!

Furthermore, SWC and KD are partners in a brand new EUREKA project that is supported by a bilateral technology/innovation program between Austria and Israel. The project is called LDL4HELTA (Linked Data Lexicography for High-End Language Technology Application) and combines lexicography and Language Technology with Semantic Web and Linked (Open) Data mechanisms and technologies to improve existing and develop new products and services. It integrates the products of both partners to better serve existing customers and new ones, as well as to enter together new markets in the field of Linked Data lexicography-based Language Technology solutions. This project has been successfully kicked off in early July and has a duration of 24 months, with the first concrete results due early in 2016.

The LDL4HELTA project is supported by a research partner (Austrian Academy of Sciences) and an expert Advisory Board including  Prof Christian Chiarcos (Goethe University, Frankfurt), Mr Orri Erling (OpenLink Software), Dr Sebastian Hellmann (Leipzig University), Prof Alon Itai (Technion, Haifa), and Ms Eveline Wandl-Wogt (Austrian Academy of Sciences).

So stay tuned and we will inform you about news and activities of this cooperation here in the blog continuously!

## July312015

07:57
Flows, data or quality: how to manage interaction with customers efficiently

## July272015

09:43

### Meet the PoolParty Team in Washington, Vienna, Utrecht, …, or on the web

Meet the PoolParty Team at upcoming events, either to listen to a presentation or to have a chat with our team in the exhibition area. PoolParty is proud sponsor of the following events in the autumn of 2015:

Find details about a talk about ‘Dynamic Semantic Publishing’, which will be given by Andreas Blumauer, CEO of Semantic Web Company at Taxonomy Bootcamp.

If you are not able to visit these events, watch out for the webinars we will organize in the upcoming months in our event calendar.

## July222015

13:24

### CKAN 2.4 release and patch releases

We are happy to announce that CKAN 2.4 is now released. In addition, new patch releases for older versions of CKAN are now available to download and install.

CKAN 2.4

The 2.4 release brings a way to set the CKAN config via environment variables and via the API, which is useful for automated deployment setups. 2.4 also includes plenty of other improvements contributed by the CKAN developer community during the past 4 months, as detailed in the 2.4.0 CHANGELOG

If you have customizations or extensions, we suggest you trial the upgrade first in a test environment and refer to the changes in the changelog. Upgrade instructions are below.

CKAN patch releases

These new patch releases for CKAN 2.0.x, 2.1.x, 2.2.x and 2.3.x fix important bugs and security issues, so users are strongly encouraged to upgrade to the latest patch release for the CKAN version they are using.

For a list of the fixes included you can check the CHANGELOG:

For details on how to upgrade, see the following links depending on your install method:

If you find any issue, you can let the technical team know in the mailing list or the IRC channel.

## July152015

20:12

### Big Data, Part 2: Virtuoso Meets Impala

In this article we will look at Virtuoso vs. Impala with 100G TPC-H on two R3.8 EC2 instances. We get a single user win for Virtuoso by a factor of 136, and a five user win by a factor of 55. The details and analysis follow.

The load setup is the same as ever, with copying from CSV files attached as external tables into Parquet tables. We get lineitem split over 88 Parquet files, which should provide enough parallelism for the platform. The Impala documentation states that there can be up to one thread per file, and here we wish to see maximum parallelism for a single query stream. We use the schema from the Impala github checkout, with string for string and date columns, and decimal for numbers. We suppose the authors know what works best.

The execution behavior is surprising. Sometimes we get full platform utilization, but quite often only 200% CPU per box. The query plan for Q1, for example, says 2 cores per box. This makes no sense, as the same plan fully well knows the table cardinality. The settings for scanner threads and cores to use (in impala-shell) can be changed, but the behavior does not seem to change.

Following are the run times for one query stream.

Query Virtuoso Impala Notes —  332     s   841     s  Data Load Q1  1.098 s   164.61  s  Q2  0.187 s   24.19  s  Q3  0.761 s   105.70  s  Q4  0.205 s   179.67  s  Q5  0.808 s   84.51  s  Q6  2.403 s   4.43  s  Q7  0.59  s   270.88  s  Q8  0.775 s   51.89  s  Q9  1.836 s   177.72  s  Q10  3.165 s   39.85  s  Q11  1.37  s   22.56  s  Q12  0.356 s   17.03  s  Q13  2.233 s   103.67  s  Q14  0.488 s   10.86  s  Q15  0.72  s   11.49  s  Q16  0.814 s   23.93  s  Q17  0.681 s   276.06  s  Q18  1.324 s   267.13  s  Q19  0.417 s   368.80  s  Q20  0.792 s   60.45  s  Q21  0.720 s   418.09  s  Q22  0.155 s   40.59  s  Total  20     s   2724     s

Because the platform utilization was often low, we made a second experiment running the same queries in five parallel sessions. We show the average execution time for each query. We then compare this with the Virtuoso throughput run average times. We permute the single query stream used in the first tests in 5 different orders, as per the TPC-H spec. The results are not entirely comparable, because Virtuoso is doing the refreshes in parallel. According to Impala documentation, there is no random delete operation, so the refreshes cannot be implemented.

Just to establish a baseline, we do SELECT COUNT (*) FROM lineitem. This takes 20s when run by itself. When run in five parallel sessions, the fastest terminates in 64s and the slowest in 69s. Looking at top, the platform utilization is indeed about 5x more in CPU%, but the concurrency does not add much to throughput. This is odd, considering that there is no synchronization requirement worth mentioning between the operations.

Following are the average times for each query in the 5 stream experiment.

Query Virtuoso Impala Notes Q1  1.95 s   191.81 s  Q2  0.70 s   40.40 s  Q3  2.01 s   95.67 s  Q4  0.71 s   345.11 s  Q5  2.93 s   112.29 s  Q6  4.76 s   14.41 s  Q7  2.08 s   329.25 s  Q8  3.00 s   98.91 s  Q9  5.58 s   250.88 s  Q10  8.23 s   55.23 s  Q11  4.26 s   27.84 s  Q12  1.74 s   37.66 s  Q13  6.07 s   147.69 s  Q14  1.73 s   23.91 s  Q15  2.27 s   23.79 s  Q16  2.41 s   34.76 s  Q17  3.92 s   362.43 s  Q18  3.02 s   348.08 s  Q19  2.27 s   443.94 s  Q20  3.05 s   92.50 s  Q21  2.00 s   623.69 s  Q22  0.37 s   61.36 s  Total for
Slowest Stream  67    s   3740    s

There are 4 queries in Impala that terminated with an error (memory limit exceeded). These were two Q21s, one Q19, one Q4. One stream executed without errors, so this stream is reported as the slowest stream. Q21 will, in the absence of indexed access, do a hash build side of half of lineitem, which explains running out of memory. Virtuoso does Q21 mostly by index.

Looking at the 5 streams, we see CPU between 1000% and 2000% on either box. This looks about 5x more than the 250% per box that we were seeing with, for instance, Q1. The process sizes for impalad are over 160G, certainly enough to have the working set in memory. iostat also does not show any I, so we seem to be running from memory, as intended.

We observe that Impala does not store tables in any specific order. Therefore a merge join of orders and lineitem is not possible. Thus we always get a hash join with a potentially large build side, e.g., half of orders and half of lineitem in Q21, and all orders in Q9. This explains in part why these take so long. TPC-DS does not pose this particular problem though, as there are no tables in the DS schema where the primary key of one would be the prefix of that of another.

However, the lineitem/orders join does not explain the scores on Q1, Q20, or Q19. A simple hash join of lineitem and part was about 90s, with a replicated part hash table. In the profile, the hash probe was 74s, which seems excessive. One would have to single-step through the hash probe to find out what actually happens. Maybe there are prohibitive numbers of collisions, which would throw off the results across the board. We would have to ask the Impala community about this.

Anyway, Impala experts out there are invited to set the record straight. We have attached the results and the output of the Impala profile statement for each query for the single stream run. impala_stream0.zip contains the evidence for the single-stream run; impala-stream1-5.zip holds the 5-stream run.

To be more Big Data-like, we should probably run with significantly larger data than memory; for example, 3T in 0.5T RAM. At EC2, we could do this with 2 I3.8 instances (6.4T SSD each). With Virtuoso, we'd be done in 8 hours or so, counting 2x for the I/O and 30x for the greater scale (the 100G experiment goes in 8 minutes or so, all included). With Impala, we could be running for weeks, so at the very least we'd like to do this with an Impala expert, to make sure things are done right and will not have to be retried. Some of the hash joins would have to be done in multiple passes and with partitioning.

In subsequent articles, we will look at other players in this space, and possibly some other benchmarks, like the TPC-DS subset that Actian uses to beat Impala.

## July142015

10:15

### Semantic Web Company with LOD2 project top listed at the first EC Innovation Radar

The Innovation Radar is a DG Connect support initiative which focuses on the identification of high potential innovations and the key innovators behind them in FP7, CIP and H2020 projects. The Radar supports the innovators by suggesting a range of targeted actions that can assist them in fulfilling their potential in the market place. The first Innovation Radar Report reviews the innovation potential of ICT projects funded under 7th Framework Programme and the Competitiveness and Innovation Framework Programme. Between May 2014 and January 2015, the Commission reviewed 279 ICT projects, which had resulted in a total of 517 innovations, delivered by 544 organisations in 291 European cities.

Core of the analysis is the Innovation Capacity Indicator (ICI), which measures both the ability of the innovator company and the quality of the environment in which it operates. AND: among this results, SWC has received two top rankings. One for the recently concluded LOD2 project (LOD2 – Creating Knowledge out of Interlinked Data) and another as being one of the key organisations and thereby innovating SMEs within this projects. Also listed are our partners OpenLink Software and Wolters Kluwer Germany.

Ranking of the top 10 innovations and key organisations behind them (Innovation Radar 2015)

We are happy and proud that the report identifies Semantic Web Company as one of those players (10 %) where commercial exploitation of innovations is already ongoing. That strengthens and confirmes our approach to interconnect FP7 and H2020 research and innovation activities with real-world business use cases coming from our customers and partners. Thereby our core product PoolParty Semantic Suite can be taken as a best practice example of embedding collaborative research into an innovation-driven commercial product. For Semantic Web Company, the report is particularly encouraging because of it’s emphasis on the positive role of SMEs, where the report sees 41% of high-potential innovation coming from…

Blogpost by Martin Kaltenböck and Thomas Thurner

## July132015

17:46

### Vectored Execution in Column/Row Stores

This article discusses the relationship between vectored execution and column- and row-wise data representations. Column stores are traditionally considered to be good for big scans but poor at indexed access. This is not necessarily so, though. We take TPC-H Q9 as a starting point, working with different row- and column-wise data representations and index choices. The goal of the article is to provide a primer on the performance implications of different physical designs.

All the experiments are against the TPC-H 100G dataset hosted in Virtuoso on the test system used before in the TPC-H series: dual Xeon E5-2630, 2x6 cores x 2 threads, 2.3GHz, 192 GB RAM. The Virtuoso version corresponds to the feature/analytics branch in the v7fasttrack github project. All run times are from memory, and queries generally run at full platform, 24 concurrent threads.

We note that RDF stores and graph databases usually do not have secondary indices with multiple key parts. However, these do predominantly index-based access as opposed to big scans and hash joins. To explore the impact of this, we have decomposed the tables into projections with a single dependent column, which approximates a triple store or a vertically-decomposed graph database like Sparksee.

So, in these experiments, we store the relevant data four times over, as follows:

• 100G TPC-H dataset in the column-wise schema as discussed in the TPC-H series, now complemented with indices on l_partkey and on l_partkey, l_suppkey

• The same in row-wise data representation

• Column-wise tables with a single dependent column for l_partkey, l_suppkey, l_extendedprice, l_quantity, l_discount, ps_supplycost, s_nationkey, p_name. These all have the original tables primary key, e.g., l_orderkey, l_linenumber for the l_ prefixed tables

• The same with row-wise tables

The column-wise structures are in the DB qualifier, and the row-wise are in the R qualifier. There is a summary of space consumption at the end of the article. This is relevant for scalability, since even if row-wise structures can be faster for scattered random access, they will fit less data in RAM, typically 2 to 3x less. Thus, if "faster" rows cause the working set not to fit, "slower" columns will still win.

As a starting point, we know that the best Q9 is the one in the Virtuoso TPC-H implementation which is described in Part 10 of the TPC-H blog series. This is a scan of lineitem with a selective hash join followed ordered index access of orders, then hash joins against the smaller tables. There are special tricks to keep the hash tables small by propagating restrictions from the probe side to the build side.

The query texts are available here, along with the table declarations and scripts for populating the single-column projections. rs.sql makes the tables and indices, rsload.sql copies the data from the TPC-H tables.

The business question is to calculate the profit from sale of selected parts grouped by year and country of the supplier. This touches most of the tables, aggregates over 1/17 of all sales, and touches at least every page of the tables concerned, if not every row.

SELECT n_name AS nation, EXTRACT(year FROM o_orderdate) AS o_year, SUM (l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity) AS sum_profit FROM lineitem, part, partsupp, orders, supplier, nation WHERE s_suppkey = l_suppkey AND ps_suppkey = l_suppkey AND ps_partkey = l_partkey AND p_partkey = l_partkey AND o_orderkey = l_orderkey AND s_nationkey = n_nationkey AND p_name LIKE '%green%' GROUP BY nation, o_year ORDER BY nation, o_year DESC 
 Query Variants The query variants discussed here are: Hash based, the best plan -- 9h.sql Index based with multicolumn rows, with lineitem index on l_partkey -- 9i.sql, 9ir.sql Index based with multicolumn rows, lineitem index on l_partkey, l_suppkey -- 9ip.sql, 9ipr.sql Index based with one table per dependent column, index on l_partkey -- 9p.sql index based with one table per dependent column, with materialized l_partkey, l_suppkey -> l_orderkey, l_minenumber -- 9pp.sql, 9ppr.sql These are done against row- and column-wise data representations with 3 different vectorization settings. The dynamic vector size starts at 10,000 values in a vector, and adaptively upgrades this to 1,000,000 if it finds that index access is too sparse. Accessing rows close to each other is more efficient than widely scattered rows in vectored index access, so using a larger vector will likely cause a denser, hence more efficient, access pattern. The 10K vector size corresponds to running with a fixed vector size. The Vector 1 sets vector size to 1, effectively running a tuple at a time, which corresponds to a non-vectorized engine. We note that lineitem and its single column projections contain 600M rows. So, a vector of 10K values will hit, on the average, every 60,000th row. A vector of 1,000,000 will thus hit every 600th. This is when doing random lookups that are in no specific order, e.g., getting lineitems by a secondary index on l_partkey. 1 — Hash-based plan Vector Dynamic 10k 1 Column-wise 4.1 s 4.1 s 145   s Row-wise 25.6 s 25.9 s 45.4 s Dynamic vector size has no effect here, as there is no indexed access that would gain from more locality. The column store is much faster because of less memory access (just scan the l_partkey column, and filter this with a Bloom filter; and then hash table lookup to pick only items with the desired part). The other columns are accessed only for the matching rows. The hash lookup is vectored since there are hundreds of compressed l_partkey values available at each time. The row store does the hash lookup row by row, hence losing cache locality and instruction-level parallelism. Without vectorization, we have a situation where the lineitem scan emits one row at a time. Restarting the scan with the column store takes much longer, since 5 buffers have to be located and pinned instead of one for the row store. The row store is thus slowed down less, but it too suffers almost a factor of 2 from interpretation overhead. 2 — Index-based, lineitem indexed on l_partkey Vector Dynamic 10k 1 Column-wise 30.4 s 62.3 s 321   s Row-wise 31.8 s 27.7 s 122   s Here the plan scans part, then partsupp, which shares ordering with part; both are ordered on partkey. Then lineitem is fetched by a secondary index on l_partkey. This produces l_orderkey, l_lineitem, which are used to get the l_suppkey. We then check if the l_suppkey matches the ps_suppkey from partsupp, which drops 3/4 of the rows. The next join is on orders, which shares ordering with lineitem; both are ordered on orderkey. There is a narrow win for columns with dynamic vector size. When access becomes scattered, rows win by 2.5x, because there is only one page to access instead of 1 + 3 for columns. This is compensated for if the next item is found on the same page, which happens if the access pattern is denser. 3 — Index-based, lineitem indexed on L_partkey, l_suppkey Vector Dynamic 10k 1 Column-wise 16.9 s 47.2 s 151   s Row-wise 22.4 s 20.7 s 89   s This is similar to the previous, except that now only lineitems that match ps_partkey, ps_suppkey are accessed, as the secondary index has two columns. Access is more local. Columns thus win more with dynamic vector size. 4 — Decomposed, index on l_partkey Vector Dynamic 10k 1 Column-wise 35.7 s 170   s 601   s Row-wise 44.5 s 56.2 s 130   s Now, each of the l_extendedprice, l_discount, l_quantity and l_suppkey is a separate index lookup. The times are slightly higher but the dynamic is the same. The non-vectored columns case is hit the hardest. 5 — Decomposed, index on l_partkey, l_suppkey Vector Dynamic 10k 1 Column-wise 19.6 s 111   s 257   s Row-wise 32.0 s 37   s 74.9 s Again, we see the same dynamic as with a multicolumn table. Columns win slightly more at long vector sizes because of overall better index performance in the presence of locality. Space Utilization The following tables list the space consumption in megabytes of allocated pages. Unallocated space in database files is not counted. The row-wise table also contains entries for column-wise structures (DB.*) since these have a row-wise sparse index. The size of this is however negligible, under 1% of the column-wise structures. Row-Wise    Column-Wise MB structure 73515 R.DBA.LINEITEM 14768 R.DBA.ORDERS 11728 R.DBA.PARTSUPP 10161 r_lpk_pk 10003 r_l_pksk 9908 R.DBA.l_partkey 8761 R.DBA.l_extendedprice 8745 R.DBA.l_discount 8738 r_l_pk 8713 R.DBA.l_suppkey 6267 R.DBA.l_quantity 2223 R.DBA.CUSTOMER 2180 R.DBA.o_orderdate 2041 r_O_CK 1911 R.DBA.PART 1281 R.DBA.ps_supplycost 811 R.DBA.p_name 127 R.DBA.SUPPLIER 88 DB.DBA.LINEITEM 24 DB.DBA.ORDERS 11 DB.DBA.PARTSUPP 9 R.DBA.s_nationkey 5 l_pksk 4 DB.DBA.l_partkey 4 lpk_pk 4 DB.DBA.l_extendedprice 3 l_pk 3 DB.DBA.l_suppkey 2 DB.DBA.CUSTOMER 2 DB.DBA.l_quantity 1 DB.DBA.PART 1 O_CK 1 DB.DBA.l_discount    MB structure 36482 DB.DBA.LINEITEM 13087 DB.DBA.ORDERS 11587 DB.DBA.PARTSUPP 5181 DB.DBA.l_extendedprice 4431 l_pksk 3072 DB.DBA.l_partkey 2958 lpk_pk 2918 l_pk 2835 DB.DBA.l_suppkey 2067 DB.DBA.CUSTOMER 1618 DB.DBA.PART 1156 DB.DBA.l_quantity 961 DB.DBA.ps_supplycost 814 O_CK 798 DB.DBA.l_discount 724 DB.DBA.p_name 436 DB.DBA.o_orderdate 126 DB.DBA.SUPPLIER 1 DB.DBA.s_nationkey In both cases, the large tables are on top, but the column-wise case takes only half the space due to compression. We note that the single column projections are smaller column-wise. The l_extendedprice is not very compressible hence column-wise takes much more space than l_quantity; the row-wise difference is less. Since the leading key parts l_orderkey, l_linenumber are ordered and very compressible, the column-wise structures are in all cases noticeably more compact. The same applies to the multipart index l_pksk and r_l_pksk (l_partkey, l_suppkey, l_orderkey, l_linenumber) in column- and row-wise representations. Note that STRING columns (e.g., l_comment) are not compressed. If they were, the overall space ratio would be even more to the advantage of the column store. Conclusions Column stores and vectorization inextricably belong together. Column-wise compression yields great gains also for indices, since sorted data is easy to compress. Also for non-sorted data, adaptive use of dictionaries, run lengths, etc., produce great space savings. Columns also win with indexed access if there is locality. Row stores have less dependence on locality, but they also will win by a factor of 3 from dropping interpretation overhead and exploiting join locality. For point lookups, columns lose by 2+x but considering their better space efficiency, they will still win if space savings prevent going to secondary storage. For bulk random access, like in graph analytics, columns will win because of being able to operate on a large vector of keys to fetch. For many workloads, from TPC-H to LDBC social network, multi-part keys are a necessary component of physical design for performance if indexed access predominates. Triple stores and most graph databases do not have such and are therefore at a disadvantage. Self-joining, like in RDF or other vertically decomposed structures, can cost up to a factor of 10-20 over a column-wise multicolumn table. This depends however on the density of access. For analytical workloads, where the dominant join pattern is the scan with selective hash join, column stores are unbeatable, as per common wisdom. There are good physical reasons for this and the row store even with well implemented vectorization loses by a factor of 5. For decomposed structures, like RDF quads or single column projections of tables, column stores are relatively more advantageous because the key columns are extensively repeated, and these compress better with columns than with rows. In all the RDF workloads we have tried, columns never lose, but there is often a draw between rows and columns for lookup workloads. The longer the query, the more columns win. 
 
 #   React 
 
 16:52 Virtuoso at SIGMOD 2015 Two papers presented at SIGMOD 2015 have been added to the Virtuoso Science Library. Orri Erling (OpenLink Software); Alex Averbuch (Neo Technology); Josep Larriba-Pey (Sparsity Technologies); Hassan Chafi (Oracle Labs); Andrey Gubichev (TU Munich); Arnau Prat-Pérez (Universitat Politècnica de Catalunya); Minh-Duc Pham (VU University Amsterdam); Peter Boncz (CWI): The LDBC Social Network Benchmark: Interactive Workload. Proceedings of SIGMOD 2015, Melbourne. This paper is an overview of the challenges posed in the LDBC social network benchmark, from data generation to the interactive workload. Mihai Capotă (Delft University of Technology), Tim Hegeman (Delft University of Technology), Alexandru Iosup (Delft University of Technology), Arnau Prat-Pérez (Universitat Politècnica de Catalunya), Orri Erling (OpenLink Software), Peter Boncz (CWI): Graphalytics: A Big Data Benchmark for Graph-Processing Platforms. Sigmod GRADES 2015. This paper discusses the future evolution of the LDBC Social Network Benchmark and gives a preview of Virtuoso graph traversal performance. #   React 16:16 Big Data, Part 1: Virtuoso Meets Hive In this series, we will look at Virtuoso and some of the big data technologies out there. SQL on Hadoop is of interest, as well as NoSQL technologies. We begin at the beginning, with Hive, the grand-daddy of SQL on Hadoop. The test platform is two Amazon R3.8 AMI instances. We compared Hive with the Virtuoso 100G TPC-H experiment on the same platform, published earlier on this blog. The runs follow a bulk load in both cases, with all data served from memory. The platform has 2x244GB RAM with only 40GB or so of working set. The Virtuoso version and settings are as in the Virtuoso Cluster test AMI. The Hive version is 0.14 from the Hortonworks HDP 2.2 distribution>. The Hive schema and query formulations are the ones from hive-testbench on GitHub. The Hive configuration parameters are as set by Ambari 2.0.1. These are different from the ones in hive-testbench, but the Ambari choices offer higher performance on the platform. We did run statistics with Hive and did not specify any settings not in the hive-testbench. Thus we suppose the query plans were as good as Hive will make them. Platform utilization was even across both machines, and varied between 30% and 100% of the 2 x 32 hardware threads. Load time with Hive was 742 seconds against 232 seconds with Virtuoso. In both cases, this was a copy from 32 CSV files into native database format; for Hive, this is ORC (Optimized Row Columnar). In Virtuoso, there is one index, (o_custkey); in Hive, there are no indices. Query Virtuoso Hive Notes — 332     s 742     s Data Load Q1 1.098 s 296.636 s Q2 0.187 s >3600     s Hive Timeout Q3 0.761 s 98.652 s Q4 0.205 s 147.867 s Q5 0.808 s 114.782 s Q6 2.403 s 71.789 s Q7 0.59  s 394.201 s Q8 0.775 s >3600     s Hive Timeout Q9 1.836 s >3600     s Hive Timeout Q10 3.165 s 179.646 s Q11 1.37  s 43.094 s Q12 0.356 s 101.193 s Q13 2.233 s 208.476 s Q14 0.488 s 89.047 s Q15 0.72 s 136.431 s Q16 0.814 s 105.652 s Q17 0.681 s 255.848 s Q18 1.324 s 337.921 s Q19 0.417 s >3600     s Hive Timeout Q20 0.792 s 193.965 s Q21 0.720 s 670.718 s Q22 0.155 s 68.462 s Hive does relatively best on bulk load. This is understandable since this is a sequential read of many files in parallel with just compression to do. Hive's query times are obviously affected by not having a persistent memory image of the data, as this is always streamed from the storage files into other files as MapReduce intermediate results. This seems to be an operator-at-a-time business as opposed to Virtuoso's vectorized streaming. The queries that would do partitioned hash joins (e.g., Q9) did not finish under an hour in Hive, so we do not have a good metric of a cross-partition hash join. One could argue that one should benchmark Hive only in disk-bound circumstances. We may yet get to this. Our next stop will probably be Impala, which ought to do much better than Hive, as it dose not have the MapReduce overheads. If you are a Hive expert and believe that Hive should have done much better, please let us know how to improve the Hive scores, and we will retry. #   React 11:40 Semantic Records Management based on Controlled Vocabularies State-of-the-art Records Management deserves better tools to support more precise data gathering and information retrieval. Collaborative workflows and metadata of highest possible accuracy plays a crucial role in any content architecture, which supports the following scenarios and requirements: Controlled Vocabularies: Metadata remains permanently consistent Semantic Search: Retrieval of records is highly precise by making use of semantic technologies Knowledge Management: Overall system supports collaborative workflows Agility: Metadata layer can be expanded at any time and grows with requirements With PoolParty Semantic Suite, integrated with a system like Confluence, the above mentioned requirements can be fulfilled. Based on a demo system, which is about collecting news, we take a look at the fundamental functionalities of a Semantic Records Management System like this. Step 1: Based on a predefined content structure, users can gather information for any new record. Highest possible data consistency and quality is guaranteed from square one, since any value for a metadata field is derived from a thesaurus (controlled vocabulary). Users are well supported by an autocomplete function. Different valid names for the same ‘thing’ can be used without harming the highly consistent data structure that results from this approach. Step 2: New metadata values can be inserted into the controlled vocabulary to become part of it Step 3: Semantic search helps to retrieve records and find information with highest possible precision. Some of the most powerful search functionalities are available, as for example: faceted search autocomplete and query expansion search based on tag clouds similarity search and content recommendation Step 4: Collaborative tools (which are made available through Atlassian Confluence, which is the basic system of this application) support teams of knowledge workers to share insights, e.g. share this record, like it, watch this, export as PDF/DOC, etc. This small example application demonstrates the power of semantically enhanced records management. It can be used for various scenarios such as: competitive intelligence help desk systems incident management contractor management technical documentation etc. Interested in Semantic Records Management? See the system in action. Get in contact with us and we will arrange a web meeting.   #   React June 29 2015 19:36 Rethink Big and Europe?s Position in Big Data I will here take a break from core database and talk a bit about EU policies for research funding. I had lunch with Stefan Manegold of CWI last week, where we talked about where European research should go. Stefan is involved in RETHINK big, a European research project for compiling policy advice regarding big data for EC funding agencies. As part of this, he is interviewing various stakeholders such as end user organizations and developers of technology. RETHINK big wants to come up with a research agenda primarily for hardware, anything from faster networks to greener data centers. CWI represents software expertise in the consortium. So, we went through a regular questionnaire about how we see the landscape. I will summarize this below, as this is anyway informative. Core competence My own core competence is in core database functionality, specifically in high performance query processing, scale-out, and managing schema-less data. Most of the Virtuoso installed base is in the RDF space, but most potential applications are in fact outside of this niche. User challenges The life sciences vertical is the one in which I have the most application insight, from going to Open PHACTS meetings and holding extensive conversations with domain specialists. We have users in many other verticals, from manufacturing to financial services, but there I do not have as much exposure to the actual applications. Having said this, the challenges throughout tend to be in diversity of data. Every researcher has their MySQL database or spreadsheet, and there may not even be a top level catalogue of everything. Data formats are diverse. Some people use linked data (most commonly RDF) as a top level metadata format. The application data, such as gene sequences or microarray assays, reside in their native file formats and there is little point in RDF-izing these. There are also public data resources that are published in RDF serializations as vendor-neutral, self-describing format. Having everything as triples, without a priori schema, makes things easier to integrate and in some cases easier to describe and query. So, the challenge is in the labor intensive nature of data integration. Data comes with different levels of quantity and quality, from hand-curated to NLP extractions. Querying in the single- or double-digit terabyte range with RDF is quite possible, as we have shown many times on this blog, but most use cases do not even go that far. Anyway, what we see on the field is primarily a data diversity game. The scenario is data integration; the technology we provide is database. The data transformation proper, data cleansing, units of measure, entity de-duplication, and such core data-integration functions are performed using diverse, user-specific means. Jerven Bolleman of the Swiss Institute of Bioinformatics is a user of ours with whom we have long standing discussions on the virtues of federated data and querying. I advised Stefan to go talk to him; he has fresh views about the volume challenges with unexpected usage patterns. Designing for performance is tough if the usage pattern is out of the blue, like correlating air humidity on the day of measurement with the presence of some genomic patterns. Building a warehouse just for that might not be the preferred choice, so the problem field is not exhausted. Generally, I’d go for warehousing though. What technology would you like to have? Network or power efficiency? OK. Even a fast network is a network. A set of processes on a single shared-memory box is also a kind of network. InfiniBand is maybe half the throughput and 3x the latency of single threaded interprocess communication within one box. The operative word is latency. Making large systems always involves a network or something very much like one in large scale-up scenarios. On the software side, next to nobody understands latency and contention; yet these are the one core factor in any pursuit of scalability. Because of this situation, paradigms like MapReduce and bulk synchronous parallel (BSP) processing have become popular because these take the communication out of the program flow, so the programmer cannot muck this up, as otherwise would happen with the inevitability of destiny. Of course, our beloved SQL or declarative query in general does give scalability in many tasks without programmer participation. Datalog has also been used as a means of shipping computation around, as in the the work of Hellerstein. There are no easy solutions. We have built scale-out conscious, vectorized extensions to SQL procedures where one can express complex parallel, distributed flows, but people do not use or understand these. These are very useful, even indispensable, but only on the inside, not as a programmer-facing construct. MapReduce and BSP are the limit of what a development culture will absorb. MapReduce and BSP do not hide the fact of distributed processing. What about things that do? Parallel, partitioned extensions to Fortran arrays? Functional languages? I think that all the obvious aids to parallel/distributed programming have been conceived of. No silver bullet; just hard work. And above all the discernment of what paradigm fits what problem. Since these are always changing, there is no finite set of rules, and no substitute for understanding and insight, and the latter are vanishingly scarce. "Paradigmatism," i.e., the belief that one particular programming model is a panacea outside of its original niche, is a common source of complexity and inefficiency. This is a common form of enthusiastic naïveté. If you look at power efficiency, the clusters that are the easiest to program consist of relatively few high power machines and a fast network. A typical node size is 16+ cores and 256G or more RAM. Amazon has these in entirely workable configurations, as documented earlier on this blog. The leading edge in power efficiency is in larger number of smaller units, which makes life again harder. This exacerbates latency and forces one to partition the data more often, whereas one can play with replication of key parts of data more freely if the node size is larger. One very specific item where research might help without having to rebuild the hardware stack would be better, lower-latency exposure of networks to software. Lightweight threads and user-space access, bypassing slow protocol stacks, etc. MPI has some of this, but maybe more could be done. So, I will take a cluster of such 16-core, 256GB machines on a faster network, over a cluster of 1024 x 4G mobile phones connected via USB. Very selfish and unecological, but one has to stay alive and life is tough enough as is. Are there pressures to adapt business models based on big data? The transition from capex to opex may be approaching maturity, as there have been workable cloud configurations for the past couple of years. The EC2 from way back, with at best a 4 core 16G VM and a horrible network for $2/hr, is long gone. It remains the case that 4 months of 24x7 rent in the cloud equals the purchase price of physical hardware. So, for this to be economical long-term at scale, the average utilization should be about 10% of the peak, and peaks should not be on for more than 10% of the time. So, database software should be rented by the hour. A 100-150% markup for the$2.80 a large EC2 instance costs would be reasonable. Consider that 70% of the cost in TPC benchmarks is database software. There will be different pricing models combining different up-front and per-usage costs, just as there are for clouds now. If the platform business goes that way and the market accepts this, then systems software will follow. Price/performance quotes should probably be expressed as speed/price/hour instead of speed/price. The above is rather uncontroversial but there is no harm restating these facts. Reinforce often. Well, the question is raised, what should Europe do that would have tangible impact in the next 5 years? This is a harder question. There is some European business in wide area and mobile infrastructures. Competing against Huawei will keep them busy. Intel and Mellanox will continue making faster networks regardless of European policies. Intel will continue building denser compute nodes, e.g., integrated Knight’s Corner with dual IB network and 16G fast RAM on chip. Clouds will continue making these available on demand once the technology is in mass production. What’s the next big innovation? Neuromorphic computing? Quantum computing? Maybe. For now, I’d just do more engineering along the core competence discussed above, with emphasis on good marketing and scalable execution. By this I mean trained people who know something about deployment. There is a huge training gap. In the would-be "Age of Data," knowledge of how things actually work and scale is near-absent. I have offered to do some courses on this to partners and public alike, but I need somebody to drive this show; I have other things to do. I have been to many, many project review meetings, mostly as a project partner but also as reviewer. For the past year, the EC has used an innovation questionnaire at the end of the meetings. It is quite vague, and I don’t think it delivers much actionable intelligence. What would deliver this would be a venture capital type activity, with well-developed networks and active participation in developing a business. The EC is not now set up to perform this role, though. But the EC is a fairly large and wealthy entity, so it could invest some money via this type of channel. Also there should be higher individual incentives and rewards for speed and excellence. Getting the next Horizon 2020 research grant may be good, but better exists. The grants are competitive enough and the calls are not bad; they follow the times. In the projects I have seen, productization does get some attention, e.g., the LOD2 stack, but it is not something that is really ongoing or with dedicated commercial backing. It may also be that there is no market to justify such dedicated backing. Much of the RDF work has been "me, too" — let’s do what the real database and data integration people do, but let’s just do this with triples. Innovation? Well, I took the best of the real DB world and adapted this to RDF, which did produce a competent piece of work with broad applicability, extending outside RDF. Is there better than this? Well, some of the data integration work (e.g., LIMES) is not bad, and it might be picked up by some of the players that do this sort of thing in the broader world, e.g., Informatica, the DI suites of big DB vendors, Tamr, etc. I would not know if this in fact adds value to the non-RDF equivalents; I do not know the field well enough, but there could be a possibility. The recent emphasis for benchmarking, spearheaded by Stefano Bertolo is good, as exemplified by the LDBC FP7. There should probably be one or two projects of this sort going at all times. These make challenges known and are an effective means of guiding research, with a large multiplier: Once a benchmark gets adopted, infinitely more work goes into solving the problem than in stating it in the first place. The aims and calls are good. The execution by projects is variable. For 1% of excellence, there apparently must be 99% of so-and-so, but this is just a fact of life and not specific to this context. The projects are rather diffuse. There is not a single outcome that gets all the effort. In this, the level of engagement of participants is less and focus is much more scattered than in startups. A really hungry, go-getter mood is mostly absent. I am a believer in core competence. Well, most people will agree that core competence is nice. But the projects I have seen do not drive for it hard enough. It is hard to say exactly what kinds of incentives could be offered to encourage truly exceptional work. The American startup scene does offer high rewards and something of this could be transplanted into the EC project world. I would not know exactly what form this could take, though. #   React June 28 2015 09:08 Improved Customer Experience by use of Semantic Web and Linked Data technologies With the rise of Linked Data technologies, there come several new approaches into play for the improvement of customer experience across all digital channels of a company. All of these methodologies can be subsumed under the term “the connected customer”. These are interesting not only for retailers operating a web shop, but also for enterprises seeking for new ways to develop tailor-made customer services and to increase customer retention. Linked Data methodologies can help to improve several measurements alongside a typical customer experience lifecycle. Personalized access to information, e.g. to technical documentation Cross-selling through a better contextualization of product information Semantically enhanced help desk, user forums and self service platforms Better ways to understand and interpret a customer intention by use of enterprise vocabularies More dynamic management of complex multi-channel websites through a better cost-effectiveness More precise methods for data analytics, e.g. to allow marketers to better target campaigns and content to the user’s preferences Enhanced search experience at aggregators like Google through the use of microdata and schema.org … In the center of this approach, knowledge graphs work like a ‘linking machine’. Based on standards-based semantic models, business entities are getting linked in a most dynamic way. Those graphs go beyond the power of social graphs. While social graphs are focused on people only, are knowledge graphs connecting all kinds of relevant business objects to each other. When customers and their behaviours are represented in a knowledge model, Linked data technologies try to preserve as much semantics as possible. By these means they are able to complement other approaches for big data analytics, which rather tend to flatten out the data model behind business entities. #   React June 26 2015 13:15 Using SPARQL clause VALUES in PoolParty Since PoolParty fully supports SPARQL 1.1 functionalities you can use clauses like VALUES. The VALUES clause can be used to provide an unordered solution sequence that is joined with the results of the query evaluation. From my perspective it is a convenience of filtering variables and an increase in readability of queries. E.g. when you want to know which cocktails you can create with Gin and a highball glass you can go to http://vocabulary.semantic-web.at/PoolParty/sparql/cocktails and fire this query: PREFIX skos:<http://www.w3.org/2004/02/skos/core#> PREFIX co: <http://vocabulary.semantic-web.at/cocktail-ontology/> SELECT ?cocktailLabel WHERE {   ?cocktail co:consists-of ?ingredient ;     co:uses ?drinkware ;     skos:prefLabel ?cocktailLabel .   ?ingredient skos:prefLabel ?ingredientLabel .   ?drinkware skos:prefLabel ?drinkwareLabel .   FILTER (?ingredientLabel = "Gin"@en && ?drinkwareLabel = "Highball glass"@en ) } When you want to add additional pairs of ingredients and drink ware you want to filter in combination the query gets quite clumsy. Wrongly placed braces can break the syntax. In addition, when writing complicated queries you easily insert errors, e.g. by mixing boolean operators which results in wrong results… ... FILTER ((?ingredientLabel = "Gin"@en && ?drinkwareLabel = "Highball glass"@en ) ||      (?ingredientLabel = "Vodka"@en && ?drinkwareLabel ="Old Fashioned glass"@en )) } Using VALUES can help in this situation. For example this query shows you how to filter both pairs Gin+Highball glass and Vodka+Old Fashioned glass in a neat way: PREFIX skos:<http://www.w3.org/2004/02/skos/core#> PREFIX co: <http://vocabulary.semantic-web.at/cocktail-ontology/> SELECT ?cocktailLabel WHERE {   ?cocktail co:consists-of ?ingredient ;     co:uses ?drinkware ;     skos:prefLabel ?cocktailLabel .   ?ingredient skos:prefLabel ?ingredientLabel .   ?drinkware skos:prefLabel ?drinkwareLabel . } VALUES ( ?ingredientLabel ?drinkwareLabel ) {   ("Gin"@en "Highball glass"@en)   ("Vodka"@en "Old Fashioned glass"@en) } Especially when you create SPARQL code automatically, e.g. generated by a form, this clause can be very useful.   #   React June 16 2015 21:53 Virtuoso Elastic Cluster Benchmarks AMI on Amazon EC2 We have another new Amazon machine image, this time for deploying your own Virtuoso Elastic Cluster on the cloud. The previous post gave a summary of running TPC-H on this image. This post is about what the AMI consists of and how to set it up. Note: This AMI is running a pre-release build of Virtuoso 7.5, Commercial Edition. Features are subject to change, and this build is not licensed for any use other than the AMI-based benchmarking described herein. There are two preconfigured cluster setups; one is for two (2) machines/instances and one is for four (4). Generation and loading of TPC-H data, as well as the benchmark run itself, is preconfigured, so you can do it by entering just a few commands. The whole sequence of doing a terabyte (1000G) scale TPC-H takes under two hours, with 30 minutes to generate the data, 35 minutes to load, and 35 minutes to do three benchmark runs. The 100G scale is several times faster still. To experiment with this AMI, you will need a set of license files, one per machine/instance, which our Sales Team can provide. Detailed instructions are on the AMI, in /home/ec2-user/cluster_instructions.txt, but the basic steps to get up and running are as follows: Instantiate machine image ami-811becea) (AMI ID is subject to change; you should be able to find the latest by searching for "OpenLink Virtuoso Benchmarks" in "Community AMIs"; this one is short-named virtuoso-bench-cl) with two or four (2 or 4) R3.8xlarge instances within one virtual private cluster and placement group. Make sure the VPC security is set to allow all connections. Log in to the first, and fill in the configuration file with the internal IP addresses of all machines instantiated in step 1. Distribute the license files to the instances, and start the OpenLink License Manager on each machine. Run 3 shell commands to set up the file systems and the Virtuoso configuration files. If you do not plan to run one of these benchmarks, you can simply start and work with the Virtuoso cluster now. It is ready for use with an empty database. Before running one of these benchmark, generate the appropriate dataset with the dbgen.sh command. Bulk load the data with load.sh. Run the benchmark with run.sh. Right now the cluster benchmarks are limited to TPC-H but cluster versions of the LDBC Social Network and Semantic Publishing benchmarks will follow soon. #   React June 11 2015 08:51 → blog.exalead.com And what about the workers? #   React June 10 2015 16:03 In Hoc Signo Vinces (part 21 of n): Running TPC-H on Virtuoso Elastic Cluster on Amazon EC2 We have made an Amazon EC2 deployment of Virtuoso 7 Commercial Edition, configured to use the Elastic Cluster Module with TPC-H preconfigured, similar to the recently published OpenLink Virtuoso Benchmark AMI running the Open Source Edition. The details of the new Elastic Cluster AMI and steps to use it will be published in a forthcoming post. Here we will simply look at results of running TPC-H 100G scale on two machines, and 1000G scale on four machines. This shows how Virtuoso provides great performance on a cloud platform. The extremely fast bulk load — 33 minutes for a terabyte! — means that you can get straight to work even with on-demand infrastructure. In the following, the Amazon instance type is R3.8xlarge, each with dual Xeon E5-2670 v2, 244G RAM, and 2 x 300G SSD. The image is made from the Amazon Linux with built-in network optimization. We first tried a RedHat image without network optimization and had considerable trouble with the interconnect. Using network-optimized Amazon Linux images inside a virtual private cloud has resolved all these problems. The network optimized 10GE interconnect at Amazon offers throughput close to the QDR InfiniBand running TCP-IP; thus the Amazon platform is suitable for running cluster databases. The execution that we have seen is not seriously network bound. 100G on 2 machines, with a total of 32 cores, 64 threads, 488 GB RAM, 4 x 300 GB SSD Load time: 3m 52s Run Power Throughput Composite 1 523,554.3 590,692.6 556,111.2 2 565,353.3 642,503.0 602,694.9 1000G on 4 machines, with a total of 64 cores, 128 threads, 976 GB RAM, 8 x 300 GB SSD Load time: 32m 47s Run Power Throughput Composite 1 592,013.9 754,107.6 668,163.3 2 896,564.1 828,265.4 861,738.4 3 883,736.9 829,609.0 856,245.3 For the larger scale we did 3 sets of power + throughput tests to measure consistency of performance. By the TPC-H rules, the worst (first) score should be reported. Even after bulk load, this is markedly less than the next power score due to working set effects. This is seen to a lesser degree with the first throughput score also. The numerical quantities summaries are available in a report.zip file, or individually -- report-100-1.txt report-100-2.txt report-1000-1.txt report-1000-2.txt report-1000-3.txt Subsequent posts will explain how to deploy Virtuoso Elastic Clusters on AWS. In Hoc Signo Vinces (TPC-H) Series In Hoc Signo Vinces (part 1): Virtuoso meets TPC-H In Hoc Signo Vinces (part 2): TPC-H Schema Choices In Hoc Signo Vinces (part 3): Benchmark Configuration Settings In Hoc Signo Vinces (part 4): Bulk Load and Refresh In Hoc Signo Vinces (part 5): The Return of SQL Federation In Hoc Signo Vinces (part 6): TPC-H Q1 and Q3: An Introduction to Query Plans In Hoc Signo Vinces (part 7): TPC-H Q13: The Good and the Bad Plans In Hoc Signo Vinces (part 8): TPC-H: INs, Expressions, ORs In Hoc Signo Vinces (part 9): TPC-H Q18, Ordered Aggregation, and Top K In Hoc Signo Vinces (part 10): TPC-H Q9, Q17, Q20 - Predicate Games In Hoc Signo Vinces (part 11): TPC-H Q2, Q10 - Late Projection In Hoc Signo Vinces (part 12): TPC-H: Result Preview In Hoc Signo Vinces (part 13): Virtuoso TPC-H Kit Now on V7 Fast Track In Hoc Signo Vinces (part 14): Virtuoso TPC-H Implementation Analysis In Hoc Signo Vinces (part 15): TPC-H and the Science of Hash In Hoc Signo Vinces (part 16): Introduction to Scale-Out In Hoc Signo Vinces (part 17): 100G and 300G Runs on Dual Xeon E5 2650v2 In Hoc Signo Vinces (part 18): Cluster Dynamics In Hoc Signo Vinces (part 19): Scalability, 1000G, and 3000G In Hoc Signo Vinces (part 20): 100G and 1000G With Cluster; When is Cluster Worthwhile; Effects of I/O In Hoc Signo Vinces (part 21): Running TPC-H on Virtuoso Cluster on Amazon EC2 (this post) #   React June 09 2015 15:51 Introducing the OpenLink Virtuoso Benchmarks AMI on Amazon EC2 The OpenLink Virtuoso Benchmarks AMI is an Amazon EC2 machine image with the latest Virtuoso open source technology preconfigured to run — TPC-H , the classic of SQL data warehousing LDBC SNB, the new Social Network Benchmark from the Linked Data Benchmark Council LDBC SPB, the RDF/SPARQL Semantic Publishing Benchmark from LDBC This package is ideal for technology evaluators and developers interested in getting the most performance out of Virtuoso. This is also an all-in-one solution to any questions about reproducing claimed benchmark results. All necessary tools for building and running are included; thus any developer can use this model installation as a starting point. The benchmark drivers are preconfigured with appropriate settings, and benchmark qualification tests can be run with a single command. The Benchmarks AMI includes a precompiled, preconfigured checkout of the v7fasttrack github repository, checkouts of the github repositories of the benchmarks, and a number of running directories with all configuration files preset and optimized. The image is intended to be instantiated on a R3.8xlarge Amazon instance with 244G RAM, dual Xeon E5-2670 v2, and 600G SSD. Benchmark datasets and preloaded database files can be downloaded from S3 when large, and generated as needed on the instance when small. As an alternative, the instance is also set up to do all phases of data generation and database bulk load. The following benchmark setups are included: TPC-H 100G TPC-H 300G LDBC SNB Validation LDBC SNB Interactive 100G LDBC SNB Interactive 300G (SF3) LDBC SPB Validation LDBC SPB Basic 256 Mtriples (SF5) LDBC SPB Basic 1 Gtriple The AMI will be expanded as new benchmarks are introduced, for example, the LDBC Social Network Business Intelligence or Graph Analytics. To get started: Instantiate machine image ami-5304ef38 (AMI ID is subject to change; you should be able to find the latest by searching for "OpenLink Virtuoso Benchmarks" in "Community AMIs") with a R3.8xlarge instance. Connect via ssh. See the README (also found in the ec2-user's home directory) for full instructions on getting up and running. #   React 15:24 SNB Interactive, Part 3: Choke Points and Initial Run on Virtuoso In this post we will look at running the LDBC SNB on Virtuoso. First, let's recap what the benchmark is about: fairly frequent short updates, with no update contention worth mentioning short random lookups medium complex queries centered around a person's social environment The updates exist so as to invalidate strategies that rely too heavily on precomputation. The short lookups exist for the sake of realism; after all, an online social application does lookups for the most part. The medium complex queries are to challenge the DBMS. The DBMS challenges have to do firstly with query optimization, and secondly with execution with a lot of non-local random access patterns. Query optimization is not a requirement, per se, since imperative implementations are allowed, but we will see that these are no more free of the laws of nature than the declarative ones. The workload is arbitrarily parallel, so intra-query parallelization is not particularly useful, if also not harmful. There are latency constraints on operations which strongly encourage implementations to stay within a predictable time envelope regardless of specific query parameters. The parameters are a combination of person and date range, and sometimes tags or countries. The hardest queries have the potential to access all content created by people within 2 steps of a central person, so possibly thousands of people, times 2000 posts per person, times up to 4 tags per post. We are talking in the millions of key lookups, aiming for sub-second single-threaded execution. The test system is the same as used in the TPC-H series: dual Xeon E5-2630, 2x6 cores x 2 threads, 2.3GHz, 192 GB RAM. The software is the feature/analytics branch of v7fasttrack, available from www.github.com. The dataset is the SNB 300G set, with: 1,136,127 persons 125,249,604 knows edges 847,886,644 posts , including replies 1,145,893,841 tags of posts or replies 1,140,226,235 likes of posts or replies As an initial step, we run the benchmark as fast as it will go. We use 32 threads on the driver side for 24 hardware threads. Below are the numerical quantities for a 400K operation run after 150K operations worth of warmup. Duration: 10:41.251 Throughput: 623.71 (op/s) The statistics that matter are detailed below, with operations ranked in order of descending client-side wait-time. All times are in milliseconds. % of total total_wait name count mean min max 20     % 4,231,130 LdbcQuery5 656 6,449.89    245 10,311 11     % 2,272,954 LdbcQuery8 18,354 123.84    14 2,240 10     % 2,200,718 LdbcQuery3 388 5,671.95    468 17,368 7.3   % 1,561,382 LdbcQuery14 1,124 1,389.13    4 5,724 6.7   % 1,441,575 LdbcQuery12 1,252 1,151.42    15 3,273 6.5   % 1,396,932 LdbcQuery10 1,252 1,115.76    13 4,743 5     % 1,064,457 LdbcShortQuery3PersonFriends 46,285 22.9979  0 2,287 4.9   % 1,047,536 LdbcShortQuery2PersonPosts 46,285 22.6323  0 2,156 4.1   % 885,102 LdbcQuery6 1,721 514.295   8 5,227 3.3   % 707,901 LdbcQuery1 2,117 334.389   28 3,467 2.4   % 521,738 LdbcQuery4 1,530 341.005   49 2,774 2.1   % 440,197 LdbcShortQuery4MessageContent 46,302 9.50708 0 2,015 1.9   % 407,450 LdbcUpdate5AddForumMembership 14,338 28.4175  0 2,008 1.9   % 405,243 LdbcShortQuery7MessageReplies 46,302 8.75217 0 2,112 1.9   % 404,002 LdbcShortQuery6MessageForum 46,302 8.72537 0 1,968 1.8   % 387,044 LdbcUpdate3AddCommentLike 12,659 30.5746  0 2,060 1.7   % 361,290 LdbcShortQuery1PersonProfile 46,285 7.80577 0 2,015 1.6   % 334,409 LdbcShortQuery5MessageCreator 46,302 7.22234 0 2,055 1     % 220,740 LdbcQuery2 1,488 148.347   2 2,504 0.96  % 205,910 LdbcQuery7 1,721 119.646   11 2,295 0.93  % 198,971 LdbcUpdate2AddPostLike 5,974 33.3062  0 1,987 0.88  % 189,871 LdbcQuery11 2,294 82.7685  4 2,219 0.85  % 182,964 LdbcQuery13 2,898 63.1346  1 2,201 0.74  % 158,188 LdbcQuery9 78 2,028.05    1,108 4,183 0.67  % 143,457 LdbcUpdate7AddComment 3,986 35.9902  1 1,912 0.26  % 54,947 LdbcUpdate8AddFriendship 571 96.2294  1 988 0.2   % 43,451 LdbcUpdate6AddPost 1,386 31.3499  1 2,060 0.0086% 1,848 LdbcUpdate4AddForum 103 17.9417  1 65 0.0002% 44 LdbcUpdate1AddPerson 2 22       10 34 At this point we have in-depth knowledge of the choke points the benchmark stresses, and we can give a first assessment of whether the design meets its objectives for setting an agenda for the coming years of graph database development. The implementation is well optimized in general but still has maybe 30% room for improvement. We note that this is based on a compressed column store. One could think that alternative data representations, like in-memory graphs of structs and pointers between them, are better for the task. This is not necessarily so; at the least, a compressed column store is much more space efficient. Space efficiency is the root of cost efficiency, since as soon as the working set is not in memory, a random access workload is badly hit. The set of choke points (technical challenges) actually revealed by the benchmark is so far as follows: Cardinality estimation under heavy data skew — Many queries take a tag or a country as a parameter. The cardinalities associated with tags vary from 29M posts for the most common to 1 for the least common. Q6 has a common tag (in top few hundred) half the time and a random, most often very infrequent, one the rest of the time. A declarative implementation must recognize the cardinality implications from the literal and plan accordingly. An imperative one would have to count. Missing this makes Q6 take about 40% of the time instead of 4.1% when adapting. Covering indices — Being able to make multi-column indices that duplicate some columns from the table often saves an entire table lookup. For example, an index on post by author can also contain the post's creation date. Multi-hop graph traversal — Most queries access a two-hop environment starting at a person. Two queries look for shortest paths of unbounded length. For the two-hop case, it makes almost no difference whether this is done as a union or a special graph traversal operator. For shortest paths, this simply must be built into the engine; doing this client-side incurs prohibitive overheads. A bidirectional shortest path operation is a requirement for the benchmark. Top K — Most queries returning posts order results by descending date. Once there are at least k results, anything older than the kth can be dropped, adding a date selection as early as possible in the query. This interacts with vectored execution, so that starting with a short vector size more rapidly produces an initial top k. Late projection — Many queries access several columns and touch millions of rows but only return a few. The columns that are not used in sorting or selection can be retrieved only for the rows that are actually returned. This is especially useful with a column store, as this removes many large columns (e.g., text of a post) from the working set. Materialization — Q14 accesses an expensive-to-compute edge weight, the number of post-reply pairs between two people. Keeping this precomputed drops Q14 from the top place. Other materialization would be possible, for example Q2 (top 20 posts by friends), but since Q2 is just 1% of the load, there is no need. One could of course argue that this should be 20x more frequent, in which case there could be a point to this. Concurrency control — Read-write contention is rare, as updates are randomly spread over the database. However, some pages get read very frequently, e.g., some middle level index pages in the post table. Keeping a count of reading threads requires a mutex, and there is significant contention on this. Since the hot set can be one page, adding more mutexes does not always help. However, hash partitioning the index into many independent trees (as in the case of a cluster) helps for this. There is also contention on a mutex for assigning threads to client requests, as there are large numbers of short operations. In subsequent posts, we will look at specific queries, what they in fact do, and what their theoretical performance limits would be. In this way we will have a precise understanding of which way SNB can steer the graph DB community. SNB Interactive Series SNB Interactive, Part 1: What is SNB Interactive Really About? SNB Interactive, Part 2: Modeling Choices SNB Interactive, Part 3: Choke Points and Initial Run on Virtuoso (this post) #   React 
 document.observe('dom:loaded', function() { SOUP.Public.fixup(\$('first_batch')); }); Older posts are this way If this message doesn't go away, click anywhere on the page to continue loading posts. Note: If you enable JavaScript, we will load old posts automatically Could not load more posts Maybe Soup is currently being updated? I'll try again automatically in a few seconds... Just a second, loading more posts... You've reached the end.   
 More → 
 
 
 
 
 
 
 
 _qoptions = { qacct:"p-e4h_-nY6rysa2" }; SOUP.Public.actionbar_enabled = true; SOUP.Endless.init(); Don't be the product, buy the product! YES, I want to SOUP ●UP for ... A Tip Of The Hat USD 4 monthly subscription or one time payment cancelable any time "Tell the chef, the beer is on me." As Long As It's Yummy' USD 35 yearly subscription or one time payment cancelable any time "Basically the price of a night on the town!" Infinity21  of  200  sold USD 199.99999... lifetime subscription one time payment limm→∞134m=0! "I'd love to help kickstart continued development! And 0 EUR/month really does make fiscal sense too... maybe I'll even get a shirt?" (there will be limited edition shirts for two and other goodies for each supporter as soon as we sold the 200) All plans include: No ads displayed on the blog ... or to the blogs user Exclusive servers for faster access in peak times, for you and your visitors Reliable feed imports with shorter intervals! Priority support and feedback! Magical feature suggestion powers 100% of the revenue will go into development PLUS: you're helping to develop a user oriented platform! SOUP.interstitial = {} console.debug("miau") SOUP.interstitial.show_up = function() { console.debug("up"); document.getElementById('payuptint').className=""; document.getElementById('payupinterstitial').className=""; } SOUP.interstitial.show_down = function() { console.debug("down"); document.getElementById('payuptint').className="inactive"; document.getElementById('payupinterstitial').className="inactive"; } SOUP.interstitial.hash_changed = function () { if (window.location.hash.indexOf("showup") != -1) { SOUP.interstitial.show_up(); } else { SOUP.interstitial.show_down(); } } window.addEventListener("hashchange", SOUP.interstitial.hash_changed); SOUP.interstitial.hash_changed(); console.debug("ex");