Web Design Discussion > General Web Design Discussion
Setting up an instance of TM's DB and web front end
yakra:
--- Quote from: Jim 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.
--- End quote ---
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.
--- Quote from: Jim 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.
--- End quote ---
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. :)
--- Quote from: Jim on June 27, 2019, 05:36:57 pm ---Looks like I should also try those DB options and might be able to get site update time down significantly.
--- End quote ---
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.
Jim:
More README.md enhancements just in, thanks for the suggestions.
On noreaster, the disk is this on the spec sheet:
--- Code: ---3.5 inch 2TB SATA 7.2k RPM HDD,FPWS
--- End code ---
and this is what FreeBSD says about it when it boots:
--- Code: ---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>
--- End code ---
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.
yakra:
--- Quote from: Jim on June 27, 2019, 10:13:12 pm ---More README.md enhancements just in, thanks for the suggestions.
--- End quote ---
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.
--- End quote ---
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`.
--- End quote ---
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?
--- Quote from: Jim on June 27, 2019, 10:13:12 pm ---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.
--- End quote ---
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.
--- Quote from: Jim on June 27, 2019, 10:13:12 pm ---Definitely worth checking on optimizing the number of entries per INSERT,
--- End quote ---
Going to try this out next.
--- Quote from: Jim on June 27, 2019, 10:13:12 pm ---and given that noreaster has all those cores,
--- End quote ---
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
--- Quote from: Jim on June 27, 2019, 10:13:12 pm ---breaking into multiple files that can be ingested in parallel could be a big win.
--- End quote ---
Haven't really read up on the mysqlimport link I posted. In any case, there are multiple ways to skin this cat.
yakra:
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
Jim:
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.
Navigation
[0] Message Index
[#] Next page
[*] Previous page
Go to full version