The Heap™ – Consultancy ©®

7:42 AM

Morning

8:12 AM

Morning

@FaheemMitha Yes, that means the PK is supposed to be composite (consisting of more than one column; in that case, consisting of two columns)

Faheem Mitha

@AndriyM Ok. Thank you for the confirmation.

Would it be easy to form queries asking for matches with expressions in terms of the tags - i.e. with ANDs, ORs, and NOTs?

Andriy M

@FaheemMitha That's somewhat unclear. Could you give an example?

As in, what kind of query instead of what other kind of query

Faheem Mitha

@AndriyM Well, a query like: give me all lines of the table matching the expression "'foo' AND 'bar'". Or "'foo' OR 'bar'". Or "NOT 'foo'".

Where 'foo' and 'bar' are tags. And in this scenario, each row of a table would have separate tags. But that's probably the standard layout, anyway.

Andriy M

Ah, I think I understand. When tags are stored in a subordinate table, you can't really use an expression like entity_tags.Tag = 'Foo' AND entity_tags.Tag = 'Bar' to query for an entity that has both those tags. The filter would need to be a little more sophisticated.

This kind of problem is commonly known as relational division. You can look up questions tagged relational-division on the main to see how such problems are handled.

McNets

9:06 AM

Morning

Peter Vandivier

9:20 AM

Morning :)

Faheem Mitha

@AndriyM So you are saying the layout that corresponds to dba.stackexchange.com/a/42489/1319 wouldn't be able to support such queries?

Andriy M

9:33 AM

@FaheemMitha Before saying either yes or no to that, I would need to understand better what you mean by "such queries". Generally speaking, however, this layout simply requires different handling than e.g. storing tags as a single delimited string, or any other approach. Some queries will be easier with this approach than that, while others the other way round. So, the issue is not "able to support vs unable to support" but "easier to handle vs more difficult to handle".

Faheem Mitha

@AndriyM So it's possible do to such queries? Just more complicated, and possibly slower?

Andriy M

@FaheemMitha Yes

Tom V - Team Monica

@FaheemMitha If I understand where you are going your where tag1 = y and tag2 = b would end up something like

where (exists select from tags t where t.parentid = parentquery.id and t.value=y) and (exists select from tags t where t.parentid=parentquery.id and t.value=b

) or something along those lines

Andriy M

@TomV-TeamMonica That's one of the ways of implementing it, yes. You could also use aggregation, and there are actually variations in how you would use aggregation, too.

Faheem Mitha

@TomV-TeamMonica Possibly yes. That doesn't look so bad, actually.

From doing a bit of searching I got the impression that that is probably the most standard layout for tagging. And also the natural approach.

Tom V - Team Monica

9:39 AM

@AndriyM sure, only the possible approaches there would likely depend on the RDBMS

I haven't read back the whole discussion so I'm probably missing stuff

Faheem Mitha

@TomV-TeamMonica There isn't much relevant discussion. I just wanted to know how I could implement tagging in a db. And of course I'd want to use it for queries.

Tom V - Team Monica

I would store them as rows in a seperate table instead of some sort of concatenated string or json column or whatever

Faheem Mitha

@TomV-TeamMonica Yes, isn't that what that answer does too? Namely, dba.stackexchange.com/a/42489/1319

The idea of a concatenated string doesn't really appeal. It seems to be to losing user structural information.

Tom V - Team Monica

In a way yes, you could opt for a defined list of tags and create a many-to-many table between the base table and the tag table, or you could just have a base table link to a tag table (and possibly have duplicates in the tag table). It kind of depends how free-form the end user can add tags to a base entity

If they are selected from a list I would probably opt for the many-to-many construction, if anybody can just type anything I would probably just go for a single table with (baseentityid, tagstring) as columns to avoid having to look up the tag, check if it exists, add it if it doesn't and then store the tagid in the many-to-many table for everything the user types

It depends ™

Faheem Mitha

@TomV-TeamMonica There really isn't a frontend or end users here. It's just for my usage.

And I've saving everything in text files, and recreating on the fly every time I need to use it. The amount of data is quite small. Like a few hundred kilobytes at most.

Andriy M

9:52 AM

Faheem's initial question might actually be answerable in its generic form too, but it definitely looks very broad, as I said from the beginning. To answer it properly, in my estimation, not only would it be necessary to cover the various approaches but also useful to mention various reasons to choose among them. A detailed answer would probably be able to fill a book chapter. A summary might just fit the Q&A format.

Either way, you would need to do a lot of research

Faheem Mitha

@AndriyM People have already asked the same basic question here. Though they did provide more information.

Andriy M

Yes, they provided some context, which made it easier to come up with a specific answer.

Faheem Mitha

@AndriyM Well, I could ask a question. I guess. Do you encourage that in this case?

Tom V - Team Monica

15 mins ago, by Tom V - Team Monica

It depends ™

Andriy M

@FaheemMitha The generic one? Not sure I do. A comprehensive answer to that would take much more time and effort than you would typically expect people to invest answering questions here. It would require the level of dedication comparable to that necessary to write a comprehensive blog article.

Faheem Mitha

10:04 AM

@AndriyM No, not the generic one. My specific situation, which isn't really that special or unusual. Probably very ordinary.

Andriy M

@FaheemMitha But what is you question? Is it still about the design? Because you are already aware of existing answers suggesting at least one approach

Faheem Mitha

@AndriyM Yes, about the design. With the proviso that I want something that makes queries such as I already described, easy.

I'm guessing this isn't new or substantial enough for a question.

Andriy M

@FaheemMitha Hmm, I think, the proviso would make it specific enough, provided the proviso is clear enough.

Tom V - Team Monica

@FaheemMitha The queries are going to be a variation of exists, or join the tag table having count(tag)>0 or whatever, but I don't think any other approach will result in easier queries

Faheem Mitha

@AndriyM @TomV-TeamMonica Ok. I don't think I'll ask a question. I'll experiment and see if I run into any issues.

Andriy M

10:16 AM

And I don't remember this kind of question to be asked with this kind of requirement either. If there have been such questions and someone finds them, yours may be closed as a duplicate, which isn't a bad thing I think.

Faheem Mitha

It doesn't seem like a terribly difficult thing to do, anyway.

Andriy M

@FaheemMitha Always a good approach to solving a problem

John aka hot2use

sqlskills.com/blogs/paul/…

> It’s also a neat way to reset the VLFs in a log if someone created the initial log to be enormous and you want to resize it to be a lot smaller, as there’s no other way of reducing the size of the first couple of VLFs

John aka hot2use

1:15 PM

This user has a lot of similar comments in his answers:

> For additional suggestions view my profile, Network profile for contact information including my Skype ID and get in touch, please.

Andriy M

1:30 PM

Nov 12 '18 at 9:56, by Andriy M

@hot2use @JackDouglas They've actually been doing that a lot

Nov 12 '18 at 10:17, by Andriy M

To this user's credit, their answers look complete enough. Even though they seem to be using this site for self-promotion, at least they seem to be trying to play by the rules otherwise

Tom V - Team Monica

1:52 PM

It annoys me

John aka hot2use

@TomV-TeamMonica +1

Tom V - Team Monica

And are his answers really any good? This for example

1

A: Something in MySQL is causing progressively high load which consumes almost all memory

Suggestions to consider for your my.cnf [mysqld] section REMOVE the following 3 lines that have other values to be used on other lines innodb_file_per_table = 1 max_allowed_packet=100M query_cache_size = 256M CHANGE read_buffer_size=256K # from 256 to improve performance read_rnd_buffer_siz...

why would those settings help? What do they do?

Same here:

0

A: mariadb takes too much load though having sufficient RAM

With your limited information posted, suggestions to consider for your my.cnf [mysqld] section max_connections=512 # from 2000 because max_used_connections only 213 join_buffer_size=256K # from 20M to reduce RAM required per connection read_buffer_size=256K # from 2M to reduce RAM required pe...

(where somebody edited out the spam)

Most of his answers to me seem like the output of the tool he's trying to sell without any explanation and I have no idea what input is used in his tool.

John aka hot2use

@TomV-TeamMonica I was re-reading that one, after reading Andriy's reply.

Not a full solution. More of a: "try this and otherwise get in touch."

Tom V - Team Monica

I'm not a MySQL expert, but if his tool takes only a my.conf as input and makes some suggested changes, I would expect the changes to be documented everywhere and ommon knowledge if they are supposed to help everybody in every situation

John aka hot2use

2:09 PM

1

A: High RAM memory usage

Rate Per Second = RPS - Suggestions to consider for your my.cnf [mysqld] section innodb_io_capacity=1900 # from 200 to use more of your SSD iops capacity thread_cache_size=100 # from 8 to reduce threads_created count of 12,926 in 2 days read_rnd_buffer_size=128K # from 256K to reduce handler_...

> ...We would like to assist via Skype.

1

A: MySQL 5.7 + InnoDB: mysqld is CPU bound, with ever increasing memory usage

Rate Per Second=RPS - Suggestions to consider for your my.cnf [mysqld] section thread_cache_size=64 # from 8 to reduce threads_created (RAM allocations) innodb_log_buffer_size=256M # from 16M for ~ 20 minutes of RAM storage innodb_buffer_pool_size=16G # from ~ 5G for about 1/2 RAM innodb_chan...

> Disclaimer: I am the content author of website mentioned in my profile, Network profile where we offer FREE Utility Scripts, additional analysis services and contact info.

Tom V - Team Monica

Yeah

I don't think he needs to be punished or something. Just edit out the marketing and vote according to what the answer is without it. (which IMO is often poor quality)

Unfortunately the new CoC still doesn't say "Don't annoy Tom V", it should though

4

John aka hot2use

Sometimes very poor.

Tom V - Team Monica

Like this question:

0

A: MySQL table gets slow after big deletion?

Rate Per Second = RPS Suggestions to consider for your my.cnf [mysqld] section read_rnd_buffer_size=256K # from 512K to reduce handler_read_rnd_next RPS of 125 innodb_lru_scan_depth=100 # from 1024 to conserve 90% of CPU cycles used for function each SECOND innodb_flushing_avg_loops=5 # from...

What's the point of his answer after Rick's explanation?

John aka hot2use

Well, I noticed that all the pastebin links in the question have died.

The whole Q & A is no longer relevant (sadly).

revise that: most of the pastebin links have been automatically deleted

Andriy M

2:36 PM

@TomV-TeamMonica "Don't annoy, or irk, or vex, or otherwise upset Tom V" (the full quote as it should be)

A bit of Fry and Laurie. Series 1. "Don't break into my car"

►

John aka hot2use

Listen with headphones:

Virtual Barber Shop (Audio...use headphones, close ur eyes)

►

@TomV-TeamMonica I don't even see how it is relevant to the question. CPU busy isn't even mentioned in the question or I'm missing something.

Same answer in both "MySQL table gets slow after big deletion?" and "High RAM memory usage".

Cut & Paste

Andriy M

2:59 PM

Another theory is, the OPs might actually be their clients and DBA.SE could be used as a support channel, sort of a public Teams (at DBA.SE)

John aka hot2use

3:13 PM

sucks

After following the above suggestions, it consumes more cpu cores than my previous settings — Godwin Alex Ogbonda Oct 31 '18 at 18:14

Transcript for

The Heap™ – Consultancy ©®