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: .1.3.6.1.4.1.9.9.599.0.4′” 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?