Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32663

mariadb filesystem size increasing for wp_options.ibd files

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.6.15, 10.6.16, 10.6.17
    • 10.6
    • None
    • Ubuntu 20.04

    Description

      I'm in a situation with a wordpress site where a table wp_options keeps increasing in filesystem size really fast even though it only has 10000 records and we have to constantly optimize it to reduce the usage.

      It's my understanding that when records are constantly deleted / inserted they are not actually deleted from the ibd file but they're kind of soft-deleted.

      Thus the filesystem size on the file actually increases constantly reaching even 500G within 2 days.

      In order to fix the issue I need to actually see what is being written / deleted from the table constantly to find possible plugins that might be affecting it.

      I can safely assume it's wordpress transients however I can't say with certainty which type of transient is causing this bloat without examining the data.

      So in order to find this out I'm looking for ways to read the soft-deleted data from the bloated ibd file.

      I've tried a couple of tools like undrop-for-innodb but it's not yielding any results.

      Any ideas how I can read the mass of deleted data that's occupying the filesystem space?

      I also think that there should be a limit to the expansion of the table with deleted data. Like, why would a table of 20MBs in size need 500GB of deleted data in the filesystem?

      Attachments

        1. blob.zip
          11 kB
        2. historylenth.zip
          90 kB
        3. historylenth-1.zip
          301 kB
        4. history-list-length.zip
          842 kB
        5. sample.zip
          152 kB

        Issue Links

          Activity

            I've managed to get a good look at the queries running in the DB by getting a huge log of queries in wp_options table.
            It seems the bulk is queries like in this file sample.zip or queries like this

            starting UPDATE `wp_options` SET `option_value` = 'a:3:{i:0;a:36:{s:12:\\"channel_hash\\";s:32:\\"d4e5a1ae056299a65ce0d2b72b83ac00\\";s:4:
            \\"name\\";s:7:\\"Skroutz\\";s:6:\\"fields\\";s:7:\\"skroutz\\";s:8:\\"taxonomy\\";s:4:\\"none\\";s:10:\\"utm_source\\";s:9:\\"Bestprice\\";s:4:\\"type\\";s:26:\\"Comparison shopping engine\\";s:11:\\"projectn
            ame\\";s:9:\\"Bestprice\\";s:9:\\"countries\\";s:6:\\"Greece\\";s:10:\\"fileformat\\";s:3:\\"xml\\";s:4:\\"cron\\";s:6:\\"hourly\\";s:4:\\"step\\";s:3:\\"100\\";s:12:\\"project_hash\\";s:32:\\"hFlvg33s2QXLeYvd
            1ozmtfHQvqMZK2DO\\";s:8:\\"filename\\";s:32:\\"hFlvg33s2QXLeYvd1ozmtfHQvqMZK2DO\\";s:13:\\"external_file\\";s:105:\\"https://www.mydb.gr/wp-content/uploads/woo-product-feed-pro/xml/hFlvg33s2QXLeYvd1ozmtfHQvqMZ
            K2DO.xml\\";s:9:\\"query_log\\";s:79:\\"https://www.mydb.gr/wp-content/uploads/woo-product-feed-pro/logs/query.log\\";s:16:\\"query_output_log\\";s:86:\\"https://www.mydb.gr/wp-content/uploads/woo-product-feed
            -pro/logs/query_output.log\\";s:10:\\"attributes\\";a:14:{i:0;a:5:{s:8:\\"rowCount\\";s:1:\\"0\\";s:9:\\"attribute\\";s:2:\\"id\\";s:6:\\"prefix\\";s:0:\\"\\";s:7:\\"mapfrom\\";s:2:\\"id\\";s:6:\\"suffix\\";s:
            0:\\"\\";}i:1;a:5:{s:8:\\"rowCount\\";s:1:\\"1\\";s:9:\\"attribute\\";s:4:\\"name\\";s:6:\\"prefix\\";s:0:\\"\\";s:7:\\"mapfrom\\";s:5:\\"title\\";s:6:\\"suffix\\";s:0:\\"\\";}i:2;a:5:{s:8:\\"rowCount\\";s:1:\
            \"2\\";s:9:\\"attribute\\";s:4:\\"link\\";s:6:\\"prefix\\";s:0:\\"\\";s:7:\\"mapfrom\\";s:4:\\"link\\";s:6:\\"suffix\\";s:0:\\"\\";}i:3;a:5:{s:8:\\"rowCount\\";s:1:\\"3\\";s:9:\\"attribute\\";s:5:\\"image\\";s
            :6:\\"prefix\\";s:0:\\"\\";s:7:\\"mapfrom\\";s:5:\\"image\\";s:6:\\"suffix\\";s:0:\\"\\";}i:4;a:5:{s:8:\\"rowCount\\";s:1:\\"4\\";s:9:\\"attribute\\";s:8:\\"category\\";s:6:\\"prefix\\";s:0:\\"\\";s:7:\\"mapfr
            om\\";s:21:\\"category_path_skroutz\\";s:6:\\"suffix\\";s:0:\\"\\";}i:5;a:5:{s:8:\\"rowCount\\";s:1:\\"5\\";s:9:\\"attribute\\";s:14:\\"price_with_vat\\";s:6:\\"prefix\\";s:0:\\"\\";s:7:\\"mapfrom\\";s:5:\\"pr
            ice\\";s:6:\\"suffix\\";s:0:\\"\\";}i:6;a:5:{s:8:\\"rowCount\\";s:1:\\"6\\";s:9:\\"attribute\\";s:12:\\"manufacturer\\";s:6:\\"prefix\\";s:0:\\"\\";s:7:\\"mapfrom\\";s:8:\\"pa_brand\\";s:6:\\"suffix\\";s:0:\\"
            \\";}i:7;a:5:{s:8:\\"rowCount\\";s:1:\\"7\\";s:9:\\"attribute\\";s:3:\\"mpn\\";s:6:\\"prefix\\";s:0:\\"\\";s:7:\\"mapfrom\\";s:3:\\"sku\\";s:6:\\"suffix\\";s:0:\\"\\";}i:9;a:5:{s:8:\\"rowCount\\";s:1:\\"9\\";s
            :9:\\"attribute\\";s:4:\\"size\\";s:6:\\"prefix\\";s:0:\\"\\";s:7:\\"mapfrom\\";s:7:\\"pa_size\\";s:6:\\"suffix\\";s:0:\\"\\";}i:10;a:5:{s:8:\\"rowCount\\";s:2:\\"10\\";s:9:\\"attribute\\";s:5:\\"color\\";s:6:
            \\"prefix\\";s:0:\\"\\";s:7:\\"mapfrom\\";s:8:\\"pa_color\\";s:6:\\"suffix\\";s:0:\\"\\";}i:11;a:5:{s:8:\\"rowCount\\";s:2:\\"11\\";s:9:\\"attribute\\";s:15:\\"additionalimage\\";s:6:\\"prefix\\";s:0:\\"\\";s:
            7:\\"mapfrom\\";s:7:\\"image_1\\";s:6:\\"suffix\\";s:0:\\"\\";}i:12;a:5:{s:8:\\"rowCount\\";s:2:\\"12\\";s:9:\\"attribute\\";s:15:\\"additionalimage\\";s:6:\\"prefix\\";s:0:\\"\\";s:7:\\"mapfrom\\";s:7:\\"imag
            e_2\\";s:6:\\"suffix\\";s:0:\\"\\";}i:13;a:5:{s:8:\\"rowCount\\";s:2:\\"13\\";s:9:\\"attribute\\";s:15:\\"additionalimage\\";s:6:\\"prefix\\";s:0:\\"\\";s:7:\\"mapfrom\\";s:7:\\"image_3\\";s:6:\\"suffix\\";s:0
            :\\"\\";}i:15;a:5:{s:8:\\"rowCount\\";s:2:\\"15\\";s:9:\\"attribute\\";s:12:\\"availability\\";s:6:\\"prefix\\";s:0:\\"\\";s:7:\\"mapfrom\\";s:15:\\"pa_availability\\";s:6:\\"suffix\\";s:0:\\"\\";}}s:6:\\"addr
            ow\\";s:1:\\"4\\";s:6:\\"utm_on\\";s:2:\\"on\\";s:10:\\"utm_medium\\";s:3:\\"cpc\\";s:12:\\"utm_campaign\\";s:9:\\"Bestprice\\";s:8:\\"utm_term\\";s:2:\\"id\\";s:11:\\"utm_content\\";s:0:\\"\\";s:11:\\"woosea_
            page\\";s:13:\\"filters_rules\\";s:6:\\"active\\";b:1;s:12:\\"last_updated\\";s:17:\\"27 Feb 2024 03:03\\";s:7:\\"running\\";s:10:\\"processing\\";s:11:\\"nr_products\\";s:4:\\"2890\\";s:21:\\"nr_products_proc
            essed\\";d:723;s:16:\\"history_products\\";a:20939:{s:17:\\"18 Oct 2019 09:04\\";i:662;s:17:\\"18 Oct 2019 09:18\\";i:662;s:17:\\"18 Oct 2019 10:05\\";i:662;s:17:\\"18 Oct 2019 11:05\\";i:662;s:17:\\"18 Oct 20
            19 12:11\\";i:662;s:17:\\"18 Oct 2019 14:05\\";i:662;s:17:\\"18 Oct 2019 14:50\\";i:662;s:17:\\"18 Oct 2019 15:09\\";i:662;s:17:\\"18 Oct 2019 16:28\\";i:660;s:17:\\"18 Oct 2019 17:53\\";i:660;s:17:\\"18 Oct 2
            019 18:08\\";i:660;s:17:\\"18 Oct 2019 19:10\\";i:660;s:17:\\"18 Oct 2019 20:08\\";i:660;s:17:\\"18 Oct 2019 21:05\\";i:660;s:17:\\"18 Oct 2019 22:02\\";i:660;s:17:\\"18 Oct 2019 22:59\\";i:660;s:17:\\"19 Oct
            2019 00:05\\";i:660;s:17:\\"19 Oct 2019 01:05\\";i:660;s:17:\\"19 Oct 2019 02:02\\";i:660;s:17:\\"19 Oct 2019 03:05\\";i:660;s:17:\\"19 Oct 2019 04:05\\";i:660;s:17:\\"19 Oct 2019 05:03\\";i:660;s:17:\\"19 Oct
             2019 07:12\\";i:660;s:17:\\"19 Oct 2019 08:33\\";i:660;s:17:\\"19 Oct 2019 09:19\\";i:660;s:17:\\"19 Oct 2019 09:59\\";i:660;s:17:\\"19 Oct 2019 11:03\\";i:660;s:17:\\"19 Oct 2019 12:08\\";i:660;s:17:\\"19 Oc
            t 2019 13:10\\";i:660;s:17:\\"19 Oct 2019 14:05\\";i:660;s:17:\\"19 Oct 2019 15:00\\";i:660;s:17:\\"19 Oct 2019 18:05\\";i:660;s:17:\\"19 Oct 2019 19:18\\";i:660;s:17:\\"19 Oct 2019 20:52\\";i:660;s:17:\\"19 O
            ct 2019 21:07\\";i:660;s:17:\\"19 Oct 2019 23:05\\";i:660;s:17:\\"19 Oct 2019 23:37\\";i:660;s:17:\\"20 Oct 2019 00:16\\";i:660;s:17:\\"20 Oct 2019 02:00\\";i:660;s:17:\\"20 Oct 2019 02:17\\";i:660;s:17:\\"20
            Oct 2019 03:36\\";i:660;s:17:\\"20 Oct 2019 04:34\\";i:660;s:17:\\"20 Oct 2019 05:10\\";i:660;s:17:\\"20 Oct 2019 06:07\\";i:660;s:17:\\"20 Oct 2019 07:07\\";i:660;s:17:\\"20 Oct 2019 09:40\\";i:660;s:17:\\"20
             Oct 2019 10:19\\";i:660;s:17:\\"20 Oct 2019 11:19\\";i:660;s:17:\\"20 Oct 2019 12:30\\";i:660;s:17:\\"20 Oct 2019 13:09\\";i:660;........
            

            Which are very large.

            rw-rr- 1 root root 128G Oct 29 20:58 wp_options_query.log
            rw-rr- 1 root root 23G Feb 29 2024 wp_options_query_real.log
            rw-rr- 1 root root 14M Oct 29 20:56 wp_options_query_real_notransient.log

            Out of the full option log of 128G of queries,
            23G were actually writing on the wp_options table
            Out of which 14M only where not of the same type as sample.zip

            Out of the 23G file I found these types of queries

            18 fornext
            6 forupdate
            5 itemsUPDATE
            1 permissionsUPDATE
            60 tablesUPDATE
            4 tonet
            3295 UPDATE`wp_options`

            So I can deduce that consecutively running queries like this reproduces the growth issue

            ioweb.gr Gabriel Tziotzis added a comment - I've managed to get a good look at the queries running in the DB by getting a huge log of queries in wp_options table. It seems the bulk is queries like in this file sample.zip or queries like this starting UPDATE `wp_options` SET `option_value` = 'a: 3 :{i: 0 ;a: 36 :{s: 12 :\\ "channel_hash\\" ;s: 32 :\\ "d4e5a1ae056299a65ce0d2b72b83ac00\\" ;s: 4 : \\ "name\\" ;s: 7 :\\ "Skroutz\\" ;s: 6 :\\ "fields\\" ;s: 7 :\\ "skroutz\\" ;s: 8 :\\ "taxonomy\\" ;s: 4 :\\ "none\\" ;s: 10 :\\ "utm_source\\" ;s: 9 :\\ "Bestprice\\" ;s: 4 :\\ "type\\" ;s: 26 :\\ "Comparison shopping engine\\" ;s: 11 :\\"projectn ame\\ ";s:9:\\" Bestprice\\ ";s:9:\\" countries\\ ";s:6:\\" Greece\\ ";s:10:\\" fileformat\\ ";s:3:\\" xml\\ ";s:4:\\" cron\\ ";s:6:\\" hourly\\ ";s:4:\\" step\\ ";s:3:\\" 100 \\ ";s:12:\\" project_hash\\ ";s:32:\\" hFlvg33s2QXLeYvd 1ozmtfHQvqMZK2DO\\ ";s:8:\\" filename\\ ";s:32:\\" hFlvg33s2QXLeYvd1ozmtfHQvqMZK2DO\\ ";s:13:\\" external_file\\ ";s:105:\\" https: //www.mydb.gr/wp-content/uploads/woo-product-feed-pro/xml/hFlvg33s2QXLeYvd1ozmtfHQvqMZ K2DO.xml\\ ";s:9:\\" query_log\\ ";s:79:\\" https: //www.mydb.gr/wp-content/uploads/woo-product-feed-pro/logs/query.log\\";s:16:\\"query_output_log\\";s:86:\\"https://www.mydb.gr/wp-content/uploads/woo-product-feed -pro/logs/query_output.log\\ ";s:10:\\" attributes\\ ";a:14:{i:0;a:5:{s:8:\\" rowCount\\ ";s:1:\\" 0 \\ ";s:9:\\" attribute\\ ";s:2:\\" id\\ ";s:6:\\" prefix\\ ";s:0:\\" \\ ";s:7:\\" mapfrom\\ ";s:2:\\" id\\ ";s:6:\\" suffix\\";s: 0 :\\ "\\" ;}i: 1 ;a: 5 :{s: 8 :\\ "rowCount\\" ;s: 1 :\\ "1\\" ;s: 9 :\\ "attribute\\" ;s: 4 :\\ "name\\" ;s: 6 :\\ "prefix\\" ;s: 0 :\\ "\\" ;s: 7 :\\ "mapfrom\\" ;s: 5 :\\ "title\\" ;s: 6 :\\ "suffix\\" ;s: 0 :\\ "\\" ;}i: 2 ;a: 5 :{s: 8 :\\ "rowCount\\" ;s: 1 :\ \ "2\\" ;s: 9 :\\ "attribute\\" ;s: 4 :\\ "link\\" ;s: 6 :\\ "prefix\\" ;s: 0 :\\ "\\" ;s: 7 :\\ "mapfrom\\" ;s: 4 :\\ "link\\" ;s: 6 :\\ "suffix\\" ;s: 0 :\\ "\\" ;}i: 3 ;a: 5 :{s: 8 :\\ "rowCount\\" ;s: 1 :\\ "3\\" ;s: 9 :\\ "attribute\\" ;s: 5 :\\ "image\\" ;s : 6 :\\ "prefix\\" ;s: 0 :\\ "\\" ;s: 7 :\\ "mapfrom\\" ;s: 5 :\\ "image\\" ;s: 6 :\\ "suffix\\" ;s: 0 :\\ "\\" ;}i: 4 ;a: 5 :{s: 8 :\\ "rowCount\\" ;s: 1 :\\ "4\\" ;s: 9 :\\ "attribute\\" ;s: 8 :\\ "category\\" ;s: 6 :\\ "prefix\\" ;s: 0 :\\ "\\" ;s: 7 :\\"mapfr om\\ ";s:21:\\" category_path_skroutz\\ ";s:6:\\" suffix\\ ";s:0:\\" \\ ";}i:5;a:5:{s:8:\\" rowCount\\ ";s:1:\\" 5 \\ ";s:9:\\" attribute\\ ";s:14:\\" price_with_vat\\ ";s:6:\\" prefix\\ ";s:0:\\" \\ ";s:7:\\" mapfrom\\ ";s:5:\\" pr ice\\ ";s:6:\\" suffix\\ ";s:0:\\" \\ ";}i:6;a:5:{s:8:\\" rowCount\\ ";s:1:\\" 6 \\ ";s:9:\\" attribute\\ ";s:12:\\" manufacturer\\ ";s:6:\\" prefix\\ ";s:0:\\" \\ ";s:7:\\" mapfrom\\ ";s:8:\\" pa_brand\\ ";s:6:\\" suffix\\ ";s:0:\\" \\ ";}i:7;a:5:{s:8:\\" rowCount\\ ";s:1:\\" 7 \\ ";s:9:\\" attribute\\ ";s:3:\\" mpn\\ ";s:6:\\" prefix\\ ";s:0:\\" \\ ";s:7:\\" mapfrom\\ ";s:3:\\" sku\\ ";s:6:\\" suffix\\ ";s:0:\\" \\ ";}i:9;a:5:{s:8:\\" rowCount\\ ";s:1:\\" 9 \\";s : 9 :\\ "attribute\\" ;s: 4 :\\ "size\\" ;s: 6 :\\ "prefix\\" ;s: 0 :\\ "\\" ;s: 7 :\\ "mapfrom\\" ;s: 7 :\\ "pa_size\\" ;s: 6 :\\ "suffix\\" ;s: 0 :\\ "\\" ;}i: 10 ;a: 5 :{s: 8 :\\ "rowCount\\" ;s: 2 :\\ "10\\" ;s: 9 :\\ "attribute\\" ;s: 5 :\\ "color\\" ;s: 6 : \\ "prefix\\" ;s: 0 :\\ "\\" ;s: 7 :\\ "mapfrom\\" ;s: 8 :\\ "pa_color\\" ;s: 6 :\\ "suffix\\" ;s: 0 :\\ "\\" ;}i: 11 ;a: 5 :{s: 8 :\\ "rowCount\\" ;s: 2 :\\ "11\\" ;s: 9 :\\ "attribute\\" ;s: 15 :\\ "additionalimage\\" ;s: 6 :\\ "prefix\\" ;s: 0 :\\ "\\" ;s: 7 :\\ "mapfrom\\" ;s: 7 :\\ "image_1\\" ;s: 6 :\\ "suffix\\" ;s: 0 :\\ "\\" ;}i: 12 ;a: 5 :{s: 8 :\\ "rowCount\\" ;s: 2 :\\ "12\\" ;s: 9 :\\ "attribute\\" ;s: 15 :\\ "additionalimage\\" ;s: 6 :\\ "prefix\\" ;s: 0 :\\ "\\" ;s: 7 :\\ "mapfrom\\" ;s: 7 :\\"imag e_2\\ ";s:6:\\" suffix\\ ";s:0:\\" \\ ";}i:13;a:5:{s:8:\\" rowCount\\ ";s:2:\\" 13 \\ ";s:9:\\" attribute\\ ";s:15:\\" additionalimage\\ ";s:6:\\" prefix\\ ";s:0:\\" \\ ";s:7:\\" mapfrom\\ ";s:7:\\" image_3\\ ";s:6:\\" suffix\\";s: 0 :\\ "\\" ;}i: 15 ;a: 5 :{s: 8 :\\ "rowCount\\" ;s: 2 :\\ "15\\" ;s: 9 :\\ "attribute\\" ;s: 12 :\\ "availability\\" ;s: 6 :\\ "prefix\\" ;s: 0 :\\ "\\" ;s: 7 :\\ "mapfrom\\" ;s: 15 :\\ "pa_availability\\" ;s: 6 :\\ "suffix\\" ;s: 0 :\\ "\\" ;}}s: 6 :\\"addr ow\\ ";s:1:\\" 4 \\ ";s:6:\\" utm_on\\ ";s:2:\\" on\\ ";s:10:\\" utm_medium\\ ";s:3:\\" cpc\\ ";s:12:\\" utm_campaign\\ ";s:9:\\" Bestprice\\ ";s:8:\\" utm_term\\ ";s:2:\\" id\\ ";s:11:\\" utm_content\\ ";s:0:\\" \\ ";s:11:\\" woosea_ page\\ ";s:13:\\" filters_rules\\ ";s:6:\\" active\\ ";b:1;s:12:\\" last_updated\\ ";s:17:\\" 27 Feb 2024 03 : 03 \\ ";s:7:\\" running\\ ";s:10:\\" processing\\ ";s:11:\\" nr_products\\ ";s:4:\\" 2890 \\ ";s:21:\\" nr_products_proc essed\\ ";d:723;s:16:\\" history_products\\ ";a:20939:{s:17:\\" 18 Oct 2019 09 : 04 \\ ";i:662;s:17:\\" 18 Oct 2019 09 : 18 \\ ";i:662;s:17:\\" 18 Oct 2019 10 : 05 \\ ";i:662;s:17:\\" 18 Oct 2019 11 : 05 \\ ";i:662;s:17:\\" 18 Oct 20 19 12 : 11 \\ ";i:662;s:17:\\" 18 Oct 2019 14 : 05 \\ ";i:662;s:17:\\" 18 Oct 2019 14 : 50 \\ ";i:662;s:17:\\" 18 Oct 2019 15 : 09 \\ ";i:662;s:17:\\" 18 Oct 2019 16 : 28 \\ ";i:660;s:17:\\" 18 Oct 2019 17 : 53 \\ ";i:660;s:17:\\" 18 Oct 2 019 18 : 08 \\ ";i:660;s:17:\\" 18 Oct 2019 19 : 10 \\ ";i:660;s:17:\\" 18 Oct 2019 20 : 08 \\ ";i:660;s:17:\\" 18 Oct 2019 21 : 05 \\ ";i:660;s:17:\\" 18 Oct 2019 22 : 02 \\ ";i:660;s:17:\\" 18 Oct 2019 22 : 59 \\ ";i:660;s:17:\\" 19 Oct 2019 00 : 05 \\ ";i:660;s:17:\\" 19 Oct 2019 01 : 05 \\ ";i:660;s:17:\\" 19 Oct 2019 02 : 02 \\ ";i:660;s:17:\\" 19 Oct 2019 03 : 05 \\ ";i:660;s:17:\\" 19 Oct 2019 04 : 05 \\ ";i:660;s:17:\\" 19 Oct 2019 05 : 03 \\ ";i:660;s:17:\\" 19 Oct 2019 07 : 12 \\ ";i:660;s:17:\\" 19 Oct 2019 08 : 33 \\ ";i:660;s:17:\\" 19 Oct 2019 09 : 19 \\ ";i:660;s:17:\\" 19 Oct 2019 09 : 59 \\ ";i:660;s:17:\\" 19 Oct 2019 11 : 03 \\ ";i:660;s:17:\\" 19 Oct 2019 12 : 08 \\ ";i:660;s:17:\\" 19 Oc t 2019 13 : 10 \\ ";i:660;s:17:\\" 19 Oct 2019 14 : 05 \\ ";i:660;s:17:\\" 19 Oct 2019 15 : 00 \\ ";i:660;s:17:\\" 19 Oct 2019 18 : 05 \\ ";i:660;s:17:\\" 19 Oct 2019 19 : 18 \\ ";i:660;s:17:\\" 19 Oct 2019 20 : 52 \\ ";i:660;s:17:\\" 19 O ct 2019 21 : 07 \\ ";i:660;s:17:\\" 19 Oct 2019 23 : 05 \\ ";i:660;s:17:\\" 19 Oct 2019 23 : 37 \\ ";i:660;s:17:\\" 20 Oct 2019 00 : 16 \\ ";i:660;s:17:\\" 20 Oct 2019 02 : 00 \\ ";i:660;s:17:\\" 20 Oct 2019 02 : 17 \\ ";i:660;s:17:\\" 20 Oct 2019 03 : 36 \\ ";i:660;s:17:\\" 20 Oct 2019 04 : 34 \\ ";i:660;s:17:\\" 20 Oct 2019 05 : 10 \\ ";i:660;s:17:\\" 20 Oct 2019 06 : 07 \\ ";i:660;s:17:\\" 20 Oct 2019 07 : 07 \\ ";i:660;s:17:\\" 20 Oct 2019 09 : 40 \\ ";i:660;s:17:\\" 20 Oct 2019 10 : 19 \\ ";i:660;s:17:\\" 20 Oct 2019 11 : 19 \\ ";i:660;s:17:\\" 20 Oct 2019 12 : 30 \\ ";i:660;s:17:\\" 20 Oct 2019 13 : 09 \\";i: 660 ;........ Which are very large. rw-r r - 1 root root 128G Oct 29 20:58 wp_options_query.log rw-r r - 1 root root 23G Feb 29 2024 wp_options_query_real.log rw-r r - 1 root root 14M Oct 29 20:56 wp_options_query_real_notransient.log Out of the full option log of 128G of queries, 23G were actually writing on the wp_options table Out of which 14M only where not of the same type as sample.zip Out of the 23G file I found these types of queries 18 fornext 6 forupdate 5 itemsUPDATE 1 permissionsUPDATE 60 tablesUPDATE 4 tonet 3295 UPDATE`wp_options` So I can deduce that consecutively running queries like this reproduces the growth issue

            ioweb.gr, thank you for the update. As far as I understand, the column wp_options.option_value is basically a key/value store, a database within database. This could be a good design when the contents is relatively small or updated infrequently.

            InnoDB will primarily try to store an entire record in a B-tree leaf page. If this is not possible, then some of the longest columns will be stored elsewhere in the tablespace, in a singly-linked list of BLOB pages. Updating a BLOB always involves making a copy. The old copy would be deleted when the history of the committed UPDATE transaction is purged.

            For what it is worth, in the most recent quarterly releases (MariaDB Server 10.6.20, 10.11.10 and so on), MDEV-34515 was one more improvement to the performance of the purge subsystem. It might allow the old values of the UPDATE to be freed soon enough so that the wp_options.ibd file would have fewer chances to grow in size.

            In any case, it would be great if you could provide a self-contained SQL test case for reproducing the anomaly so that we can investigate if anything could be improved further. You may anonymize some data. In particular, if the option_value column is not indexed, then only the length of the column should matter, and we might just execute statements like UPDATE wp_options SET option_value=REPEAT('x',12345) WHERE ….

            marko Marko Mäkelä added a comment - ioweb.gr , thank you for the update. As far as I understand, the column wp_options.option_value is basically a key/value store, a database within database. This could be a good design when the contents is relatively small or updated infrequently. InnoDB will primarily try to store an entire record in a B-tree leaf page. If this is not possible, then some of the longest columns will be stored elsewhere in the tablespace, in a singly-linked list of BLOB pages. Updating a BLOB always involves making a copy. The old copy would be deleted when the history of the committed UPDATE transaction is purged. For what it is worth, in the most recent quarterly releases (MariaDB Server 10.6.20, 10.11.10 and so on), MDEV-34515 was one more improvement to the performance of the purge subsystem. It might allow the old values of the UPDATE to be freed soon enough so that the wp_options.ibd file would have fewer chances to grow in size. In any case, it would be great if you could provide a self-contained SQL test case for reproducing the anomaly so that we can investigate if anything could be improved further. You may anonymize some data. In particular, if the option_value column is not indexed, then only the length of the column should matter, and we might just execute statements like UPDATE wp_options SET option_value=REPEAT('x',12345) WHERE … .
            ioweb.gr Gabriel Tziotzis added a comment - - edited

            Just an update @Marko Mäkelä I still haven't given up on a way to replicate this consistently but the nature of the queries is weird in the system itself. I'm not even sure of the routine that generates them and looking into that. Perhaps checking the php code side that generates them will hint at a good way to consistently replicate the issue as well.

            As far as the wp_options table, indeed wordpress is using this table for a key/value storage type but most likely it wasn't intended to be storing blobs like that and Woocommerce is abusing this table not thinking about possible consequences.

            But still the space at some point should be reclaimed fully while it isn't.

            Also in my current mariadb version the history list length keeps growing substantially

            mariadb Ver 15.1 Distrib 10.6.20-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

            The file can be downloaded here it's over 10MB so I can't upload it via the system
            https://plik.yourstaging.eu/file/g3gVhth1WMVtrna0/EYQdUPCpO7Ya1KZW/listlength.zip

            ioweb.gr Gabriel Tziotzis added a comment - - edited Just an update @Marko Mäkelä I still haven't given up on a way to replicate this consistently but the nature of the queries is weird in the system itself. I'm not even sure of the routine that generates them and looking into that. Perhaps checking the php code side that generates them will hint at a good way to consistently replicate the issue as well. As far as the wp_options table, indeed wordpress is using this table for a key/value storage type but most likely it wasn't intended to be storing blobs like that and Woocommerce is abusing this table not thinking about possible consequences. But still the space at some point should be reclaimed fully while it isn't. Also in my current mariadb version the history list length keeps growing substantially mariadb Ver 15.1 Distrib 10.6.20-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 The file can be downloaded here it's over 10MB so I can't upload it via the system https://plik.yourstaging.eu/file/g3gVhth1WMVtrna0/EYQdUPCpO7Ya1KZW/listlength.zip

            ioweb.gr, I think that we could try to repeat this issue by running a workload that updates long BLOBs. I suppose that the WHERE condition is on the PRIMARY KEY. Can you post the result of the following?

            SELECT MIN(LENGTH(option_value)),MAX(LENGTH(option_value)),AVG(LENGTH(option_value)) FROM wp_options;
            

            This would give us some idea of what kind of workload to run.

            marko Marko Mäkelä added a comment - ioweb.gr , I think that we could try to repeat this issue by running a workload that updates long BLOBs. I suppose that the WHERE condition is on the PRIMARY KEY . Can you post the result of the following? SELECT MIN (LENGTH(option_value)), MAX (LENGTH(option_value)), AVG (LENGTH(option_value)) FROM wp_options; This would give us some idea of what kind of workload to run.

            Hi, of course here it is

             SELECT MIN(LENGTH(option_value)),MAX(LENGTH(option_value)),AVG(LENGTH(option_value)) FROM wp_options;
            +---------------------------+---------------------------+---------------------------+
            | MIN(LENGTH(option_value)) | MAX(LENGTH(option_value)) | AVG(LENGTH(option_value)) |
            +---------------------------+---------------------------+---------------------------+
            |                         0 |                    758101 |                  999.0166 |
            +---------------------------+---------------------------+---------------------------+
            1 row in set (0.019 sec)
            
            

            ioweb.gr Gabriel Tziotzis added a comment - Hi, of course here it is SELECT MIN(LENGTH(option_value)),MAX(LENGTH(option_value)),AVG(LENGTH(option_value)) FROM wp_options; +---------------------------+---------------------------+---------------------------+ | MIN(LENGTH(option_value)) | MAX(LENGTH(option_value)) | AVG(LENGTH(option_value)) | +---------------------------+---------------------------+---------------------------+ | 0 | 758101 | 999.0166 | +---------------------------+---------------------------+---------------------------+ 1 row in set ( 0.019 sec)

            People

              marko Marko Mäkelä
              ioweb.gr Gabriel Tziotzis
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.