Author Topic: Setting up an instance of TM's DB and web front end  (Read 304 times)

0 Members and 1 Guest are viewing this topic.

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2055
  • Last Login:Today at 12:35:30 pm
Setting up an instance of TM's DB and web front end
« on: June 26, 2019, 07:46:05 pm »
Don't know if there's an existing topic I'm better off merging this into, but...

I've been following the instructions in Web/README.md and setting up a TM mirror on a CentOS system. There are some kinks to iron out; I'm still learning as I go.

A few observations & questions:

From Web/README.md:
Quote
This [lib/tm.conf] file needs to be readable by the web server but should not be served by the web server. Configure Apache to ensure this.
How do I do this exactly? I tried something in httpd.conf, and I believe I even restarted httpd; no luck.

Updates on the updates page are still sorted by date, but in a different order within: the two CZE II603 entries from 2019-06-24 are swapped, as are the two WV WV817 entries from 2019-04-21. Appears deterministic on each site; reloading does not change it. On each server, one pair of entries is kept in CSV order, and the other swapped. Not a deal-breaker by any means, just something odd I noticed.

Forum links: lib/tmheader.php links to "/forum", while index.php links to "http://forum.travelmapping.net"

From devel/tools.php contains a  hard link to "http://travelmapping.net/wptedit/", rather than a link relative to the DocumentRoot directory.

graphs/index.php is broken -- below the "Filter by graph type" dropdown, I get:
Quote
\n"; mysqli_report(MYSQLI_REPORT_STRICT); try { $tmdb = new mysqli($tmdbhost, $tmdbuser, $tmdbpasswd, $tmdbname); } catch ( Exception $e ) { //echoecho "
Failed to connect to database ".$tmdbname." on ".$tmdbhost." Please try again later.

"; exit; } $result = $tmdb->query("SELECT * FROM graphs ORDER BY descr, format"); $counter = 0; $prevRow; while ($counter < $result->num_rows) { // get three entries: collapsed, simple, then traveled for each $crow = $result->fetch_assoc(); $srow = $result->fetch_assoc(); $trow = $result->fetch_assoc(); $counter += 3; if ($crow == NULL || $srow == NULL || $trow == NULL) { // should produce some kind of error message continue; } // build table row (was: class=collapsed) echo "\n"; echo "\n"; echo "\n"; } $values = array(); $descr = array(); $result = $tmdb->query("SELECT * FROM graphTypes"); while ($row = $result->fetch_array()) { array_push($values, $row[0]); array_push($descr, $row[1]); } ?>
I'm still too new to sql & php, and how to configure it for TM, to know what's going on here. :)

Other than that, the DB seems to be working. I was able to query a few of the tables, and what I saw made sense. stat.php, user/index.php, user/mapview.php, user/region.php, and user/system.php all look good.

README.md does not cover what we need to do to get leaflet working. I was flying blind but tried to fly fast, and didn't do much research.
Initially, none of the maps worked. Then, just to make sure I have everything in place, I copied the /leaflet/ directory from noreaster. I don't know if there's anything specific to Jim's instance of leaflet I shouldn't be copying, any keys/tokens or anything (nothing immediately obvious from the contents); Jim, let me know if I shouldn't be doing that! FWIW, this isn't a public server; it's only visible to a few whitelisted IPs.
In any case, where's the proper place to find these files for someone setting up an instance of a TM server?
After copying /leaflet/, I got the aforementioned maps to work. Those in the HB still don't. Additionally copying the /leaflet-1.5.1/ directory didn't help.
Not sure what's going on here; my first thought is that it may be related to the recent Leaflet version upgrade, and maybe some changes to the production server haven't made it back to the Web repo yet. I'll nose around lab2 & noreaster, have a look at various .php & .js files, and see if any filesizes differ...

Ingesting the .sql file took a bit over an hour on my machine. I don't understand why; my CPU use meters were all barely above 0% during the process. If disk access were the bottleneck here, it should have only taken ~20s to read the file... Jim, is this also your experience?
It rather dwarfs the time spent running the siteupdate progam itself. I wonder whether playing around with these values here would have an effect on performance...

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2055
  • Last Login:Today at 12:35:30 pm
Re: Setting up an instance of TM's DB and web front end
« Reply #1 on: June 26, 2019, 08:18:51 pm »
/lib:
FileSize in Web repoSize on noreaster
jquery.tablesorter.pager.js(not present)4077
tmjsfuncs.js3605736061
tmphpfuncs.php1539015408

Edit: I see that these just refer to /leaflet-1.5.1 instead of /leaflet. No changes to maps; HB is still broken.
Forgot to mention, all I see on the HB is the page header. Not only is there no map; there's no Route Stats or Waypoints tables either.

Is shieldgen.php in the Web repo? If so, where do I find it?
After copying this from noreaster, I got the HB to work on lab2. WOOT!

I'd also like to understand the various tm.conf, tm.conf.standard, and tm.conf.updating in /lib/.
Edit:  OK, I see we have this & this in localupdate.sh. Got it. How do they differ?
Schmedit: Let me guess:
Code: [Select]
TravelMapping
travmap
[password]
localhost
vs
Code: [Select]
TravelMappingCopy
travmap
[password]
localhost
?
« Last Edit: June 27, 2019, 02:24:42 am by yakra »

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2055
  • Last Login:Today at 12:35:30 pm
Re: Setting up an instance of TM's DB and web front end
« Reply #2 on: June 27, 2019, 03:30:54 pm »
Ingesting the .sql file took a bit over an hour on my machine. I don't understand why; my CPU use meters were all barely above 0% during the process. If disk access were the bottleneck here, it should have only taken ~20s to read the file... Jim, is this also your experience?
It rather dwarfs the time spent running the siteupdate progam itself. I wonder whether playing around with these values here would have an effect on performance...
https://stackoverflow.com/questions/29643714/improve-speed-of-mysql-import
https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/
https://dba.stackexchange.com/questions/13446/slow-load-speed-of-data-from-mysqldump
https://medium.com/@benmorel/high-speed-inserts-with-mysql-9d3dcd76f723
way out in the future or never at all

Setting innodb_flush_log_at_trx_commit=0 helped big time. Down to 23m13s to import TravelMapping.sql
« Last Edit: June 27, 2019, 04:12:04 pm by yakra »

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1490
  • Last Login:Today at 06:13:33 am
Re: Setting up an instance of TM's DB and web front end
« Reply #3 on: June 27, 2019, 05:35:40 pm »
Thanks for all of these.  I think it's very good to ensure we can install on another server and keep the documentation and code updated to facilitate this.  I'm making some fixes and will add some things to the README.md.

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1490
  • Last Login:Today at 06:13:33 am
Re: Setting up an instance of TM's DB and web front end
« Reply #4 on: June 27, 2019, 05:36:57 pm »
The DB takes under 5 minutes to ingest on noreaster.  I know it's a pretty high-end server with lots of memory but I'm amazed to see such a difference on yours.  Looks like I should also try those DB options and might be able to get site update time down significantly.

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2055
  • Last Login:Today at 12:35:30 pm
Re: Setting up an instance of TM's DB and web front end
« Reply #5 on: June 27, 2019, 09:51:50 pm »
Thanks for all of these.  I think it's very good to ensure we can install on another server and keep the documentation and code updated to facilitate this.  I'm making some fixes and will add some things to the README.md.
I'd also mention:
CREATE DATABASE TravelMappingCopy; and GRANTing permissions to travmap & travmapadmin
• replacing tm.conf with tm.conf.standard & tm.conf.updating, and creating a symbolic link to tm.conf.standard to start out.

The DB takes under 5 minutes to ingest on noreaster.  I know it's a pretty high-end server with lots of memory but I'm amazed to see such a difference on yours.
I don't think memory's coming into play in my case. Memory usage tops out at about 1.3 GB (out of 8) while importing, so I'm not paging. Increasing the value of innodb_buffer_pool_size had no effect.
Disk access seems to be the bottleneck here. There's all the writing of whatever back-end format MySQL uses, and/or logging going on. I've read reports online that having solid state drives can help quite a bit. And of course, a well-done RAID array. What's noreaster using? Solid state? Magnetic? RAID?
This CentOS system ("lab2") has a really sluggish disk subsystem, particularly when writing. Writing WaypointQuadtree.tmg takes about 10s, compared to 0.2s on BiggaTomato, which has an unremarkable 200GB Maxtor from ~2003ish. Writing nmp_merged files is painful too: 9.6s on lab2 vs 1.5s on BiggaTomato.
I've also read that attaching a SATA drive to a SAS interface can slow things down; you won't get full SATA speed. If true, that could be coming into play here. I have a bunch of extra drives (still SATA though), so I can give a RAID array a go ion the future if I get bored. :)

Looks like I should also try those DB options and might be able to get site update time down significantly.
In the meantime, innodb_flush_log_at_trx_commit=0 helped a lot of course. Playing with innodb_flush_log_at_timeout also looks promising; I'll give that a go shortly. And then, the other recommendations at the links above that I've not even looked into yet.
Per the 4th link, finding a sweet spot of number of inserts per query may help too. Adding a commandline switch to siteupdate to customize this will be a piece of cake, and then we can play around with finding a sweet spot.

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1490
  • Last Login:Today at 06:13:33 am
Re: Setting up an instance of TM's DB and web front end
« Reply #6 on: June 27, 2019, 10:13:12 pm »
More README.md enhancements just in, thanks for the suggestions.

On noreaster, the disk is this on the spec sheet:

Code: [Select]
3.5 inch 2TB SATA 7.2k RPM HDD,FPWS
and this is what FreeBSD says about it when it boots:

Code: [Select]
da0 at mpr0 bus 0 scbus0 target 3 lun 0
da0: <ATA ST2000DM001-1ER1 CC27> Fixed Direct Access SPC-4 SCSI device
da0: Serial Number Z4Z8SHCY
da0: 600.000MB/s transfers
da0: Command Queueing enabled
da0: 1907729MB (3907029168 512 byte sectors)
da0: quirks=0x8<4K>

For DB optimization, I'm hesitant to change settings just yet since I don't want to pay any price on reads to speed up writes.  But I think it's very useful to investigate and try them at some point.  Definitely worth checking on optimizing the number of entries per INSERT, and given that noreaster has all those cores, breaking into multiple files that can be ingested in parallel could be a big win.

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2055
  • Last Login:Today at 12:35:30 pm
Re: Setting up an instance of TM's DB and web front end
« Reply #7 on: June 28, 2019, 12:18:16 am »
More README.md enhancements just in, thanks for the suggestions.

A few more:

"root of the directory server" -> "root directory of the server"

Quote
Note that the `fonts` directory is not updated by this script, and those files will need to be transferred separately.
On the one hand, maybe put it into updateserver.sh, if it's recommended to initially populate the root directory?
OTOH, anyone undertaking setting a server up is going to need at least a little Unix knowledge, and should know how to cp -r...
Back on the first hand, maybe the fonts could get updated at some point in the future, and...

Quote
The JS files for these should be placed into a location so they can be read by the code generated by `tm_common_js` in `lib/tmphpfuncs.php`.
Would it be more clear to specify just putting the leaflet directory in the root directory?
Also, someone could conceivably get a future version of the leaflet files. Looks like they come in a .zip just named "leaflet". Could make the "1.5.1" in the directory name moot or confusing; maybe revert to just "leaflet" instead?



For DB optimization, I'm hesitant to change settings just yet since I don't want to pay any price on reads to speed up writes.  But I think it's very useful to investigate and try them at some point.
The one setting I've changed so far at least, I don't believe is something that affects reads. IIUC it can affect how hosed your data is in the case of a crash. In my case, with every update I'll be repopulating the DB essentially from scratch, and I don't care about that. :)
I definitely hear you about the reads though. There'll be a lot more of those on a real, public, production server. I'm not too sure yet what lurks in the other mysqld setting I've not yet looked into.

Definitely worth checking on optimizing the number of entries per INSERT,
Going to try this out next.

and given that noreaster has all those cores,
ISTR you once referring to 20 HyperThreaded cores. Searched the forum, GitHub, emails, can't find the post.
Is it a 2x10 setup?
Lab2 has a Xeon E5-2470. 2x8. Not bad for something my friend's data center was going to throw in the garbage. :D

breaking into multiple files that can be ingested in parallel could be a big win.
Haven't really read up on the mysqlimport link I posted. In any case, there are multiple ways to skin this cat.

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2055
  • Last Login:Today at 12:35:30 pm
Re: Setting up an instance of TM's DB and web front end
« Reply #8 on: June 28, 2019, 03:16:26 am »
Items to take a look at:
innodb_flush_method Tested first. Didn't seem to do much. May come back to it later. Again, after disabling doublewrite: 2m51s
log_bin (how-to-disable) <--HELL YEAH. Down to 2m48s! ("From MySQL 8.0, binary logging is enabled by default")
innodb_log_buffer_size No effect
innodb_log_file_size No effect
innodb_doublewrite Down to 2m21s
foreign_key_checks mysqld fails to restart
innodb_buffer_pool_instances Doesn't look likely; ignoring
« Last Edit: June 28, 2019, 04:25:08 am by yakra »

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1490
  • Last Login:Today at 06:13:33 am
Re: Setting up an instance of TM's DB and web front end
« Reply #9 on: June 28, 2019, 09:51:23 am »
Addressed or created issues for some of these.

The leaflet location is in part to help HDX find it easily, but I'm open to reorganization for ease of configutation/update.  The version number was specified explicitly at first so I wouldn't cause problems with the production server when experimenting with the new version on the test server.

On noreaster we have this:

Dual Intel Xeon Processor E5-2630 v4 (10C, 2.2GHz, 3.1GHz Turbo, 2133MHz, 25MB, 85W)

The OS sees it as 40 virtual cores with hyperthreading.  So yes, we can throw lots of concurrency at it and it should be happy to help.

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2055
  • Last Login:Today at 12:35:30 pm
Re: Setting up an instance of TM's DB and web front end
« Reply #10 on: June 28, 2019, 01:06:12 pm »
The leaflet location is in part to help HDX find it easily, but I'm open to reorganization for ease of configutation/update.
I think its current location in DocumentRoot makes sense.

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2055
  • Last Login:Today at 12:35:30 pm
Re: Setting up an instance of TM's DB and web front end
« Reply #11 on: June 29, 2019, 11:45:52 am »
Updates on the updates page are still sorted by date, but in a different order within: the two CZE II603 entries from 2019-06-24 are swapped, as are the two WV WV817 entries from 2019-04-21. Appears deterministic on each site; reloading does not change it. On each server, one pair of entries is kept in CSV order, and the other swapped. Not a deal-breaker by any means, just something odd I noticed.
More of a deal-breaker: Right now, as of
HighwayData @ eb32463dc3d59802a4a85ff4b38e127ffe8b0600
UserData @ 546c981890969f2d97e7df6f2b3d3d7852267f89
http://travelmapping.net/user/region.php?units=miles&u=yakra&rg=ME
Right now, there are 2 pairs of travelers with the same % (to 2 decimal places) value in the tables:
wadsteckel @ 1327.02 mi & drebbin37 @ 1326.51 mi, with 20.85%
new_friends_gr @ 104.60 mi & foresthills93 @ 104.06, with 1.64%
They're sorted properly on noreaster, but swapped on lab2. Are the tables sorted by % rather than Distance Traveled?

Edit: Observed on noreaster too:
UserData @ 1fd6db1692f28c6810781fdfa29380b719079473
http://travelmapping.net/user/region.php?units=miles&u=a3&rg=NY
roukan listed before clong listed before moshitea, ran4sh listed before bogdymol, etc.
« Last Edit: June 29, 2019, 11:51:56 am by yakra »

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1490
  • Last Login:Today at 06:13:33 am
Re: Setting up an instance of TM's DB and web front end
« Reply #12 on: June 29, 2019, 12:50:04 pm »
The tablesorter library we use is, I think, pretty outdated and not used properly in all cases.  It's been on the radar for a long time (https://github.com/TravelMapping/Web/issues/183) but I haven't been able to make it a priority myself and continue to hope we'll get a web design expert interested in contributing a better web front end to our project.

Offline yakra

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 2055
  • Last Login:Today at 12:35:30 pm
Re: Setting up an instance of TM's DB and web front end
« Reply #13 on: June 30, 2019, 12:21:28 pm »
Lab2 has some issues.

1.) Apache won't serve /graphdata/, /logs/, /nmp_merged/, /stats/, or their contents. I think it's SELinux related. Gotta learn how to do the things before I do the things.
Fixed. Yes, it was SELinux context.

2.) graphs/index.php DB access failure, as outlined in the OP. I can't imagine this being SELinux context, if the rest of the DB queries work, but don't see anything else obviously causing it either. I'll tackle issue #1 first.
Fix: https://github.com/TravelMapping/Web/pull/309

3.) Route maps in the HB lack waypoint markers and segment polylines when an alternate dbname is specified in the URL and the route is not in the primary database. We could attempt to recreate this on noreaster, and see if it's a bug on my system, or something that crept into the TM code, maybe around the time of the change to Leaflet.
Jim, I've left a DB file for this purpose at ~yakra/TravelMapping/DataProcessing/siteupdate/cplusplus/TravelMapping.sql
« Last Edit: June 30, 2019, 06:55:53 pm by yakra »

Offline Jim

  • TM Collaborator
  • Hero Member
  • *****
  • Posts: 1490
  • Last Login:Today at 06:13:33 am
Re: Setting up an instance of TM's DB and web front end
« Reply #14 on: June 30, 2019, 07:51:11 pm »
Want me to load that .sql file into TravelMappingTest?