OK. Sorry if it becomes boring, but let me reframe it again and explain bit better. I develop the current situation with some new experiments with MySQL in Toolforge.

I create the datasets named CCC from the WCDO project. I use:
a) Data from the Wikipedia mysql mediawiki databases replicas from each language edition.
b) Data from Wikidata.

In regards to a) there is no specific problem other than the general performance issues that are possible to handle. In regards to b) we are looking for an optimized solution because the datasets cannot be generated.

So, in b) I need to revise around 7 groups of properties for Wikidata items. I select the 'good data' to create the CCC datasets according to the properties they have, their relationships and some other criteria. So I need this data to be able to be read and processed several times.

I first tried with the API (SPARQLE) to obtain the Wikidata qitems and it was not viable. Too much info. Too many queries. Too many properties.

I later tried with the Wikidata dump because it needed to go through all the items. So I created a 'cleaned' version of the dump and went from 30 GB compressed to 6 GB in a sqlite3 database file.
Sqlite3 has the advantages of being a database (so I can select info), it is fast but it is no good at concurrency (I don't care about that).

I created a sqlite3 file in my laptop with the wikidata data in. One table named sitelinks (page_title, langcode, qitems) and the rest of tables with the qitems triplets (qitem1, property, qitem2). This way I can filter by languagecode with an inner join. This allowed me to do all the operations.

When moving from my laptop to tool labs grid I find some bottlenecks.

1. If I want to create the sqlite3 file from the wikidata dump (whether I download it to the tool directory or I retrieved from somewhere else), it goes from 4h (time of parsing and storing in my laptop to 2 days).

This happens because the file is in NFS and the job is somewhere else and read-write speed is not good in this scenario.

@Chicocvenancio suggested me not to use the sqlite3 file but to introduce the data into the mysql user-created tables.
I use these tables in a), with the data selected from the replicas, for the final dataset creation (when data is verified as good) and I also use them for final 'results' that are going to be depicted in the website/wp.
I create new mysql tables to store the parsed dump as I did in the sqlite3.

2. At this point, I come across the mysql bottlenecks. Parsing the wikidata dump and storing it to the new mysql tables takes 4 days.

Chicocvenancio points out that I could optimize the code and insert by batches. This is something I do with the verified data comiong from a) but I didn't think of doing it for b) since I kept the code I used for sqlite3 the same, and in that case writing to disk did not matter. This could be done.

The problem appears when I want to retrieve the data recently stored in the mysql tables. A simple query that uses two tables (sitelinks) and geolocation property takes 15-20 min. 'SELECT * FROM s53619wcdo.wd_geolocated_property INNER JOIN s53619wcdo.wd_sitelinks ON wd_sitelinks.qitem=wd_geolocated_property.qitem WHERE wd_sitelinks.langcode="'+languagecode+'wiki";'
This is the kind of query I mentioned that I would need to run for every WP of the 288. It uses the languagecode to filter by language with the sitelinks table.

In a sqlite3 file (on my laptop) the query execution is direct, it has no response time. But the mysql takes a lot, even having the indexes in both sitelinks and wd_geolocated_property tables. A lot means around 50 min for just one language (I tried with cawiki, which has roughly 600,000 articles).
I executed the query 'optimize table sitelinks' and it took 1h and 23 min and I am not sure it was positive, but now it takes 20 min for the cawiki. It might have helped.
However, I am concerned with the rest of properties tables. Obtaining the geolocations is direct. Same for the language-based properties. But in some cases there is recursion, and I need to compare qitems that are set as origin with others that are in destination and I may need to read the table several times. This is something I need to test well, but it may not be viable with mysql.

In the end of the day, I think that having a small VPS server would really help. It would allow the project to be independent from the mysql databases and only use them for the website displayed data.

@Chicocvenacio was suggesting that perhaps in the future the wikidata data from every item is available and possible to query from a replica. This would mean that the triplets would be in a table in the same way that links or langlinks from mediawiki database schema are in tables in the wp replicas. If this was done, it would simply the process for b).

In any other case, processing the dump and having another space to work the data is unavoidable. This means, every 15 days, to download the dump (30GB), to create the parsed version of it in few sqlite3 db tables (6GB) and generate the datasets for all languages (I estimated 7-9 GB for a collection).
This would mean that having a VPS with a disk of around 80 GB would be safe. Any new dataset could be moved to another place with all the history of copies, so the disk would be only a working space for the dataset generation and the website.

All in all, I think that VPS is the way to go. I shared all the process as best as I can with all the details from my experiments. This is my humble perspective. If anyone knows a better solution, I'd like to hear it.

+1 for trying a Cloud VPS project for this.

In T189165#4036696, @Chicocvenancio wrote:

This seems needed to be able to complete the tasks set forth in the grant, running it in toolforge does not seem sustainable and will impact other users.

If I wasn't clear on that comment, I support the project request as well.

+1'ing

Project has been created.

Public read only info: https://tools.wmflabs.org/openstack-browser/project/wcdo
To create instances and manage the project: https://horizon.wikimedia.org

Current quotas are the default ones:

root@labcontrol1001:~# nova --os-project-id wcdo quota-show 
+-----------------------------+-------+
| Quota                       | Limit |
+-----------------------------+-------+
| instances                   | 8     |
| cores                       | 8     |
| ram                         | 16384 |
| floating_ips                | 0     |
| fixed_ips                   | 200   |
| metadata_items              | 128   |
| injected_files              | 5     |
| injected_file_content_bytes | 10240 |
| injected_file_path_bytes    | 255   |
| key_pairs                   | 100   |
| security_groups             | 10    |
| security_group_rules        | 20    |
| server_groups               | 10    |
| server_group_members        | 10    |
+-----------------------------+-------+