Some Notes on GHCN Database Using Odroid N2

I’ve been getting the whole GHCN database moved onto the Odroid N2 so that I could find out if it works correctly, and how much speed difference there might be.

This is also using Ubuntu with SystemD so I’m getting an assessment of just how painful that might be.

First off: SystemD

The more I use it, the more I hate it. The database would not start after a reboot. Why? Well you get to run a “special” command to find out. No, you can’t just look in the log file(s), they are binary. Want to know more of what was in the logs than that particular command tells you? Good luck with that…

Here’s the command: “systemctl status mariadb.service”

I don’t know why it wouldn’t start, as all this told me is that if failed to start, which I already knew… It did say it in far far more words spread over 17 lines though, so there’s that…

I had an issue with building the indexes on my first run and had ended up crashing the system to get it back to responsive. I have hangs happen at least once, and sometimes twice or more per day every day I’ve run the Odroid N2 / Ubuntu / SystemD combination. Why? No clue why. It just sporadically seems to lock up. I’ve left it idle over night and it didn’t lock up. It seems to be that you must be “doing something” and perhaps something it wasn’t expecting (or that SystemD is ill prepared to handle…) and then it goes off to La-La-Land. The whole thing locks up usually. Sometimes just your session interactions while “htop” continues to update in the corner, and your cursor moves, but no window is active nor can you change windows etc. etc.

I probably ought to try a remote SSH into the board and see if the whole OS is locked up or just my session, but I don’t care enough to do that (as ssh is now a PITA to use being way overly “secure” for inside a private network, so you must set up enough keys and key-ring and such to run at the TLA of your choice just to log into your machine…) I usually get around to that about the 3rd week.

So I’ve gotten familiar with just pulling the plug…

Do I know it is SystemD and not just a young and buggy Linux port on new hardware? Nope. However… The old “init” just starts other programs. One of them hangs, it’s just that process that’s toast. There’s plenty of reports of SystemD getting all panties in a bunch and hanging the whole system AND it’s the only thing with “fingers in the pie” everywhere… so it is almost certainty that SystemD is the thing barfing and hanging it all.

I’m sure that, in time, the rate of hang / crash will reduce as bugs are fixed and this port matures. I’m also quite certain that I’ll be really really happy when I can get a Devuan (or Debian uplifted to Devuan) running on it.

Some Database & File System Comments

It’s likely one of those hang / plug pulls “messed up the database”. So as it is on a mounted file system, I just unmounted it (and remounted it elsewhere, then deleted everything in it) and copied the original base set-up from under the mount point. That, BTW, is a nice sys-admin trick to know. Build your pristine base in your main file system (usually / ) and then make a copy of it into a separate partition; then remount that partition over the original. Now you can play with it, update it, whatever. If you “blow it” (or blow it up), just unmount it and you are back to pristine base. Copy and repeat.

So, OK, Now I’m on iteration 2 of “build the whole thing from scratch”, and this time I’m going to paste the results in here as they show up. Why? Because last time it did the hang/crash thing before I could capture the times anywhere…

As I’ve got the database mounted as a partition on a discrete disk, I also decided to put all the GHCN files, SQL, and Python programs on a partition on the same disk. This means I don’t need to move my home directory disk onto this system just to get the database stuff. This also means changing the path name in some of the programs, but that’s not much work. Yes, it ought to be a passed parameter but I’ve not learned how to do that yet with saved SQL programs. It’s probably trivial, but so is editing a dozen lines.

The disk in question is a Western Digital 2 TB disk, named WD2, with the file system named xfs as an xfs file system. Pretty simple naming convention, eh? ;-) So /WD2/xfs/SQL holds the SQL code. Here’s a partial ‘df’ listing of the disks (I’ve deleted the lines for a disk that isn’t involved)

chiefio@odroid:/WD2/xfs/SQL/tables$ df

Filesystem      1K-blocks      Used  Available Use% Mounted on
udev              1638048         0    1638048   0% /dev
tmpfs              380128      3928     376200   2% /run
/dev/mmcblk0p2    7251432   5777424    1169068  84% /
tmpfs             1900632     80796    1819836   5% /dev/shm
tmpfs                5120         4       5116   1% /run/lock
tmpfs             1900632         0    1900632   0% /sys/fs/cgroup
/dev/mmcblk0p1     130798     21390     109408  17% /media/boot
/dev/sda3        33538048     66448   33471600   1% /WD2/var
/dev/sda4        67076096    212724   66863372   1% /var/lib/mysql
/dev/sda5      1816278536   6946120 1809332416   1% /WD2/xfs
/dev/sda1        33538048     66560   33471488   1% /tmp
[...]
tmpfs              380124        12     380112   1% /run/user/1616

You can see that the 8 GB eMMC card has all of 1 GB free. I decided to use that partition overlay technique to cut down on the growth of the use of space as I add more programs by putting /var onto a separate partition, but I’ve not done that yet. You can see that future partition mounted as /WD2/var. I’ll copy /var into /WD2/var, then make sure the two directories match AND that the file system has the same owner and permission as /var, then umount /WD2/var and mount it as /var. IF all that works fine, then I’ll edit /etc/fstab to make the change happen at boot time too. (IFF I’ve ‘blown it’ in some way, then a reboot doesn’t hang as the fstab entry will mount it back on /WD2/var and I can figure out what I did wrong… that’s the reason to delay the edit of /etc/fstab …)

Further down, just above the […] line, you can see that I’ve mounted a 32 GB /tmp file system. Recall that I ran into “issues” with the database building indexes taking over 1 GB of /tmp to do so and I had a 1 GB temp. Yeah, 32 GB is “overkill” but heck, it’s a 2 TB disk, so why not? Yes, this will result in head seeks and contention issues when building indexes as the head has to fly back and forth from the database table partition to the /tmp partition. But notice that this is the first partition, then there’s a 2 GB swap partition (that doesn’t show up on this df listing), a 32 GB future /var, and then the database. All that is crammed into the first part of the disk. only 34 GB of disk is between the end of /tmp and the start of /var/lib/mysql. Out of 2 TB, that’s right next door…
Then on /dev/sda5 you get the remaining 1.8 TB of space as just open for “whatever”. During data loading operations, the head will need to pick up data from the files here and load it into the database right next door on /dev/sda4. As I loaded all that stuff first, it will be right on the start of that partition, so again, not a long seek issue.

All this is on a USB 3.0 disk on a USB 3.0 connection, so ought to be fast anyway.

Making The Database & Tables

I did the usual “apt-get install mariadb-client” and “apt-get install mariadb-server”. I likely don’t need both, but why worry? It’s there if I need it. Then the other configuration as before. Including defining the database as “temps”.

We’ll pick it up after that point (which was where I had the base system and copied it to the disk partition) with the creation of the tables.

I have a script that calls the various table creation SQL bits:

chiefio@odroid:/WD2/xfs/SQL/tables$ cat tables
source /WD2/xfs/SQL/tables/continent
source /WD2/xfs/SQL/tables/country
source /WD2/xfs/SQL/tables/invent3
source /WD2/xfs/SQL/tables/invent4
source /WD2/xfs/SQL/tables/temps3
source /WD2/xfs/SQL/tables/temps4
source /WD2/xfs/SQL/tables/mstats3
source /WD2/xfs/SQL/tables/mstats4
source /WD2/xfs/SQL/tables/anom3
source /WD2/xfs/SQL/tables/anom4

Here you can see where I changed the name of the path to /WD2/xfs/…

Here’s a sample of one of those files for folks who haven’t read them in the prior postings:

chiefio@odroid:/WD2/xfs/SQL/tables$ cat country
CREATE TABLE country (
    cnum CHAR(3) NOT NULL,
    abrev   CHAR(2),
    region CHAR(1) NOT NULL,
    cname  CHAR(60),
    version CHAR(5),
    ascension CHAR(10),
    PRIMARY KEY(abrev)
    ) ;

So this one “tables” script does something like that to create all 10 tables used in this GHCN analysis system.

How long does it take to run?

MariaDB [temps]> source SQL/tables/tables
Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

I think the first one had to wait for the disk to spin up… but it’s a total of 0.23 seconds. Oh, and remember some of the 0.01 might actually be smaller fractions…

Yeah, fast ;-) I’m liking the fast 8-)

Loading The Tables

Similarly, there’s a master script that calls the various table loading programs:

chiefio@odroid:/WD2/xfs/SQL/bin$ cat LoadTables 
source /WD2/xfs/SQL/bin/LOAD/lcontinent.sql
source /WD2/xfs/SQL/bin/LOAD/LCxu4.sql
source /WD2/xfs/SQL/bin/LOAD/l3xu.sql
source /WD2/xfs/SQL/bin/LOAD/l4xu.sql
source /WD2/xfs/SQL/bin/LOAD/ltemps3xu.sql
source /WD2/xfs/SQL/bin/LOAD/ltemps4xu.sql
source /WD2/xfs/SQL/bin/LOAD/lmstats3.sql
source /WD2/xfs/SQL/bin/LOAD/lmstats4.sql

At this point I need to insert a “make the indexes” step into the script, so the rest of it ought to read:

source /WD2/xfs/SQL/tables/mkindexcn
source /WD2/xfs/SQL/tables/mkindex3
source /WD2/xfs/SQL/tables/mkindex4
source /WD2/xfs/SQL/bin/LOAD/lanom3.sql
source /WD2/xfs/SQL/bin/LOAD/lanom4.sql

I did it “long hand” this time (see comments further down) but will change the script for the future.

Again I got to change the path names. You will note that in the past I changed the scripts and named them for the system they were on, as I moved my home directory between them. So “l3xu.sql” was to load the GHCNv3.3 data using the path name of the mount point on the Odroid XU4. I decided that since this is already a separate copy, I’d not bother with making Yet Another Set of scripts, and just changed the path in these on this system.

chiefio@odroid:/WD2/xfs/SQL/bin$ cat LOAD/l3xu.sql 
LOAD DATA LOCAL INFILE '/WD2/xfs/SQL/v3/3invent.out' INTO TABLE invent3;

show warnings;

Here you can see that the path name now points to the /WD2/xfs disk for the source of the instrument inventory data. As that file is in “database order” the fields don’t need to be specified. Also note that I’ve got a “show warnings;” after each operation. This might be confusing when you see the runs, as it reports a big zero. That’s not about the load of the data, it’s about warnings ;-)

Here’s the temperature load script:

chiefio@odroid:/WD2/xfs/SQL/bin$ cat LOAD/ltemps3xu.sql 
LOAD DATA LOCAL INFILE '/WD2/xfs/SQL/v3/3temps.out' INTO TABLE temps3;

show warnings;

This is the post FORTRAN processing file (to add the separators) for input to the database table, that is the temps.out output of the FORTRAN… which is why an input file is named “temps.out” ;-)

After the raw data load, there are some statistics tables that need to have that data calculated and loaded. These are the big time / compute sinks:

chiefio@odroid:/WD2/xfs/SQL/bin$ cat LOAD/lmstats3.sql 
INSERT INTO  mstats3 (stnID,month,mean,big,small,num,trang,stdev)
SELECT stnID,month,
AVG(deg_C),MAX(deg_C),MIN(deg_C),COUNT(deg_C), MAX(deg_C)-MIN(deg_C), STDDEV(deg_C)
FROM temps3 
WHERE deg_C>-90 
GROUP BY stnID,month;

show warnings;

During the initial temperature data load process, it is largely “disk wait” limited, with one core bouncing between about 30% and 80% utilization. Way more CPU than needed for that operation ;-) Only once you get to the statistics computation and the anomalies does it load up a CPU core.

Here’s the output of the data load, divided into chunks:

First up, we load the continents. This is done one at a time each with a dedicated line of SQL, so each of these is just putting one continent (region) name into that table. About 1/100 to 5/100 of a second each ;-)

MariaDB [temps]> source SQL/bin/LoadTables
Query OK, 1 row affected (0.05 sec)

Empty set (0.00 sec)

Query OK, 1 row affected (0.05 sec)

Empty set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Empty set (0.00 sec)

Query OK, 1 row affected (0.02 sec)

Empty set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Empty set (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Empty set (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Empty set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Empty set (0.00 sec)

Then we get to loading the Country data. One country has a way long name and IIRC it’s an island where the last letter of “possession of UK” gets lost. Yeah, I ought to make the table one character longer… Again with the 1/100 second:

Query OK, 240 rows affected, 1 warning (0.01 sec)    
Records: 240  Deleted: 0  Skipped: 0  Warnings: 1

+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'cname' at row 202 |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

Here’s line 202 from the input file:

3       317     SX      South Georgia and the South Sandwich Islands [United Kingdom]

That name is 61 CHAR long and the field is 60 (and I think it is defined as 60 in their text anyway) so losing the trailing ] isn’t a big deal, and I think it is likely their mistake anyway.

Finally we start to take some time. This is loading the GHCN v3.3 Thermometer Inventory, then the V4:

Query OK, 7280 rows affected (0.45 sec)              
Records: 7280  Deleted: 0  Skipped: 0  Warnings: 0

Empty set (0.00 sec)

Query OK, 27361 rows affected (0.70 sec)             
Records: 27361  Deleted: 0  Skipped: 0  Warnings: 0

Empty set (0.00 sec)

So again we’re down n the “about a second” to load all that. 1.15 seconds total.

Next we load up all the temperature data. This takes about 2 1/2 minutes for v3.3:

Query OK, 5554200 rows affected (2 min 24.05 sec)    
Records: 5554200  Deleted: 0  Skipped: 0  Warnings: 0

Empty set (0.00 sec)

I probably ought to also mention that through all of this, even with the browser open, I’ve not used most of the cores, nor has memory use rolled to swap. In fact, with 4 GB of memory, “htop” is reporting only a bit over 1 GB used.

For GHCN v4, out of 16 million records, we get 50 warnings. It looks like the “source” column is displaced, likely by a ‘tab’ in the original data somewhere being interpreted as a field separator when it ought not. I’ll need to go back and inspect those records “someday”, to assure they are not messing up anything. That it takes all of about 7 1/2 minutes to load all that is pretty trick ;-)

Query OK, 16809258 rows affected, 50 warnings (7 min 27.03 sec)
Records: 16809258  Deleted: 0  Skipped: 0  Warnings: 50

+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'source' at row 1056267                               |
| Warning | 1262 | Row 1056267 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1067942                               |
| Warning | 1262 | Row 1067942 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1067952                               |
| Warning | 1262 | Row 1067952 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1067955                               |
| Warning | 1262 | Row 1067955 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1067986                               |
| Warning | 1262 | Row 1067986 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1068009                               |
| Warning | 1262 | Row 1068009 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1068994                               |
| Warning | 1262 | Row 1068994 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1069011                               |
| Warning | 1262 | Row 1069011 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1069028                               |
| Warning | 1262 | Row 1069028 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1069030                               |
| Warning | 1262 | Row 1069030 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1071632                               |
| Warning | 1262 | Row 1071632 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1071639                               |
| Warning | 1262 | Row 1071639 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1071640                               |
| Warning | 1262 | Row 1071640 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1071818                               |
| Warning | 1262 | Row 1071818 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1071819                               |
| Warning | 1262 | Row 1071819 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1071826                               |
| Warning | 1262 | Row 1071826 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1071828                               |
| Warning | 1262 | Row 1071828 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1071829                               |
| Warning | 1262 | Row 1071829 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1071830                               |
| Warning | 1262 | Row 1071830 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1071840                               |
| Warning | 1262 | Row 1071840 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1071842                               |
| Warning | 1262 | Row 1071842 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1071843                               |
| Warning | 1262 | Row 1071843 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1071847                               |
| Warning | 1262 | Row 1071847 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1073732                               |
| Warning | 1262 | Row 1073732 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'source' at row 1082193                               |
| Warning | 1262 | Row 1082193 was truncated; it contained more data than there were input columns |
+---------+------+---------------------------------------------------------------------------------+
50 rows in set (0.00 sec)

UPDATE: Ah, I remember these guys now. I was able to pull this whole file into the vi editor (and FINALLY used swap… there’s now 2.88 MB rolled out to swap. All it took was editing a 1.2 GB file WHILE having the browser loaded up WHILE having two login sessions on the MariaDB WHILE having 4 terminal sessions with long scroll back history…

-rw-r--r-- 1 chiefio chiefio 1260880200 Mar 20 20:05 temps.out

These records look rather like all the others

AYM00089050     AY      M       0       0       0       89050   1968    TAVG    Gv4     17Mar2019        MAR      -0.39                 \
AYM00089050     AY      M       0       0       0       89050   1968    TAVG    Gv4     17Mar2019        APR      -1.20                 \
AYM00089050     AY      M       0       0       0       89050   1968    TAVG    Gv4     17Mar2019        MAY      -5.75                 \
AYM00089050     AY      M       0       0       0       89050   1968    TAVG    Gv4     17Mar2019       JUNE      -6.60                 \
AYM00089050     AY      M       0       0       0       89050   1968    TAVG    Gv4     17Mar2019       JULY      -4.25                 \

The last field is the “source” and I’m not using it for anything. The FORTRAN has a single CHAR size, so it can’t be writing more than that. There are other records with a ‘\’ last character that are not problematic, so it’s not that. I’ve tabbed through these records and they seem to have tabs where they ought to be, so I’m not sure what’s causing the complaint. I do know it isn’t going to matter to the processing I’m doing as “source” is a field I’m just not using. An interesting question for later, though.

A quick check on the table shows that all the “month” fields are correct, so any alignment issue is not in the start of the record, but something out in those source fields / flags portion.

MariaDB [temps]> SELECT DISTINCT month FROM temps4;
+-------+
| month |
+-------+
|  APR  |
|  AUG  |
|  DEC  |
|  FEB  |
|  JAN  |
|  MAR  |
|  MAY  |
|  NOV  |
|  OCT  |
| JULY  |
| JUNE  |
| SEPT  |
+-------+
12 rows in set (28.57 sec)

Not bad running through all those records in 1/2 minute ;-)

Computing The Monthly Statistics & Anomalies

Now onward to the statistics and anomaly calculations. All this happens inside the database system.

Doing the stats and anomaly processing, it pegs one core at about 100% and the others get some usage. I think this means that MariaDB isn’t very parallel in how it does math. No real surprise there, but something they can enhance in the future.

One really nice thing about this N2 system is that it does have Very Fast single cores, so even things that are CPU limited, and only run in one core, go pretty fast. Also, I can edit in the browser with zero lag, delay, or anything really, even while it is cranking on that compute load.

Here we’re calculating the monthly statistics for the two data sets. 2/3 of a minute for v3.3 then almost 12 minutes for v4 as it has a lot more records to process.

Here’s an example of what it’s doing. This is for v4:

chiefio@odroid:/WD2/xfs/SQL/bin$ cat LOAD/lmstats4.sql 
INSERT INTO  mstats4 (stnID,month,mean,big,small,num,trang,stdev)
SELECT stnID,month,
AVG(deg_C),MAX(deg_C),MIN(deg_C),COUNT(deg_C), MAX(deg_C)-MIN(deg_C), STDDEV(deg_C)
FROM temps4 
WHERE deg_C>-90 
GROUP BY stnID,month;

show warnings;

So it gets to do an AVeraGe, COUNT up all of them, do a MIN / MAX find, then the range as their difference and the STanDard Deviation of the temperatures. I probably ought to make some reports of those just to see what’s in there ;-) It does this for each station, for each of the 12 months of the year, across years.

Query OK, 87360 rows affected (39.90 sec)
Records: 87360  Duplicates: 0  Warnings: 0

Query OK, 328327 rows affected (11 min 50.25 sec)
Records: 328327  Duplicates: 0  Warnings: 0

Empty set (0.00 sec)

For comparison, here’s the results from the run on the Raspberry Pi M3 (that was also CPU limited on one core, but not disk limited even on the USB 2.0 disk)

MariaDB [temps]> source tables/mstats4
Query OK, 0 rows affected (0.57 sec)
[...]
MariaDB [temps]> source tables/anom4
Query OK, 0 rows affected (0.31 sec)
[...]
MariaDB [temps]> source bin/lmstats4.sql
Query OK, 328327 rows affected (29 min 32.63 sec)
Records: 328327  Duplicates: 0  Warnings: 0

MariaDB [temps]> 

So the Odroid N2 is about 3 x as fast on the statistics computing / loading. That means each of the 4 fast cores is 3 x a Pi M3 core, and that overall, it is 3 x faster PLUS you get 2 cores that are about 1.5 of a Pi M3 core, so call it another 3 Pi cores worth. So about 3.75 x as much total computes. Except the Pi has a too small heat sink and will start to speed limit if your run it at 100% for a long time … This is one FAST SBC! For about 2 x the money you get almost 4 x the computes and a lot more memory (the basic board is 2 GB for about $65, I paid a bit more to get the 4 GB).

Computing The Anomalies

I added index fields to get these to compute in anything other than glacial time on the Pi M3. Initially, I let this one run without the indexes. As the Pi took over 15 minutes with indexes, I had no idea how long this was likely to take. I then went out and mowed the lawn. When I came back, it had not gotten anywhere. So, I just killed it off and added the indexes, then ran it… Without index fields, I’m pretty sure the database engine has to search on the joined tables for each of the records processed. That’s a LOT of searches of the tables… like 10s of thousands of searches.

Here we compute the Anomaly Tables. These are done using the statistics above. A given instrument / month is only compared with itself. In this way if there’s data missing in one season more than another, it has not effect. Similarly, one instrument is not influenced by any other instrument and there are no “splice artifacts”. This is a very pure kind of anomaly calculation.

chiefio@odroid:/WD2/xfs/SQL/bin$ cat LOAD/lanom4.sql 
INSERT INTO  anom4 (stnID,abrev,region,cnum,year,month,deg_C)
SELECT T.stnID,C.abrev,C.region,C.cnum,T.year,T.month,T.deg_C-ST.mean
FROM temps4 AS T
INNER JOIN country AS C
ON T.abrev=C.abrev
INNER JOIN mstats4 AS ST 
ON
        ST.stnID=T.stnID
	AND ST.month=T.month 
WHERE T.deg_C > -90 
;

show warnings;

The “WHERE T.deg_C > -90” is just saying to not use the missing data flag records. So we’re loading the anom4 talbe with temperature data from temps4 (T) and information about the country (region(continent),number, abbreviation) along with using statistics from mstats4 to calculate Degrees-mean_degrees for that station (in that month… that line “AND ST.month=T.month”)

A fairly trick little bit of code if I do say so myself. Joining 3 tables, one of them statistics, to create anomaly data loaded into a fourth.

It also looks to “peg a core” and I’ve not seen disk waits, so the USB 3.0 is paying off here as is the very fast single core speeds.

UPDATE: Well, it’s been maybe an hour? I went out and mowed the front yard, so how ever long that is… and how much has that load of the anomaly table for v3.3 got done?

MariaDB [temps]> SELECT COUNT(stnID) FROM anom3;
+--------------+
| COUNT(stnID) |
+--------------+
|            0 |
+--------------+
1 row in set (0.98 sec)

Not a single record… So it looks like, without indexes, that particular table load doesn’t get anything done but repeatedly searching tables to try to match records to make a decision…

Given that, I’m going to kill off the anomaly loading steps, make the indexes, and then rerun the anomaly creation. Just like I did on the Raspberry Pi M3… So “whatever” it is doing, throwing a lot more cores and a faster disk at it is not enough. It’s index fields or just give it up…

Adding an index on the “country” table was darned fast:

MariaDB [temps]> source SQL/tables/mkindexcn
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

So fast I felt compelled to check that the index showed up. It’s that “MUL” under “key”:

MariaDB [temps]> describe country;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| cnum      | char(3)  | NO   | MUL | NULL    |       |
| abrev     | char(2)  | NO   | PRI | NULL    |       |
| region    | char(1)  | NO   |     | NULL    |       |
| cname     | char(60) | YES  |     | NULL    |       |
| version   | char(5)  | YES  |     | NULL    |       |
| ascension | char(10) | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
6 rows in set (0.01 sec)

MariaDB [temps]> 

Here’s the script that makes the indexes on the anom3 table:

chiefio@odroid:/WD2/xfs/SQL/tables$ cat mkindex3
CREATE INDEX regionx  ON temps3 (region);
CREATE INDEX regmonx  ON temps3 (region, month);

It uses about 1/2 GB of /tmp in the process:

Filesystem     1K-blocks   Used Available Use% Mounted on
/dev/sda1       33538048 590780  32947268   2% /tmp

Then it takes all of about 3 minutes to make the indexes:

MariaDB [temps]> source SQL/tables/mkindex3 
Query OK, 0 rows affected (1 min 40.24 sec)         
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (1 min 31.97 sec)         
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [temps]> 

That whole process being “disk wait” limited with about 1/2 of one CPU core working.

Now the lanom3.sql program is running between (bouncing between) about 58% and 97% of a core used, with long stretches of 90+% after the first few minutes. So my guess would be loading up some indexes into memory first, then “cranking on it”.

And the load finishes in about 4 minutes now. What a difference an index makes ;-)

MariaDB [temps]> source SQL/bin/LOAD/lanom3.sql
Query OK, 5278201 rows affected, 65535 warnings (4 min 36.55 sec)
Records: 5278201  Duplicates: 0  Warnings: 5272409

+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
[...]
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
+-------+------+--------------------------------------------+
64 rows in set (0.00 sec)

MariaDB [temps]> 

It only prints out 50 lines of the warnings, not all 5 million ;-)

Since we calculated deg_C of anomaly, it has far more precision that the defined length of the field. The “warning” is just telling us that, which we already knew. There’s likely some way to shape the value prior to loading it so the warnings go away, but I’m too lazy at the moment to find it ;-)

Anomaly loading for v4 will take longer as it is a much bigger data set. So back “in a while” when it is done.

I only need to create one index on temps4 as it already has several fields as keys, though it runs to over 1 GB of /tmp:

MariaDB [temps]> source SQL/tables/mkindex4 
Query OK, 0 rows affected (5 min 29.31 sec)         
Records: 0  Duplicates: 0  Warnings: 0
Filesystem     1K-blocks    Used Available Use% Mounted on
/dev/sda1       33538048 1115068  32422980   4% /tmp
MariaDB [temps]> describe temps4;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| stnID     | char(11)     | NO   | PRI | NULL    |       |
| abrev     | char(2)      | NO   | MUL | NULL    |       |
| F1        | char(1)      | YES  |     | NULL    |       |
| F2        | char(1)      | YES  |     | NULL    |       |
| F3        | char(1)      | YES  |     | NULL    |       |
| F4        | char(1)      | YES  |     | NULL    |       |
| wmo       | char(5)      | YES  |     | NULL    |       |
| year      | char(4)      | NO   | PRI | NULL    |       |
| type      | char(4)      | NO   | PRI | NULL    |       |
| version   | char(5)      | NO   |     | NULL    |       |
| ascension | char(10)     | YES  |     | NULL    |       |
| month     | char(4)      | NO   | PRI | NULL    |       |
| deg_c     | decimal(5,2) | YES  |     | NULL    |       |
| missing   | char(1)      | YES  |     | NULL    |       |
| qc        | char(1)      | YES  |     | NULL    |       |
| source    | char(1)      | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
16 rows in set (0.02 sec)

The one added index is on abrev.

Then the load of anom4 table. Man does this thing fly! Under 8 minutes. That’s IT.
For 15 MILLION rows of data matched between 3 tables and computes done.

We do again get the truncation of computed anomaly deg_C data where the precision exceeds the field size

MariaDB [temps]> source SQL/bin/LOAD/lanom4.sql 
Query OK, 15448107 rows affected, 65535 warnings (7 min 43.60 sec)
Records: 15448107  Duplicates: 0  Warnings: 15444656

+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
[...]
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
| Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
+-------+------+--------------------------------------------+
64 rows in set (0.01 sec)

MariaDB [temps]> 

A note on N2 “comfort”

What is especially nice is that where the Pi M3 struggles to edit a page this long at all, and really bogs down with this process running if you try to do something else, the Odroid N2 is still flying effortlessly through this edit AND the data compute / load at the same time, AND has 3 cores mostly idle and 2 of them only sporadic with the FireFox load. It’s like nothing is really going on on the board…It is even cool to the touch (where the other boards get quite warm with this much load on them).

Nice. Just nice.

So at this point I’ve completed all the table loads. This is one very fast board. I ought to go back and investigate the various warnings / truncates and find a way to eliminate them, and also find what is wrong in those few input records that have an excess tab in them. Then do the loading all over again. Not a big deal on this machine, PITA on the Pi.

This, BTW, is IMHO a much better “benchmark” than anything synthetic. It has a nice mix of disk, CPU, integer, character, double precision math, etc. etc. It is far more like “a normal use profile” than anything that just pushes really hard on one ability.

Then, the fact that overall I could never get it to “load up” more than a couple of cores said it has a lot more left to use. I’d not be at all bashful about having this thing cranking like crazy on the database while editing web pages while compiling / running some FORTRAN, while a C dist-build was going on. I’m already thinking it will be fun to try a build of the Linux kernel on this (or maybe a browser build) as they are typically only done as “cross compiles” from Intel boxes to ARM targets just to get enough speed and memory for them to complete. I’m pretty sure 4 GB and with these fast CPU Cores, it will work just fine as a native build.

I just hope I don’t get so used to the speed that I can’t be comfortable on the Pi M3 anymore ;-)

UPDATE Per Python & Matplotlib

Well, for better or worse, this Ubuntu has the same failure mode for the plotting library as did the RockPro64, and likely for the same reasons and using the same code. Here’s a screenshot of my terminal window, with “idle” running Python. This is a program that ran fine before, and it makes a graph of the anomaly data for the “water” area. (Not much data so quick to run). That graph is in the center. You can see all the “chicken scratch” on the left that is where the axis marks / description are supposed to be.

Screen Shot of Ubuntu N2 running Python matplotlib and how it fails

Screen Shot of Ubuntu N2 running Python matplotlib and how it fails

You can also see how I have an “htop” panel with the usage bars showing in the upper left corner and some other bits of how I lay out my workspace.

So this will conclude my use of the Odroid N2 for a while. Since my next task is making a LOT of graphs, I’ll be using one of the systems where they are properly plotted and labeled.

I’ll still fire this board up from time to time and do updates and such, and test this again. Eventually one of the updates will fix this. I’ll also occasionally use it just for browsing and postings.

Unfortunately, its utility as a Database Engine and Graphing Station is nil for me as long as this issue exists. So basically it’s back to the Raspberry Pi M3 or the Odroid XU4.

This is a very common state of affairs for “The Latest Hot Computers”. The software just hasn’t caught up with the changes in how the various boards do their computing. Some of it is just uncovering latent bugs. Some of it is changes in how something important works. Other times it’s especially rough, like when the whole word size changes too and all your variables and pointers can be different. I think that’s part of the issue here. Both the “hot boards” that have this issue are 64 Bit and newer architectures. The ones without this issue are the old 32 bit code base, or the Raspberry Pi M3 which, while it is a 64 bit CPU, may have found this issue early and either patched it, or more likely just compiled and runs the 32 bit codes (which you can do on the ARM CPUs).

So, OK, this one goes back in the shipping box for a while and I get the XU4 back out ;-)

A short time later…. Here’s the same graph, made on the Odroid XU4. This is what it is supposed to look like. This too is a full sized screen shot so be careful if you “click to embiggen” ;-)

A screen shot of the Odroid XU4 with the proper graph showing

A screen shot of the Odroid XU4 with the proper graph showing

The cross in the background is from a photo of the “Truckers Chapel” I took one night driving to Florida. It is at a road side truck stop just over the border in Georgia on I-75 IIRC. It is set up in a Refer Trailer (not visible as it is under the various windows). There was just a hint of mist or incipient fog in the air and I loved the way it made a kind of ‘halo cross’ around the actual one.

Subscribe to feed

About E.M.Smith

A technical managerial sort interested in things from Stonehenge to computer science. My present "hot buttons' are the mythology of Climate Change and ancient metrology; but things change...
This entry was posted in NCDC - GHCN Issues, Tech Bits and tagged , , , . Bookmark the permalink.

30 Responses to Some Notes on GHCN Database Using Odroid N2

  1. CoRev says:

    EM, really happy to hear that.

  2. E.M.Smith says:

    @CoRev:

    I think maybe it is the constant spell checking via a scripted language in a VM inside the browser, but something about editing a long page of text in the WordPress posting editor just chews up CPU. The longer the page gets, the slower it goes.

    Eventually it reaches a length where the R. Pi just can’t cut it.

    Chromium does better as it uses multiple cores more effectively, but eventually it hits a bog point too.

    So far on the Odroid N2, despite this being one of the longest postings I’ve ever edited and with LOTS of tech stuff that the spell checker flags as “wrong”, it just flies through it.

    IMHO this speed of board is “close enough to Intel” speeds that the fact all the web page builders only ever test on fast Wintel boxes so never catch “sloth” in their code, just doesn’t hurt this SBC.

    So far, too, it’s been stable today. I suspect that the “hang / freeze” issue is related to the behaviour of trying to mount various disk partitions as /dev/usb[0-3] when they have system images / boot partitions on them, then me “doing something” that causes those /dev/usbx files to be looked at, and the “assumption” about file system type being a bit off causes the barf. I’ve now just gone ahead and mounted the (4 or so) partitions in /etc/fstab and not had an issue. (I DID have entries in /etc/fstab before but they were “noauto” that ought to have prevented any mount attempts; but “something” (cough – systemD) was insisting it knew better and mounting them as /dev/usbx…

    Similarly the other hang / crash was on attempting to use the database after another hang/crash that might well have scrambled something in the table as it was trying to build indexes… (This was yesterday…)

    So my guess is that systemD doesn’t deal well with broken expectations about file system type when it’s doing a mount to /dev/usb0, and that “who knows what” doesn’t handle a daft MariaDB table with scrambled bits.

    Since doing those fixes, it has not hung and I’ve not had to ‘pull the plug’.

    That’s the kind of stuff that tends to be missed in QA, and gets fixed after users put in bug reports. It will likely be gone inside 6 months or so. Until then, I’m just going to assume that “noauto” doesn’t work ;-)

  3. E.M.Smith says:

    OK, I’ve gone back and checked on the warnings. Nothing that’s going to taint the results. So I’m done updating this particular posting now.

    Next I’ll get the Python stuff going and see how it does. Remember that it was failure to make graphs with the matplotlib that had the RockPro64 not become my reporting station. Hopefully that is working right n this Ubuntu port.

  4. Steven Fraser says:

    @EM: Interesting that you mentioned potential systemd issues this post. I just put up a java-based process, which had been designed to run in the Linux foreground (complete with logging to stdout) , as a systemd service, invoked from a bash script.

    It had issues to begin with. All the debug and status lines in the script, and generated from within the java, showed up just fine using the service journaling report program, journalctl -u unit.service, where unit serves as my service name. (Your example would be mariadb.service). The log lines showed me what was missing… the java environment was not correctly established.

    You probably already know this, but your mariadb.service file (likely in /lib/systemd/system) would show the program syntax for your db invokation, on the line which starts with ExecStart. If it is script-initiated, it would be the path to the script.

    I had not done a service from scratch before on Linux using systemd, and found some of its features to be convenient advances from the init.d approach. Of particular use were these entries in the unit.service file, [Service] section, with descriptive content:

    Type=forking
    WorkingDirectory=
    PIDFile=/-SERVICE-PID
    EnvironmentFile=-
    ExecStart=path to bash script to start the service>
    RemainAfterExit=no
    TimeoutStartSec=60
    TimeoutStopSec=15
    SuccessExitStatus=143

    This last one is instructive. Since my service was java-based, the java task, run in the background (java syntax between nohup and & ) returns non-0 on successful exit after a TERM interrupt, which is what systemctl stop unit.service gives it for the graceful shutdown. What java returns on successful shutdown in these circumstances is 143.

    Glad to hear that you got by the db service issues. An SB with those performance characteristics sounds fun to work with. I may have to get one.

  5. Steven Fraser says:

    @EM: WordPress trimmed some of my config file characters, but mostly readable anyway.

  6. E.M.Smith says:

    Things inside an angle bracket set will be removed then WP tries to use them as HTML and when that fails, just skips it. You always need to removecthe angle bracket sets or replace them with the unicode.

    Yeah, I know the service files exist and that journalctl provides some info. I’ve just resisted learning to deal with it…. I’d rather not have to play more “find the cheese” when on other systems I know where the cheese warehouses are….

    So far, despite my grousing about Ubuntu Mate and SystemD, I really like this SBC. I think I’ve got past the hang crash issues (mount all disk partitions so it can’t try mapping them to usb [0-3] and then barfing if you touch the contents). Then I’ve got my preferred environment up, LXDE. Given this is used mostly just for “regular user stuff” I don’t have to interact with systemD much at all, so can mostly ignore it.

    IFF the Python libs work right and it graphs properly, it will be my main temperature db box. Just the speed of reloads alone makes it easier to do stuff and change table designs. Then the no waiting WordPress page edits is A Very Nice Thing. Add in that video and sound both work out of the box too…

    The only open Aw Shit possibke is that the HDMI input to my monitor died at the time I first tried this board. So there is a tiny cloud of “Did the N2 blow the adapter cable or the DVI input port?” Or was it just a coincidental failure? As I don’t have another monitor to try it with (and would not risk blowing another one anyway…) that cloud will just have to await other folks experience reports. It works with the TV HDMI in, and any new monitor will be HDMI, so I’m kind of past caring.

  7. E.M.Smith says:

    Well, I finished the swap to /var on a dedicated disk partition, then installed all the Python stuff, and GIMP, and some more. Then ran a graph.

    It’s botched the headers / sidebar markings, just like on the RockPro64. So the software is not yet up to the task of running on these newest CPU architectures. I’ve added a screen shot image to the bottom of the posting where you can see the graph in the middle. It’s full sized, so “click to embiggen” if you have the monitor real-estate for it, and care to…

  8. CoRev says:

    Guess I too will be patient before buying.

  9. Steven Fraser says:

    @EM: you wrote ‘Given this is used mostly just for “regular user stuff” I don’t have to interact with systemD much at all, so can mostly ignore it.’

    Understood. Later on, if there are services starting on boot that you really don’t need (or, don’t want for resource, boot speed or conflict reasons), they are pretty easy to stop and toggle off from the command line, without disturbing the config.

    Glad to hear that the system stability has improved. At this price point, a distributed processing cluster of a half-dozen nodes would be quite doable.

  10. E.M.Smith says:

    @CoRev:

    “All the usual stuff” works fine. FireFox, Libre Office, terminal windows, Gimp, Python3 itself, Idle3, htop, lxde, mate, mysql/mariadb, etc.

    So far, all that it “broken” is one part of the matplotlib python graphing library and the interaction of “noauto” and whatever does the /etc/usbx mounting (which is easily bypassed and will only show up for people who swap disks and multiboot a lot).

    Unfortunately, that graph library is my immediate need. IF my next task wasn’t more graphs, this would still be my desktop daily driver. Unless you too have that need, it just won’t matter to you. As soon as the next batch of graphs are done, I will be putting this back on my desktop as daily driver. Basically, this is cheap enough and works well for almost everything, so if you want one, there really is no reason not to buy one.

    It is also the case that the library works in Debian on the 64 bit Pi, so IF the issue is a 64 vs 32 bit one, the fix is known and just needs to percolate through the delay from Debian upstream through Ubuntu (who often are a bit behind). It is quite possible that in a few weeks, my next apt-get update will fix it. (Thus my set it aside for a graph making binge, then try again later).

    Basically, you need to know if your use case is OK or not. So what is your use case and I’ll test it.

    @Steven Fraser:

    I have a few v7 nodes in a cluster, and a few v8 nodes not (yet) clustered. But will get back to that project once the present one completes. Yes, it would make a heck of a cluster node.

    At about $80 all in per node, for a 120 CPU cluster it would cost $1600, have either 40 GB or 80 GB of memory, a cumulative 20 Gb/sec of ethernet and huge disk bandwidth. Very much in the reach of lots of ordinary folks.

    I’m not sure what I could do to keep it busy. (But I’d like to try :-)

    Even just my limited v7 cluster does C compiles and such faster than I really need. In fact it is only memory limits per board that bite, and then only on a few things like FireFox (where this board is likely to be big enough at 4 GB).

    I’ve slowly turned boards in the v7 cluster into dedicated service providers (like DNS and PiHole) just so they are not 99.99% idle… They still participate in things like distcc and “parrallel” (shell script distribution) but ‘have a day job’ too ;-)

    There are ways to do heterogeneous architecture clusters, but I’ve not gone there yet. I may never get there, as board speeds pick up faster than my use case expands….

    So at present I’ve got 2 x Pi M3, 2x Pi M2 (old type), one XU4, one C1. The Pis can be run v7 or v8. So that can make a 5 x 4= 20 plus 8 or a 28 core v7 cluster.

    On the 64 bit side: Pine A64, 4 x Pi (in 64 bit os mode), c2, RockPro64, N2, 2x Orange Pi One or
    6 x 4 =24 plus 12+8 = 44 cores many of them Damn Fast. (Though the Orange Pi is only 512 MB memory and heat limits after a while)

    The biggest issue is just the different OS levels prevent things like distcc over the whole collection. Compilers need the same libraries everywhere. The downside of my heterogeneous mush. For distcc, I’d be best served by the 4x Pi on the same Devuan release. Thats what I used for my testing of things like OpenCL etc. (And found that the Pi didn’t do parallel FORTRAN very well so moved on…)

  11. E.M.Smith says:

    It is well worth noting that this one Odroid N2 board has more compute power, faster networking, faster disk, and as much memory as all 4 of the R.Pi boards in my cluster, and without the clustering overhead.

    The impact of a Damn Fast board is to make a slow cluster irrelevant… As soon as I had the 8 core Odroid XU4, my use of the Pi cluster plummeted.

  12. Steven Fraser says:

    @EM: You wrote. ‘At about $80 all in per node, for a 120 CPU cluster it would cost $1600,..Very much in the reach of lots of ordinary folks.’

    Agreed. IIRC, I paid about that for my first Pentium. I wonder what it takes to run GFSV2?

  13. E.M.Smith says:

    It looks like you need specific SAN hardware, per the wiki, for nulti-node use:

    https://en.wikipedia.org/wiki/GFS2

    Hardware

    The design of GFS and of GFS2 targets SAN-like environments. Although it is possible to use them as a single node filesystem, the full feature-set requires a SAN. This can take the form of iSCSI, FibreChannel, AoE, or any other device which can be presented under Linux as a block device shared by a number of nodes, for example a DRBD device.

    The DLM requires an IP based network over which to communicate. This is normally just Ethernet, but again, there are many other possible solutions. Depending upon the choice of SAN, it may be possible to combine this, but normal practice[citation needed] involves separate networks for the DLM and storage.

    The GFS requires fencing hardware of some kind. This is a requirement of the cluster infrastructure, rather than GFS/GFS2 itself, but it is required for all multi-node clusters. The usual options include power switches and remote access controllers (e.g. DRAC, IPMI, or ILO). Fencing is used to ensure that a node which the cluster believes to be failed cannot suddenly start working again while another node is recovering the journal for the failed node. It can also optionally restart the failed node automatically once the recovery is complete.

    You might want to try a different one:

    https://en.wikipedia.org/wiki/OpenIO

    OpenIO has been founded in 2015 by Laurent Denel (CEO) and six co-founders, to offer an integrated object storage and serverless computing solution for building hyper scalable IT infrastructures for a wide range of applications. OpenIO leverages open source software, developed since 2006, which is based on a grid technology that enables dynamic behaviors and supports heterogenous hardware.
    […]
    OpenIO software has native object APIs and SDKs for Python, C and Java, it integrates a HTTP REST/API and has strong compatibility with the Amazon S3 API and the OpenStack Swift API] The company also offers a proprietary File System connector to access data stored in an OpenIO SDS object store through file access methods: it is based on Fuse and presents a POSIX File System which can be shared over local networks via NFS, SMB and FTP.

    OpenIO software is compatible with any x86 or ARMv7 server running Linux and has low hardware requirements, it can be run also on Raspberry Pis and on storage drives with embedded server.

    Or others…

    https://en.wikipedia.org/wiki/Comparison_of_distributed_file_systems

  14. E.M.Smith says:

    I decided to make the Asia set of graphs using the Odroid UX4, then discovered that the extensions I’d made to the database for those anomaly graphs were not in the version on the XU4, so decided to just do a “scrub and reload” of it all.

    This will assure it’s all the most current version and also give some comparative performance statistics. It is worth noting that this particular SBC has /var mounted as a partition from a 2 GB Seagate USB 3.0 disk on the USB 3.0 port, while the various Python and SQL programs are on a USB 2.0 port (where due to their very short length the speed doesn’t matter during the loading of the program) so most of this difference ought to be the 32 bit word length of the XU4 or the general CPU speeds involved / memory speeds.

    First a did a “drop” of all tables:

    I use a script named “dropall” to make it one command

    chiefio@odroidxu4:/SG/xfs/chiefio/SQL/tables$ cat dropall
    DROP TABLE continent;
    DROP TABLE country;
    DROP TABLE invent3;
    DROP TABLE invent4;
    DROP TABLE temps3;
    DROP TABLE temps4;
    DROP TABLE anom3;
    DROP TABLE anom4;
    DROP TABLE mstats3;
    DROP TABLE mstats4;
    DROP TABLE yrcastats;
    

    It s that ‘yrcastats’ table that is needed, plus I added ‘abrev’ to the anom3 table and similar changes that made the whole thing work and go faster. Easier to just “dump it all” and reload it all than unscramble just the changes. I could, of course, just run this all on the R. Pi M3 (as I did for the other postings graphs) but where’s the fun in that? ;-)

    MariaDB [temps]> source tables/dropall
    Query OK, 0 rows affected (0.52 sec)
    
    Query OK, 0 rows affected (0.21 sec)
    
    Query OK, 0 rows affected (0.22 sec)
    
    Query OK, 0 rows affected (0.24 sec)
    
    Query OK, 0 rows affected (0.38 sec)
    
    Query OK, 0 rows affected (0.40 sec)
    
    Query OK, 0 rows affected (0.28 sec)
    
    Query OK, 0 rows affected (0.54 sec)
    
    Query OK, 0 rows affected (0.13 sec)
    
    Query OK, 0 rows affected (0.11 sec)
    
    Query OK, 0 rows affected (0.14 sec)
    
    MariaDB [temps]> show tables
        -> ;
    Empty set (0.00 sec)
    

    Then re-created all the tables (again with a script):

    chiefio@odroidxu4:/SG/xfs/chiefio/SQL/tables$ cat tables
    source /SG/xfs/chiefio/SQL/tables/continent
    source /SG/xfs/chiefio/SQL/tables/country
    source /SG/xfs/chiefio/SQL/tables/invent3
    source /SG/xfs/chiefio/SQL/tables/invent4
    source /SG/xfs/chiefio/SQL/tables/temps3
    source /SG/xfs/chiefio/SQL/tables/temps4
    source /SG/xfs/chiefio/SQL/tables/mstats3
    source /SG/xfs/chiefio/SQL/tables/mstats4
    source /SG/xfs/chiefio/SQL/tables/anom3
    source /SG/xfs/chiefio/SQL/tables/anom4
    source /SG/xfs/chiefio/SQL/tables/yrcastats
    
    MariaDB [temps]> source tables/tables
    Query OK, 0 rows affected (0.33 sec)
    
    Query OK, 0 rows affected (0.25 sec)
    
    Query OK, 0 rows affected (0.26 sec)
    
    Query OK, 0 rows affected (0.26 sec)
    
    Query OK, 0 rows affected (0.25 sec)
    
    Query OK, 0 rows affected (0.24 sec)
    
    Query OK, 0 rows affected (0.24 sec)
    
    Query OK, 0 rows affected (0.24 sec)
    
    Query OK, 0 rows affected (0.28 sec)
    
    Query OK, 0 rows affected (0.26 sec)
    
    Query OK, 0 rows affected (0.25 sec)
    
    MariaDB [temps]> 
    

    For the actual loading of the database, I’ve scattered the making of the indexes up the script into the places just after the various tables are loaded. This means you need a bit of care comparing this lines to the lines from the run above on the N2. It may also make some of the processes of loading a bit faster as there are now indexes that were not there before (but I’ve not bothered to sort that…)

    Here’s the newest ‘LoadTables” script:

    chiefio@odroidxu4:/SG/xfs/chiefio/SQL/bin$ cat LoadTables 
    source /SG/xfs/chiefio/SQL/bin/LOAD/lcontinent.sql
    source /SG/xfs/chiefio/SQL/bin/LOAD/LCxu4.sql
    source /SG/xfs/chiefio/SQL/bin/LOAD/l3xu.sql
    source /SG/xfs/chiefio/SQL/bin/LOAD/l4xu.sql
    source /SG/xfs/chiefio/SQL/tables/mkindexcn
    source /SG/xfs/chiefio/SQL/bin/LOAD/ltemps3xu.sql
    source /SG/xfs/chiefio/SQL/bin/LOAD/ltemps4xu.sql
    source /SG/xfs/chiefio/SQL/tables/mkindex3
    source /SG/xfs/chiefio/SQL/tables/mkindex4
    source /SG/xfs/chiefio/SQL/bin/LOAD/lmstats3.sql
    source /SG/xfs/chiefio/SQL/bin/LOAD/lmstats4.sql
    source /SG/xfs/chiefio/SQL/bin/LOAD/lanom3.sql
    source /SG/xfs/chiefio/SQL/bin/LOAD/lanom4.sql
    source /SG/xfs/chiefio/SQL/tables/mkanomindex3
    source /SG/xfs/chiefio/SQL/tables/mkanomindex4
    

    Which is running now.

    MariaDB [temps]> source bin/LoadTables
    Query OK, 1 row affected (0.08 sec)
    
    Empty set (0.00 sec)
    
    Query OK, 1 row affected (0.08 sec)
    
    Empty set (0.00 sec)
    
    Query OK, 1 row affected (0.18 sec)
    
    Empty set (0.00 sec)
    
    Query OK, 1 row affected (0.07 sec)
    
    Empty set (0.00 sec)
    
    Query OK, 1 row affected (0.05 sec)
    
    Empty set (0.00 sec)
    
    Query OK, 1 row affected (0.06 sec)
    
    Empty set (0.00 sec)
    
    Query OK, 1 row affected (0.06 sec)
    
    Empty set (0.00 sec)
    
    Query OK, 1 row affected (0.06 sec)
    
    Empty set (0.00 sec)
    
    Query OK, 240 rows affected, 1 warning (0.17 sec)    
    Records: 240  Deleted: 0  Skipped: 0  Warnings: 1
    
    +---------+------+----------------------------------------------+
    | Level   | Code | Message                                      |
    +---------+------+----------------------------------------------+
    | Warning | 1265 | Data truncated for column 'cname' at row 202 |
    +---------+------+----------------------------------------------+
    1 row in set (0.00 sec)
    
    Query OK, 7280 rows affected (2.07 sec)              
    Records: 7280  Deleted: 0  Skipped: 0  Warnings: 0
    
    Empty set (0.00 sec)
    
    Query OK, 27361 rows affected (2.77 sec)             
    Records: 27361  Deleted: 0  Skipped: 0  Warnings: 0
    
    Empty set (0.00 sec)
    
    Query OK, 0 rows affected (0.29 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

    So those times get us through the load of the Continents (regions), countries, and inventory data for both v3.3 and v4 and the first making of indexes that is just a very fast one on countries.

    As the actual data loads et. al. complete, I’ll paste those times here in another comment.

    For these, it looks like that .45 sec and .7 sec time to load those two tables are now about 2 seconds. Yes, it’s over a tripple, but over a tripple of almost nothing is still almost nothing…

  15. E.M.Smith says:

    Looks like the 2:45 loading v3.3 temperatures turns into 4:10 seconds, so under a double. Then loading the v4 data takes almost 13 minutes, again less than a double, and with the same warnings on “source” flag.

    Query OK, 5554200 rows affected (4 min 0.10 sec)     
    Records: 5554200  Deleted: 0  Skipped: 0  Warnings: 0
    
    Empty set (0.00 sec)
    
    Query OK, 16809258 rows affected, 50 warnings (12 min 55.68 sec)
    Records: 16809258  Deleted: 0  Skipped: 0  Warnings: 50
    
    +---------+------+---------------------------------------------------------------------------------+
    | Level   | Code | Message                                                                         |
    +---------+------+---------------------------------------------------------------------------------+
    | Warning | 1265 | Data truncated for column 'source' at row 1056267                               |
    | Warning | 1262 | Row 1056267 was truncated; it contained more data than there were input columns |
    | Warning | 1265 | Data truncated for column 'source' at row 1067942                               |
    | Warning | 1262 | Row 1067942 was truncated; it contained more data than there were input columns |
    [...]
    | Warning | 1265 | Data truncated for column 'source' at row 1073732                               |
    | Warning | 1262 | Row 1073732 was truncated; it contained more data than there were input columns |
    | Warning | 1265 | Data truncated for column 'source' at row 1082193                               |
    | Warning | 1262 | Row 1082193 was truncated; it contained more data than there were input columns |
    +---------+------+---------------------------------------------------------------------------------+
    50 rows in set (0.00 sec)
    
  16. E.M.Smith says:

    Making those (big) indexes on the temperature data has 326 MB rolled out to swap (I do have python/idle3 and the FireFox browser open which likely is why it’s gone over the 2 GB board memory.

    Making the index in the temp3 data took:

    Query OK, 0 rows affected (5 min 11.56 sec)         
    Records: 0  Duplicates: 0  Warnings: 0
    

    and making the temp4 index failed, so I’ve got to find out what I typed wrong ;-)

    ERROR 1072 (42000) at line 1 in file: '/SG/xfs/chiefio/SQL/tables/mkindex4': Key column 'abrev' doesn't exist in table
    

    It looks like that table was expanded by the “abrev” field and I’ll need to add it, reload, and index.

    This will likely mean some of the rest of the anomaly and statistics creating processes will fail too, so I’ll get to poke though them and validate / do-over.

    Making the monthly statistics for temps3:

    Query OK, 87360 rows affected (1 min 14.92 sec)
    Records: 87360  Duplicates: 0  Warnings: 0
    

    as opposed to 40 seconds, so 75/40 the speed ( 1.87 x faster for the N2).

    I’m actually impressed with this from the XU4. Yes, it is 8 cores ( 4 of them fast / big, 4 slower 32 bit little), but mysql/Mariadb seems to mostly be single core for heavy duty math and maybe 1/2 to 1/3 core load on two other cores likely doing I/O / housekeeping functions. As 32 bit and slower cores, that’s still pretty impressive performance. Then my browser and all have all those other cores to run in ;-)

    Computing the monthly stats on v4 ought not to depend on the added abrev field, so likely is fine. Here’s that result:

    Query OK, 328327 rows affected (9 min 30.04 sec)
    Records: 328327  Duplicates: 0  Warnings: 0
    
    Empty set (0.00 sec)
    

    How odd… it is FASTER than the N2 data (copied from above):

    Query OK, 328327 rows affected (11 min 50.25 sec)
    Records: 328327  Duplicates: 0  Warnings: 0
    
    Empty set (0.00 sec)
    

    Hmmm…. as it is computing things from temps4, perhaps those index fields paid off?

    INSERT INTO  mstats4 (stnID,month,mean,big,small,num,trang,stdev)
    SELECT stnID,month,
    AVG(deg_C),MAX(deg_C),MIN(deg_C),COUNT(deg_C), MAX(deg_C)-MIN(deg_C), STDDEV(deg_C)
    FROM temps4 
    WHERE deg_C>-90 
    GROUP BY stnID,month;
    

    Don’t see how… or maybe just moving 32 bit words around instead of 64 bit sometimes is a winner…
    At this point it will make the anomalies for v3.3 then barf on v4 as it does want the abrev field. Again unless there’s some hidden character typo…

    UPDATE: See below, but the abrev field IS there. I blew it on the index creation script, wrong table name.

  17. E.M.Smith says:

    Well, looks like it computed the v3.3 anomalies OK.

    Query OK, 5278201 rows affected, 65535 warnings (12 min 30.64 sec)
    Records: 5278201  Duplicates: 0  Warnings: 5272409
    
    +-------+------+--------------------------------------------+
    | Level | Code | Message                                    |
    +-------+------+--------------------------------------------+
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    [...]
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    +-------+------+--------------------------------------------+
    64 rows in set (0.02 sec)
    

    And 12 minutes vs

    MariaDB [temps]> source SQL/bin/LOAD/lanom3.sql
    Query OK, 5278201 rows affected, 65535 warnings (4 min 36.55 sec)
    Records: 5278201  Duplicates: 0  Warnings: 5272409
    

    4 1/2 minutes before. About a triple on a very math intensive step.

    OK, I’m going to go back and see what the deal is with the index on v4 now.

  18. E.M.Smith says:

    Ah, wrong table name.

    chiefio@odroidxu4:/SG/xfs/chiefio/SQL/tables$ cat mkindex4
    CREATE INDEX abrevx ON temps3 (abrev);
    

    So nothing wrong with the tables or data. I just typoed it when making this script.

    So nothing will fail, it just might take a very very long time ;-)

    I’m going to pop open another Mariadb session and use it to add the index. This will partly screwup whatever the timing is on whatever it’s doing now (I THINK it ought to be making the yearly stats table…) but hopefully not too badly.

  19. E.M.Smith says:

    Loading v4 anomalies table took a long time:

    Query OK, 15448107 rows affected, 65535 warnings (33 min 1.81 sec)
    Records: 15448107  Duplicates: 0  Warnings: 15444656
    
    +-------+------+--------------------------------------------+
    | Level | Code | Message                                    |
    +-------+------+--------------------------------------------+
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    [...]
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    | Note  | 1265 | Data truncated for column 'deg_C' at row 1 |
    +-------+------+--------------------------------------------+
    64 rows in set (0.03 sec)
    

    So 33 minutes instead of a bit under 8. Part of that will be my making the index field while this is running, but compared with a bit under 8 minutes, that’s over 4 x as long a run time.

    Also, making 2 indexes at once took /tmp over 1.5 GB:

    df /tmp
    Filesystem     1K-blocks    Used Available Use% Mounted on
    /dev/sdb5        4184064 1505640   2678424  36% /tmp
    
  20. E.M.Smith says:

    Slogging through making the index on temps4 while that other stuff was running sure slowed it down:

    MariaDB [temps]> source mkindex4
    Query OK, 0 rows affected (27 min 55.04 sec)        
    Records: 0  Duplicates: 0  Warnings: 0
    

    Also, at one point, over 800 MB were on swap and changing windows got way slow, so I eventually quit out of FireFox and Python/idle for a few minutes. It’s all better now ;-)

    So that time isn’t fair to compare to anything before, but it is interesting that the UX4 was OK with a major Python/ Idle3 session, a FireFox browser with 20 tabs open (and a couple of them WordPress edit or admin pages that do some heavy scripting loads) and a significant MariaDB session all at the same time.

    Only when I added the second MariaDB session also doing intensive (search a giant table) process of adding an index, was the total load “a bit much”.

    Here’s the time it took to make the three indexs on anom3

    Query OK, 0 rows affected (5 min 36.87 sec)         
    Records: 0  Duplicates: 0  Warnings: 0
    
    Query OK, 0 rows affected (4 min 53.08 sec)         
    Records: 0  Duplicates: 0  Warnings: 0
    
    Query OK, 0 rows affected (2 min 54.48 sec)         
    Records: 0  Duplicates: 0  Warnings: 0
    

    Which are not all that bad and about in line with everything else.

    Making indexes on anom4 use over 1 GB of /tmp all on their own. I just note in passing that when you are slinging a GB at /tmp, having it on “something fast” will matter, and likely more than having a fast CPU.

    I suspect that fore real speed on a dedicated SQL database server, you would want a system with some kind of very fast Solid State Disk for both the database itself, and /tmp.

    UPDATE:

    Then on the “doover” of making the anomalies v4 indexes, we have:

    MariaDB [temps]> source tables/mkanomindex4
    Query OK, 0 rows affected (11 min 27.49 sec)        
    Records: 0  Duplicates: 0  Warnings: 0
    
    Query OK, 0 rows affected (12 min 28.19 sec)        e
    Records: 0  Duplicates: 0  Warnings: 0
    
    Query OK, 0 rows affected (12 min 44.12 sec)        
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [temps]> 
    

    About 12 minutes each.

    It is probably worth mentioning that when you are writing a GB of /tmp and picking up the data from /var and they are located thus:

    root@odroidxu4:/# df
    Filesystem      1K-blocks      Used  Available Use% Mounted on
    [...]
    /dev/sdb5         4184064     37488    4146576   1% /tmp
    /dev/sdb3        20961280   9484104   11477176  46% /var
    

    (Where /dev/sdb4 is the physical partition entry that holds the logical partitions starting at 5)

    There will be SOME amount of head seeks between those two partitions. Not TOO horrible, as they are next to each other, but extant seeks all the same. I’d also note that also on that disk are:

    /dev/sdb6         4184064    136212    4047852   4% /lib
    /dev/sdb7        12572672   1989800   10582872  16% /usr
    /dev/sdb8         1038336     34728    1003608   4% /root
    /dev/sdb9         1038336     40656     997680   4% /bin
    /dev/sdb10        1038336     43184     995152   5% /sbin
    /dev/sdb11        1038336     39148     999188   4% /etc
    /dev/sdb12     1900148220  56708724 1843439496   3% /SG2/xfs
    

    So every time the operating system needs to run off to /lib or /bn or /sbin or /usr (or even /etc and /usr) it is going to take a head seek. Those are glacial physical events in comparison to everything CPU, GPU, Memor, etc. related. We’re talking like months are to minutes.

    So when I do things like launch FireFox or the calculator, it will step on the DB /tmp index building process for just a bit. As will swapping to /dev/sdb2 for tose hundreds of MB swapped.

    Where I doing this regularly on a professional system, I’d have all the system stuff on one disk and the database stuff on a different disk, with the most time critical partitions as “memory disk” or SSD disk.

    The “modern” way of doing this is to put in place a large LVM volume and let it sort it out, but I’m rather found of thinking about where I’m taking long vs short head seeks… and while a nice 8 disk RAID LVM can work wonders, well, there’s something to be said for keeping the system stuff away from the user stuff ;-)

  21. E.M.Smith says:

    Interesting…. MariaDB is different from MySQL in how it handles some updates. Doing the yrcastats table load I got these error messages:

    MariaDB [temps]>  source bin/Lyrcastats
    Query OK, 28423 rows affected, 5 warnings (2 min 55.67 sec)
    Records: 28423  Duplicates: 0  Warnings: 5
    
    +---------+------+---------------------------------------------+
    | Level   | Code | Message                                     |
    +---------+------+---------------------------------------------+
    | Warning | 1364 | Field 'big3' doesn't have a default value   |
    | Warning | 1364 | Field 'small3' doesn't have a default value |
    | Warning | 1364 | Field 'num3' doesn't have a default value   |
    | Warning | 1364 | Field 'trang3' doesn't have a default value |
    | Warning | 1364 | Field 'stdev3' doesn't have a default value |
    +---------+------+---------------------------------------------+
    5 rows in set (0.07 sec)
    

    That this link was helpful in explaining, if not removing:

    https://www.farbeyondcode.com/Solution-for-MariaDB-Field–xxx–doesn-t-have-a-default-value-5-2720.html

    To “fix it” I removed the “NOT NULL” from the v3 fields that ARE null at the first load of v4 data, then get updated in the second update part of the process:

    chiefio@odroidxu4:/SG/xfs/chiefio/SQL/tables$ cat yrcastats 
    CREATE TABLE yrcastats (
        year   CHAR(4) NOT NULL,
        abrev  CHAR(2) NOT NULL,
        mean3  FLOAT,
        mean4  FLOAT,
        big3   DECIMAL(7,2),
        big4   DECIMAL(7,2) NOT NULL,
        small3 DECIMAL(7,2),
        small4 DECIMAL(7,2) NOT NULL,
        num3   INTEGER,
        num4   INTEGER NOT NULL,
        trang3 FLOAT,
        trang4 FLOAT NOT NULL,
        stdev3 FLOAT,
        stdev4 FLOAT NOT NULL,
        PRIMARY KEY (year,abrev)
        ) ;
    

    Then a “drop table yrcastats” and recreating it, followed by running the Lyrcastats code:

    chiefio@odroidxu4:/SG/xfs/chiefio/SQL/tables$ cat ../bin/Lyrcastats
    INSERT INTO  yrcastats (year,abrev,mean4,big4,small4,num4,trang4,stdev4)
    SELECT year,abrev,
    AVG(deg_C),MAX(deg_C),MIN(deg_C),COUNT(deg_C), MAX(deg_C)-MIN(deg_C), STDDEV(deg_C)
    FROM anom4 
    GROUP BY year,abrev
    ;
    show warnings;
    
    UPDATE  yrcastats AS Y
    SET 
    mean3 = (SELECT AVG(A.deg_C) 
    FROM anom3 AS A
    WHERE Y.year=A.year AND Y.abrev=A.abrev),
    
    big3 = (SELECT MAX(A.deg_C) 
    FROM anom3 AS A
    WHERE Y.year=A.year AND Y.abrev=A.abrev),
    
    small3 = (SELECT MIN(A.deg_C) 
    FROM anom3 AS A
    WHERE Y.year=A.year AND Y.abrev=A.abrev),
    
    num3 = (SELECT COUNT(A.deg_C) 
    FROM anom3 AS A
    WHERE Y.year=A.year AND Y.abrev=A.abrev),
    
    trang3 = (SELECT MAX(A.deg_C)-MIN(A.deg_C) 
    FROM anom3 AS A
    WHERE Y.year=A.year AND Y.abrev=A.abrev),
    
    stdev3 = (SELECT STDDEV(A.deg_C) 
    FROM anom3 AS A
    WHERE Y.year=A.year AND Y.abrev=A.abrev)
    ;
    show warnings;
    

    A somewhat complex bit of SQL, but really just some simple calculations and storing them for easy reporting.

    Answering the question: “What are the statistics for a given country in a given year for both v3.3 and v4 data?”

    In any case, it ran fine after that…

    MariaDB [temps]> source bin/Lyrcastats
    Query OK, 28423 rows affected (2 min 57.24 sec)
    Records: 28423  Duplicates: 0  Warnings: 0
    
    Empty set (0.01 sec)
    

    This doesn’t really matter much as all I was using were the (un-involved) mean3 average of anomalies and mean4 average of anomalies values for finding how the anomalies differed.

    But as a note “for the future”, there it is.

    IIRC, all those “UPDATE” processes drag on something horrible, so I’m off to asses the contents of my Tequila Bottle ;-)

    Hey, it’s getting into the later afternoon here and Its been a long day… a bit of sparkle could really help my attitude…. maybe even my blood pressure and longevity. It will certainly help my computer equipment to remain intact ;-)

    Though I am wondering just a bit if all this might have finished faster had I just done the 70 graphs on the R. Pi M3 and not wondered about bringing the XU4 copy up to date ;-)

    FWIW, the above “update” processes do peg one core at 100%. But that leaves the other 7 free for all the other stuff I’m doing … Something magical about an 8 core CPU ;-)

  22. E.M.Smith says:

    What a difference an index can make…

    In the program that loads the Year Comparative Anomaly STATisticS table (yrcastats) it does a join on “abrev”. I’d forgotten to put that index create into the script, then didn’t do it long hand.

    After running all night, while the anom4 data were loaded, none of the anom3 data had made it yet. I did a table drop, made the index, remade the table, and did the reload of the data:

    The key bit (no pun intended) being this phrase:

    “AND Y.abrev=A.abrev),”

    In yrcastats the abrev field is a primary key so gets an index automatically. It was n anom3 where it was doing a (probably linear) search of the entire data set for every record to be matched. Ooops. A bit slow… So 3 minutes of “make an index”:

    MariaDB [temps]> CREATE INDEX abrevx ON anom3 (abrev);
    Query OK, 0 rows affected (3 min 32.42 sec)         
    Records: 0  Duplicates: 0  Warnings: 0
    

    Then drop and remake the table:

    MariaDB [temps]> drop table yrcastats;
    Query OK, 0 rows affected (0.43 sec)
    
    MariaDB [temps]> describe yrcastats
        -> ;
    ERROR 1146 (42S02): Table 'temps.yrcastats' doesn't exist
    
    MariaDB [temps]> source tables/yrcastats
    Query OK, 0 rows affected (0.56 sec)
    

    And the whole load process drops to under 3 1/2 hours:

    MariaDB [temps]> source bin/Lyrcastats
    Query OK, 28423 rows affected (3 min 11.12 sec)
    Records: 28423  Duplicates: 0  Warnings: 0
    
    Empty set (0.00 sec)
    
    Query OK, 28423 rows affected (3 hours 20 min 52.56 sec)
    Rows matched: 28423  Changed: 28423  Warnings: 0
    
    Empty set (0.00 sec)
    
    MariaDB [temps]> 
    

    For comparison, here’s the comment from making it on the Pi M3:

    https://chiefio.wordpress.com/2019/04/09/ghcn-v3-3-vs-v4-selected-country-anomaly-differences/

    This is rather slow on the UPDATE section. There may well be a faster way… The initial insert of the v4 data that’s much larger in volume than the v3.3 data takes only 4 minutes on the Raspberry Pi M3. Then the Update to add the v3.3 values takes over 4 hours…

    MariaDB [temps]> source bin/Lyrcastats
    Query OK, 28423 rows affected, 5 warnings (4 min 7.05 sec)
    Records: 28423  Duplicates: 0  Warnings: 5
    
    Query OK, 23987 rows affected, 17744 warnings (4 hours 20 min 58.52 sec)
    Rows matched: 28423  Changed: 23987  Warnings: 17744
    

    So the XU4 is about an hour faster (and both are too slow to be usable without the index).

  23. jim2 says:

    Have you considered an alternative to Matlab? Here are some that work very much like it, but open source. Octave, fore example, might work on the ON2.

    https://opensource.com/alternatives/matlab

  24. E.M.Smith says:

    @Jim2:

    No, I’d not. Why? Because I’m a Nooby to Python and don’t know about the alternatives.

    But now that you’ve told me ;-)

    So thanks for the pointer, I’ll give it a look. I’m not giving up on the N2 or RockPro64. Eventually they will get updates that fix this. IF I can get a working alternative sooner, I’ll do that too. There’s a certain amount of time when I can do “development” work as something else is running on the database / graphing station. So easy enough for me to “take a look”.

    BTW, 100% of all the Python I’ve ever written has been published on these pages, so you can see exactly my experience level 8-} I have no illusions about my coding ability in Python, it is primitive compared to any other language I’ve used. Any help, advice, or criticism welcomed.

  25. jim2 says:

    I’ve never used Python. When I feel the need to create charts, I use R. That said, I’m not an R expert either.

    However, there is no reason one can’t generate the data using one language, the graph it in another. R likes dataframes. It’s just another data format and the dataframe format, I’m pretty sure, could be generated using Python.

    But Octave is supposed to be very similar to Matlab. I’ve played with it a bit. It would probably make an easier transition for you.

  26. E.M.Smith says:

    @Jim2:

    A fundamental problem is the explosion of programing paradigms and approaches, then the plethora of implementations inside each one. We’ve reached the point where it takes 100% of one staff time to just evaluate the choices. Well, as a total staff of 1, that kinda sucks….

    So you end up in a space where it is most productive to just quasi-randomly “pick one” and start producing. So that’s what I’ve done.

    Are there better? Almost certainly. BUT: Can I FIND and EVALUATE and LEARN better before i’d be done with “good enough”? Probably not…

    As an inherent optimizer that pains me greatly, but there it is…

    The optimal choice is not to optimize….

    My answer to all that is to just “Pick one and get started” then sequentially improve along the way. IFF you hit a roadblock, search more widely. It seems to work OK. So, for example, I dusted off my old FORTRAN skills to port GISStemp as it was written in FORTRAN. Shortest path. Now I’m a noob in Python, but I’m giving it a try as a lot of stuff is written in it. (Not impressed so far, but it’s not horrible). I’m willing to change horses if something is clearly better, and i devote maybe 15% of my time to “looking for better”, but I’m always willing to ride this crappy horse I’m on to the end of the line if that’s what gets me there first.

    So I looked at R and at Python and saw no real decision criterion between them. I tried a bit of each and was able to do more faster in Python (that says more about me than about the two languages). What matters most is avoiding “Analysis Paralysis” and making some progress to goal. So that’s where I am. I have “good enough” in hand and a few percent of time spent on “can I find better without losing progress to goal?”

  27. jim2 says:

    I was suggesting Octave only because Matlab had problems on the ON2.

    At work, I fight to get to use new technology. Opposite problem :)

    WRT R – it is R-cane. It does take longer to pick it up than most others. You have to learn a new mindset, not just a new but more traditional programming language.

  28. E.M.Smith says:

    Digging into some of the warnings, and fixing them. Turned “and” into & on the one too long name.

    The 50 “warns” on truncation for too much data turns out to be significant, unlike what I thought.

    The “root cause’ looks to be that the backslash character is “special” in mysql/MariaDB and is the escape character – and that looks to be true even in the input data. So it is “escaping” the EOL field (at least this is my speculation) and that then means the following line is treated as part of the same line. As it exceeds the field length, it gets truncated (i.e. that data is tossed).

    Rather than doing gymnastics to keep the \ in play (it has some flag meaning), I’m just going to replace it in the input data with a different character.

    As this is only 50 lines, it ought not to have any significant effect on the graphs produced from hundreds of thousands of lines, but I’ll check what country that data is from, and then spot check those graphs and only update them if there is something visible. (i.e. a 1/100000 C shift in the location of a dot will not be visible on a graph with 0.01 C granularity. so the graph is in fact accurate).

    I’m also going to just force all the European site data for GHCN v3.3 Russia & Kazakhstan into Asia, and just the one abbreviation for the country, as that will conform v3.3 with v4 for comparison purposes. I expect that WILL change the Russia and Kazakhstan anomaly graphs, so will make updated versions of them as an added update where appropriate.

    As I’m doing this I’m going to make some other minor changes to the database and reload all the data, redo all the statistics generation, etc. It will take a day or two to get it all done, I think. At that time I’ll post a new article with the changes noted and the load metadata (that ought to show now warnings remaining).

  29. E.M.Smith says:

    Well that’s not good.

    After spending far too long trying to escape the escape character in bash for grep (all the “usual” escapes didn’t work, so ‘\’ gave “trailing backslash” or something like that as did “\” and \\ and several other trys… I finally just pulled the whole giant temperature input file into the vi editor.

    There I did a global regular expression replacement of \ with ^ (just randomly chosen). It takes a while for the file to load on a R.Pi M3 (including rolling about 3/4 GB of something to swap) and then another good long time for the “find and replace” to run. that command is:

    :g/\\/r//^/g
    

    Which says “get the : prompt and then tell it g(lobally) i.ie. in all lines of the file / search for escaped escape character and when you rind it r(eplace) it /^/ with a ^ g(lobally) i.e. anywhere it happens on that line, all instances.

    That reported 2188 substitutions on 2188 lines.

    What THAT implies (says) is that there are only lines with one instance to almost certainly all of them in the last position, and there are 2188 such lines.

    That was the “penny drop” moment… What that means is that since only 50 lines are complaints, there are 50 BLOCKS of such final position \ escape characters and the load process is treating each BLOCK as a long single concatenated line and tossing everything after the first line. So there are 2188-50= 2138 lines tossed in the bit bucket during the load.

    So I’ve got 50 blocks of lines to look at, see what country code is involved, and then see what graphs might be wrong / needing a QA check.

    Figuring this pretty much means a reload from scratch, I’ve gone ahead and “bit the bullet” to clean up a loose end in the table schemas. I’d used “country” in the first couple of tables, then swapped to ‘cnum’ and ‘abrev’ to distinguish the v3.3 3 CHAR country number from the v4 2 CHAR country abbreviation. I’ve gone back to the country, anom3, etc. tables and made them match the later choices. I’ve also updated all the data loading programs appropriately. I’ve not (yet) updated any reporting / graphing programs that might need the change, but as it doesn’t propagate to the later stages (they already have it) the bulk of all those ought to be unchanged.

    This also implies I need to do a full on new posting of all the schema, load, etc. codes with a consistent set of new “stuff” in them for a “one place to get it all” to avoid folks needing to piece things together over a 1/2 dozen postings with different ‘vintages’…

    I’m still pretty sure these \ lines won’t have any major impact. It is only in GHCN v4, it is a couple of thousand out of many many thousands of lines, and it looks like it is limited in scope to just a few physical places. In particular, looking in the ghcnm-flags.txt file, it says that the backslash is the code for data originating from “ISTI russsource-antarctica”.

    That means there will be 50 blocks of russource-antarctica that are botched, plus the 50 lines following the last line of each block. That will be one single data item (one month average for one thermometer in one place in one year) scattered through all the other 16,811,736 items. Then the loss of a couple of thousand of Antarctic items.

    So at this point I’m fairly certain that any graph outside Antarctica will have zero impact visible in it. I’ll need to redo the one Antarctic graph and see if it changes… So that’s a bit of a relief ;-)

    It looks like they have used every character on my keyboard for a different source, so to get a different character I’ll need to resort to Unicode. For now, I’m just changing it to ^ (despite that meaning “start of line” in grep / editor searches) as it is not causing trouble (it is presently in use for russource-australia_wwr but since I’m not doing anything with source anyway, it will not matter if one russouce gets attributed with both data items. “Sometime later” I’ll find an alternative character ;-)

    So, for now, I’m going to use that version of the v4 input data, and the newly preened table description and load programs, to reload it all on the R.Pi M3 and then check out the Antarctic graphs.

  30. Pingback: GHCN v3.3 vs v4 – Top Level Entry Point | Musings from the Chiefio

Comments are closed.