VSpike: do the queries that.

Dambrozio: Jkavalik: still, using all the memory and losing disk cache is bad practive

Bisarra: Http://pastebin.com/WuZNB9bc – is there any more I can do to optimize either the query or the table definition?

Nolting: Dambrozio, but they are talking about 50GB of 64 that seems to me like quite reasonable value for buffer pool on mostly-innodb satabase

Vankampen: Http://pastebin.com/mhTeknyU

Weichbrodt: Luckymurali, “created all idnexes” – you mean like one index per column? that is not the right way

Kiesser: Yes, one index per colunm

Jurin: Luckymurali, read https://www.percona.com/blog/2015/04/27/indexing-101-optimizing-mysql-queries-on-a-single-table/

Gottlieb: Luckymurali, mysql can usually use only one index per query and table, so you often need composite multicolumn indexes

Ditch: Yes composite also done based on select statements

Filipek: I think people should state one index per row of the explain

Hurtz: Dagb, do you use mysql or mariadb?

Theuenin: Filipek, will try to remember, but some “recipients” have no idea about explain rows.

Bisarra: Does the answer depend on that? :-

Filipek: Jkavalik, I know the problem :

Palagi: But please tell me what other configuration makes me optimized one

Ruthledge: I have done the paste bin

Griffth: Dagb, mariadb has indexable “virtual columns” which might extract data from the “value” column in some cases, mysql has that only in 5.7 afaik

Bisarra: Jkavalik: that sounds useful here.

Bisarra: Then I could index on the username

Bloom: Luckymurali, if your queries are “bad” not well designed or lacking indexes etc, then no amount of optimization can help you much, better config gives you speedup in few percents range – good index in thousands times often. But your buffer pool is small at 8GB so thats one config to change

Maves: Dagb, that entire schema is unfortunate but you probably heard that already

Bisarra: Jkavalik: yes. :- thanks

Barroso: Dagb, with a persistent virtual/generated column you might be able to create an expression which would “extract” the OID part or NULL if it is not there, and index that or something, but I did not use anything like that yet

Bisarra: Jkavalik: I’ll look into it. The actual query as it stands today looks ok?

Bisarra: Jkavalik: hmm. Primary Keys cannot contain any virtual columns. You cannot create an index on a mix of virtual and non-virtual columns. You cannot create a spatial or fulltext index on virtual columns this limitation will be lifted later

Bisarra: A virtual index cannot be used as a foreign key

Bisarra: This applies to mysql. Haven’t looked at mariadb yet

Wykle: Dagb, one thing comes to my mind, you might want to extend “trap.value = ‘OID: .′” with “and trap.type = ‘oid'” – and give it an index where type is before value so it does not have to match so many values – but not sure it will actually help with real times

Bisarra: Jkavalik: thank you. noted.

Hillan: Dagb, yep, they are far from perfect, but for instance the “do not mix” part can be “simply” ignored by creating one virtual column per real column you want to add to the same index

Bisarra: Had to p**** that a few times, but I think I understand what you mean.

Slowe: Dagb, yes, sorry if I sometimes write nonsense, not a native speaker and I get lost sometimes

Bisarra: Jkavalik: no worries. Not a native english speaker either.

Vankoten: If I want to benchmark my server and try changing various things to see the effect, how worthwhile is it to try to create a benchmarking test that mimics my actual traffic versus using an off-the-shelf benchmark?

Yannucci: VSpike: the only benchmark that matters is the one for your traffic.

Bergholm: Danblack: so how do you go about creating that benchmark?

Bisarra: VSpike: do the queries that matter to you. measure the time required. Tune table/indexes/queries, redo test?