Very slow query's after upgrading from 6.2 to 6.4

[Kelarlee]

Hi there, yesterday i updated my zabbix server from 6.2.6 to 6.4.5 and ran into a problem with very slow frontend interface. Also i updated several templates and cleared history for maybe 5000 items so i thought it just making alot of work with database, but after 1 day nothing changed. I'm using Mysql 8.0.33 on the same host as zabbix. I have full of errors in zabbix log like this:

3365:20230803:051856.406 slow query: 145.107991 sec, "delete from history_uint where itemid=85987 limit 40000"
3377:20230803:052121.193 slow query: 3.768664 sec, "insert into history_uint (itemid,clock,ns,value) values (46577,1691040077,243912338,17156591616),(47679,16 91040075,589801143,13561856),(48497,1691040077,261 944325,1661411328),(48737,1691040077,337893249,0), (49577,1691040077,239705036,0),(49757,1691040077,2 77745494,0),(58690,1691040074,151989646,1),(58744, 1691040074,151989646,1),(58920,1691040075,58318981 2,33554432),(58921,1691040075,583189812,25174016), (61331,1691040075,687367411,2528),(61346,169104007 5,687367411,1240),(61347,1691040075,687367411,4312 ),(61348,1691040075,687367411,472),(61349,16910400 75,687367411,0),(61350,1691040075,687367411,0),(61 351,1691040075,687367411,0),(61360,1691040075,6873 67411,1),(61361,1691040075,687367411,1),(61362,169 1040075,687367411,1),(61363,1691040075,687367411,2 ),(61364,1691040075,687367411,2),(61365,1691040075 ,687367411,2),(61366,1691040075,687367411,1),(1001 94,1691040075,583189812,59124),(108123,1691040077, 310053913,0),(108124,1691040077,304878275,0),(1081 25,1691040077,304878275,0),(108126,1691040077,3048 78275,0),(108127,1691040077,310053913,0),(108128,1 691040077,304878275,0),(108129,1691040077,31005391 3,0),(108130,1691040077,304878275,0),(108131,16910 40077,304878275,0),(108132,1691040077,310053913,0) ,(108133,1691040077,310053913,0),(108134,169104007 7,310053913,0),(108135,1691040077,304878275,0),(10 8136,1691040077,304878275,0),(108137,1691040077,31 0053913,0),(108138,1691040077,304878275,0),(108139 ,1691040077,304878275,0),(108140,1691040077,304878 275,0),(108141,1691040077,310053913,0),(108142,169 1040077,310053913,0),(108143,1691040077,310053913, 0),(108144,1691040077,304878275,0),(108145,1691040 077,304878275,0),(108146,1691040077,304878275,0),( 108147,1691040077,304878275,0),(108148,1691040077, 304878275,0),(108149,1691040077,304878275,0),(1081 50,1691040077,304878275,0),(108151,1691040077,3100 53913,0),(108152,1691040077,304878275,0),(108153,1 691040077,310053913,0),(108154,1691040077,31005391 3,0),(108155,1691040077,304878275,0),(108156,16910 40077,304878275,0),(108157,1691040077,304878275,0) ,(108158,1691040077,304878275,0),(108159,169104007 7,304878275,0),(108160,1691040077,304878275,0),(10 8161,1691040077,310053913,0),(108162,1691040077,31 0053913,0),(108163,1691040077,310053913,0),(108164 ,1691040077,310053913,0),(108165,1691040077,304878 275,0),(108166,1691040077,304878275,0),(108167,169 1040077,310053913,0),(108168,1691040077,304878275, 0),(108169,1691040077,310053913,0),(108170,1691040 077,310053913,0),(108171,1691040077,304878275,0),( 108172,1691040077,304878275,0),(108173,1691040077, 310053913,0),(108174,1691040077,304878275,0),(1081 75,1691040077,304878275,0),(108176,1691040077,3100 53913,0),(108177,1691040077,310053913,0),(108178,1 691040077,310053913,0),(108179,1691040077,31005391 3,917912),(108180,1691040077,304878275,13954720),( 108181,1691040077,310053913,0),(108182,1691040077, 310053913,26968),(108183,1691040077,304878275,152) ,(108184,1691040077,304878275,128),(108185,1691040 077,310053913,128),(108186,1691040077,304878275,47 60),(108187,1691040077,304878275,152),(108188,1691 040077,310053913,0),(108189,1691040077,304878275,7 2),(108190,1691040077,304878275,3264),(108191,1691 040077,310053913,3656),(108192,1691040077,30487827 5,12848),(108193,1691040077,310053913,11792),(1081 94,1691040077,310053913,9440),(108195,1691040077,3 04878275,16016),(108196,1691040077,304878275,2416) ,(108197,1691040077,304878275,0),(108198,169104007 7,304878275,3176),(108199,1691040077,304878275,271 2),(108200,1691040077,304878275,2960),(108201,1691 040077,304878275,3280),(108202,1691040077,30487827 5,3024),(108203,1691040077,304878275,2016),(108204 ,1691040077,310053913,0),(108205,1691040077,304878 275,112),(108206,1691040077,304878275,112),(108207 ,1691040077,304878275,0),(108208,1691040077,310053 913,0),(108209,1691040077,304878275,0),(108210,169 1040077,304878275,0),(108211,1691040077,310053913, 0),(108212,1691040077,310053913,0),(108213,1691040 077,304878275,0),(108214,1691040077,310053913,0),( 108215,1691040077,304878275,0),(108216,1691040077, 304878275,0),(108217,1691040077,310053913,0),(1082 18,1691040077,304878275,0),(108219,1691040077,3100 53913,0),(108220,1691040077,304878275,0),(108221,1 691040077,304878275,0),(108222,1691040077,31005391 3,0),(108223,1691040077,310053913,0),(108224,16910 40077,304878275,0),(108225,1691040077,304878275,0) ,(108226,1691040077,304878275,0),(108227,169104007 7,304878275,0),(108228,1691040077,310053913,0),(10 8229,1691040077,310053913,0),(108230,1691040077,30 4878275,0),(108231,1691040077,304878275,0),(108232 ,1691040077,310053913,0),(108233,1691040077,304878 275,0),(108234,1691040077,310053913,0),(108235,169 1040077,310053913,0),(108236,1691040077,310053913, 0),(108237,1691040077,304878275,0),(108238,1691040 077,304878275,0),(108239,1691040077,310053913,0),( 108240,1691040077,310053913,0),(108241,1691040077, 304878275,0),(108242,1691040077,310053913,0),(1082 43,1691040077,304878275,0),(108244,1691040077,3048 78275,0),(108245,1691040077,304878275,0),(108246,1 691040077,304878275,0),(108247,1691040077,30487827 5,0),(108248,1691040077,304878275,0),(108249,16910 40077,310053913,0),(108250,1691040077,304878275,0) ,(108251,1691040077,310053913,0),(108252,169104007 7,304878275,0),(108253,1691040077,304878275,0),(10 8254,1691040077,310053913,0),(108255,1691040077,31 0053913,0),(108256,1691040077,304878275,0),(108257 ,1691040077,304878275,0),(108258,1691040077,304878 275,0),(108259,1691040077,304878275,0),(108260,169 1040077,304878275,0),(108261,1691040077,304878275, 0),(108262,1691040077,310053913,0),(108263,1691040 077,310053913,14098184),(108264,1691040077,3100539 13,712784),(108265,1691040077,304878275,64),(10826 6,1691040077,304878275,39992),(108267,1691040077,3 10053913,360),(108268,1691040077,304878275,456),(1 08269,1691040077,304878275,360),(108270,1691040077 ,310053913,1448),(108271,1691040077,304878275,448) ,(108272,1691040077,310053913,0),(108273,169104007 7,304878275,192),(108274,1691040077,304878275,2152 ),(108275,1691040077,310053913,2008),(108276,16910 40077,304878275,7176),(108277,1691040077,310053913 ,4160),(108278,1691040077,304878275,5352),(108279, 1691040077,310053913,8752),(108280,1691040077,3048 78275,2080),(108281,1691040077,310053913,0),(10828 2,1691040077,304878275,2576),(108283,1691040077,31 0053913,2256),(108284,1691040077,310053913,2440),( 108285,1691040077,310053913,2672),(108286,16910400 77,304878275,2504),(108287,1691040077,310053913,18 08),(108288,1691040077,304878275,24),(108289,16910 40077,310053913,248),(108290,1691040077,304878275, 320),(108319,1691040077,304878275,1),(108320,16910 40077,304878275,1),(108321,1691040077,304878275,1) ,(108322,1691040077,310053913,1),(108323,169104007 7,304878275,1),(108324,1691040077,304878275,1),(10 8325,1691040077,304878275,1),(108326,1691040077,31 0053913,1),(108327,1691040077,304878275,1),(108328 ,1691040077,304878275,1),(108329,1691040077,304878 275,1),(108330,1691040077,304878275,1),(108331,169 1040077,304878275,1),(108332,1691040077,304878275, 1),(108333,1691040077,304878275,1),(108334,1691040 077,304878275,1),(108335,1691040077,304878275,1),( 108336,1691040077,310053913,1),(108337,1691040077, 304878275,2),(108338,1691040077,304878275,1),(1083 39,1691040077,304878275,1),(108340,1691040077,3048 78275,1),(108341,1691040077,310053913,1),(108342,1 691040077,304878275,1),(108343,1691040077,30487827 5,1),(108344,1691040077,304878275,1),(108345,16910 40077,304878275,1),(108346,1691040077,304878275,1) ,(108392,1691040077,304878275,235750),(108396,1691 040077,310053913,2046820352),(108397,1691040077,30 4878275,148746240);

I'm not familiar with databases so i'm not sure wthat was happened after update. I will be grateful for any help, thank you.

Also my system information:

[tim.mooney]

You potentially have a couple of issues, one of them may be sizing or tuning-related but the other issue is the first one you should address.

In your display, the line that says "Database history tables use primary key" with the red "No" means that your database has not been upgraded so that the history* tables (there are multiple) all have a primary key. You should fix that first.

In the release notes for the upgrade to version 6.0, there was a section about adding primary keys to all tables. Read the "Primary keys" section of this document, then follow the link for the mysql/mariadb procedure: https://www.zabbix.com/documentation...rade_notes_600

You have to choose one of those methods, depending upon what's appropriate for your environment. Ask questions (probably in a separate thread) if you need to, but get the history tables fixed up first, so that they have primary keys.

If your install is still having performance issues after fixing that, follow up in this thread. You're likely in a situation where your data has grown to the point where you crossed a threshold and now you need to do some database tuning to get performance back to a level you find acceptable.