Weird perf issue for a specific database

[justinclift]

A recently user uploaded public database is being extremely slow to access through the webUI:

    https://db4s-beta.dbhub.io/AnsenIO/siren

Even re-viewing columns which should clearly be in Memcached is extremely slow (multiple seconds), as if the database is being pulled from Minio on each request.

Other databases aren't having this problem, so it's not something like a system wide load problem that hasn't been noticed.

[chrisjlocke]

I can try later removing various tables? Might be something wacky with a specific table?

[justinclift]

Ahhh, good thinking. Yeah, give that a shot. :smile:

[justinclift]

Ahhh, I think it only has one table. But yeah, maybe try nuking 1/2 the rows, then a different set of rows (etc), or maybe even see if it happens when emptied of all rows. If we can figure out what's causing it, we'll have something to work with. :smile:

[chrisjlocke]

Viewing the table and paging down works fine for me. No real lag? The only issue was the paging controls are in the middle of the 'virtual' table, not the screen, so you have to keep scrolling to find the paging controls.

Can I sugggest the paging controls are left aligned?

[chrisjlocke]

extremely slow (multiple seconds)

3 seconds or 10? I presume you're talking about simply paging? On my machine / connection (using Chrome) it takes approx 1 second from clicking the 'Down page' button.

[justinclift]

Hmmm, it's taking ~7 seconds or so for me. 😦

And yeah, definitely need to figure out a better approach for the paging arrows. Already have a ToDo list item in the current list for it.

Was thinking maybe putting them vertically down the top left side of the table instead. Something like this:

⏫
▲
▼
⏬

Not sure what to do with the text though (eg 25-50 of 7,276 rows) when trying that. Maybe do the arrows on the top left (like above) and put the number text to the left side of the row it's currently on?

[chrisjlocke]

From my own viewpoint, I think horizontal is better, only because it matches that the table/grid/records are horizontal, and it matches DB4S. I'd just either prefer it left aligned, or central but to the screen width, not the virtual table width. I think (guessing, without seeing a mockup) a vertical paging would look clumsy, and be a pain to code.

[chrisjlocke]

Even re-viewing columns which should clearly be in Memcached is extremely slow

Are you logged in or not? If I log in, I can only view 10 records at a time. If I log out, this changes to 25. When viewing 25, there is a 5 to 7 second delay when paging through the records. When viewing 10, this is reduced to 1 second, or 2 tops.

or

I didn't know changing that was possible?

Edit: Aah.

[chrisjlocke]

If I up that to 500 records and view the above database,, DBHub.io 'freezes' - well, its about a minute to page up/down. Scrolling the table across is also a drag (pun intended....) very slow. Well, duh - there are a lot of columns and data, so kinda makes sense.

[justinclift]

Yeah, but I reckon there's something weird happening anyway. It just seems wrong when it's so much slower than (say) the UK postcode database which is 1.7Mill rows. This one's just a few thousand.

I'll take a closer look at it tomorrow after other stuff. I've solved the upload problem in DB4S, at least on Linux (will confirm on Win & OSX shortly), so can take some time looking at it.

[justinclift]

Ohhh, this is interesting in the server logs:

2017/08/17 21:28:29 Table data handler: Error when caching table data for 'AnsenIO/siren': memcache: unexpected response line from "set": "SERVER_ERROR object too large for cache\r\n"

Didn't know that could happen. It's a different database, but something to look into later as well I suppose.

[chrisjlocke]

It's a different database

Its the one you linked to.... the one I've been testing... well, thats and a copy of it... but I was also viewing that one directly.

[chrisjlocke]

than (say) the UK postcode database

I don't think its the number of rows, but the number of columns and the data within those columns. Theres a lot fo data being displayed for each 10 records...

Edit: Actually, not a fat lot ... just looks a lot. 20 KB for 10 records, 80 KB for 25 records.

[justinclift]

Oops, you're right. That is the problem database. Guess I'm mentally fuzzier than I realised. :wink:

Well, at least that explains why data lookups are always seeming to be slow. They're not getting into Memcache.

Investigating that... is not a task for right now for me. "Later" will do. :smile:

[justinclift]

And I couldn't help myself. Looked into it anyway.

Raising the maximum size set for individual memcached objects seems to have done the trick. No errors showing on the console when paging through that table, and it's now "normal" speed when compared to the rest.

Reading through some Memcached docs, it seems like we'll want to compress objects before sending them to the server, but that's something to investigate at a later date. :smile:

[justinclift]

Interestingly, viewing that database just now is really slow again.

Wonder if we have a memory leak somewhere? It's something to investigate later.

[justinclift]

Can I sugggest the paging controls are left aligned?

Now fixed. They're not left aligned, but instead the centering now shouldn't go wrong. Moved them out of the table data structure to an independent line below. Seems ok now. :smile:

[chrisjlocke]

Interestingly, viewing that database just now is really slow again.

Just to throw salt on the fire (??) its going fine for me at the moment. <1 second per page change.

[justinclift]

No worries. :smile:

I'm kind of suspecting that the difference might be something to do with my browser/OS combo.

Using Firefox on Linux it's dog slow. The other day when it was fast for me, it's possibly I was on my OSX desktop, likely using Opera.

I'll check again later. :smile: