{"id":879,"date":"2014-07-17T16:45:17","date_gmt":"2014-07-17T20:45:17","guid":{"rendered":"http:\/\/blog.uvm.edu\/ctl-projects\/?p=879"},"modified":"2014-07-17T16:45:17","modified_gmt":"2014-07-17T20:45:17","slug":"geology-landscape-change-project-exploded-chapter-2","status":"publish","type":"post","link":"https:\/\/blog.uvm.edu\/waw\/2014\/07\/17\/geology-landscape-change-project-exploded-chapter-2\/","title":{"rendered":"Geology Landscape Change project exploded Chapter 2"},"content":{"rendered":"


On Jul 17, 2014, at 10:24 AM, Scott Dellinger wrote:<\/span><\/p>\n

[Jim Lawson and I were discussing yesterday afternoon who to speak with about the landscape project\u2019s maintenance, so I\u2019ll let this serve tentatively as confirmation of who belongs on\u00a0this thread. \ud83d\ude42 ]<\/p>\n

The landscape code for image searches performs some very inefficient searches against the database in ways that would not be so apparent with fewer images, but start quickly making\u00a0queries pile up as the image count increases and multiple search engines start accessing your web pages simultaneously. \u00a0Despite there only being 70 some thousand rows in your image\u00a0tables in the database, these types of database queries in large numbers have recently been seriously impacting performance of WebDB services for other users as well as your own. \u00a0Short\u00a0of temporarily banning your user account from access, yesterday our best option in the short term was (further) restricting the number of simultaneous connections permitted by the \u201cglcp\u201d\u00a0WebDB user, to 15. \u00a0This is why you encountered an error about exceeding max_user_connections.<\/p>\n

As a matter of scalability, 70 thousand rows is not a lot. \u00a0Hundreds of millions of rows would be. \u00a0However, database servers are not designed to efficiently handle queries such as \u201clist\u00a0all images without restriction and toss out all but one in the middle\u201d, which is the main culprit type of query occurring here. \u00a0This is very, very different from a conceptual \u201cshow me\u00a0image number 5,002\u201d, which can be extremely fast.<\/p>\n

Because these queries are hurting performance on WebDB for other users at this point, we cannot raise the concurrent connection limit again unless these problems are resolved in some way.\u00a0\u00a0Obviously, the current state is not optimal for you or your users. \u00a0I am not sure how this software is being regularly maintained at present, but here are the non-mutually-exclusive\u00a0options I can think of, going forward:<\/p>\n

\u2022\u00a0Move the landscape database onto a MySQL service on your existing dedicated web server. \u00a0You would not have a connection limit this way and would not impact anyone else. \u00a0This will\u00a0not solve performance issues, which will continue getting worse over time.<\/div>\n
\u2022\u00a0Put a caching proxy server (probably Varnish) in front of your web server. \u00a0For pages that are accessed frequently, this would query the database however many times one page\u00a0requires, then not again for that page until the page expires from the cache. \u00a0A basic Varnish setup for a web application that is not overly complicated can be easy to set up.\u00a0\u00a0This would not solve the database issues, but might put a big dent in their impact.<\/div>\n
\u2022\u00a0Rewrite the inefficient database queries to access data more efficiently. \u00a0Selecting a single row at a time from a table should not involve querying half the table before picking\u00a0out one row. \u00a0There must either be some reasonable way to search by a specific identifier to retrieve one row in the first place, or at least have a periodic job that performs the\u00a0regular searches and caches the results in a way that itself can be queried by an identifier.<\/div>\n

Who is currently maintaining the landscape project code? \u00a0SAA would be happy to offer advice, but cannot do more than that at present.<\/p><\/blockquote>\n

So while on vacation…<\/p>\n