Details

      Description

      This DB has about 30GB and a lot of tables in many databases:

      [mysql]# find ./ -name "*.ibd" | wc -l
      90219
      

      Start takes more than 5 minutes with heavy IO load made by mysql process:

      2017-09-22 10:07:27 140632756656096 [Note] InnoDB: Highest supported file format is Barracuda.
      2017-09-22 10:13:05 140632756656096 [Note] InnoDB: 128 out of 128 rollback segments are active.
      

      During the startup, strace shows this:

      cat /root/strace | grep open
       
      [pid 29937] open("./deploy_test_b2c15/data_sources.ibd", O_RDONLY) = 1363
      [pid 29937] open("./deploy_test_b2c15/data_sources.ibd", O_RDWR) = 1363
      [pid 29937] open("./deploy_test_b2c15/data_sources_backup.isl", O_RDWR) = -1 ENOENT (No such file or directory)
      [pid 29937] open("./deploy_test_b2c15/data_sources_backup.ibd", O_RDONLY) = 4108
      [pid 29937] open("./deploy_test_b2c15/data_sources_backup.ibd", O_RDONLY) = 1364
      [pid 29937] open("./deploy_test_b2c15/data_sources_backup.ibd", O_RDWR) = 1364
      [pid 29937] open("./deploy_test_b2c15/data_sources_brands.isl", O_RDWR) = -1 ENOENT (No such file or directory)
      [pid 29937] open("./deploy_test_b2c15/data_sources_brands.ibd", O_RDONLY) = 4108
      [pid 29937] open("./deploy_test_b2c15/data_sources_brands.ibd", O_RDONLY) = 1365
      [pid 29937] open("./deploy_test_b2c15/data_sources_brands.ibd", O_RDWR) = 1365
      [pid 29937] open("./deploy_test_b2c15/data_sources_categories.isl", O_RDWR) = -1 ENOENT (No such file or directory)
      [pid 29937] open("./deploy_test_b2c15/data_sources_categories.ibd", O_RDONLY) = 4108
      [pid 29937] open("./deploy_test_b2c15/data_sources_categories.ibd", O_RDONLY) = 1366
      [pid 29937] open("./deploy_test_b2c15/data_sources_categories.ibd", O_RDWR) = 1366
      [pid 29937] open("./deploy_test_b2c15/data_sources_categories_brands.isl", O_RDWR) = -1 ENOENT (No such file or directory)
      [pid 29937] open("./deploy_test_b2c15/data_sources_categories_brands.ibd", O_RDONLY) = 4108
      [pid 29937] open("./deploy_test_b2c15/data_sources_categories_brands.ibd", O_RDONLY) = 1367
      [pid 29937] open("./deploy_test_b2c15/data_sources_categories_brands.ibd", O_RDWR) = 1367
      [pid 29937] open("./deploy_test_b2c15/data_sources_img_queue.isl", O_RDWR) = -1 ENOENT (No such file or directory)
      [pid 29937] open("./deploy_test_b2c15/data_sources_img_queue.ibd", O_RDONLY) = 4108
      [pid 29937] open("./deploy_test_b2c15/data_sources_img_queue.ibd", O_RDONLY) = 1368
      [pid 29937] open("./deploy_test_b2c15/data_sources_img_queue.ibd", O_RDWR) = 1368
      [pid 29937] open("./deploy_test_b2c15/data_sources_methods.isl", O_RDWR) = -1 ENOENT (No such file or directory)
      [pid 29937] open("./deploy_test_b2c15/data_sources_methods.ibd", O_RDONLY) = 4108
      [pid 29937] open("./deploy_test_b2c15/data_sources_methods.ibd", O_RDONLY) = 1369
      [pid 29937] open("./deploy_test_b2c15/data_sources_methods.ibd", O_RDWR) = 1369
      [pid 29937] open("./deploy_test_b2c15/default_delivery_address.isl", O_RDWR) = -1 ENOENT (No such file or directory)
      [pid 29937] open("./deploy_test_b2c15/default_delivery_address.ibd", O_RDONLY) = 4108
      [pid 29937] open("./deploy_test_b2c15/default_delivery_address.ibd", O_RDONLY) = 1370
      [pid 29937] open("./deploy_test_b2c15/default_delivery_address.ibd", O_RDWR) = 1370??
      

      Full strace:

      [pid 29937] open("./demo_b2c15/novinky4_kategorie_priv_write.ibd", O_RDONLY) = 3810
      [pid 29937] lseek(3810, 0, SEEK_CUR)    = 0
      [pid 29937] lseek(3810, 0, SEEK_END)    = 131072
      [pid 29937] lseek(3810, 0, SEEK_SET)    = 0
      [pid 29937] pread(3810, "\343t\2[\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\3\351\31;\355\0\10\0\0\0\0\0\0"..., 16384, 0) = 16384
      [pid 29937] close(3810)                 = 0
      [pid 29937] open("./demo_b2c15/novinky4_kategorie_priv_write.ibd", O_RDWR) = 3810
      [pid 29937] fcntl(3810, F_SETLK, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0}) = 0
      [pid 29937] pread(3810, "\343t\2[\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\3\351\31;\355\0\10\0\0\0\0\0\0"..., 16384, 0) = 16384
      [pid 29937] open("./demo_b2c15/novinky4_soubory.isl", O_RDWR) = -1 ENOENT (No such file or directory)
      [pid 29937] open("./demo_b2c15/novinky4_soubory.ibd", O_RDONLY) = 4108
      [pid 29937] fstat(4108, {st_mode=S_IFREG|0660, st_size=114688, ...}) = 0
      [pid 29937] pread(4108, "W\352G\231\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\3\351\31`/\0\10\0\0\0\0\0\0"..., 65536, 0) = 65536
      [pid 29937] close(4108)                 = 0
      [pid 29937] close(3811)                 = 0
      [pid 29937] open("./demo_b2c15/novinky4_soubory.ibd", O_RDONLY) = 3811
      [pid 29937] lseek(3811, 0, SEEK_CUR)    = 0
      [pid 29937] lseek(3811, 0, SEEK_END)    = 114688
      [pid 29937] lseek(3811, 0, SEEK_SET)    = 0
      [pid 29937] pread(3811, "W\352G\231\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\3\351\31`/\0\10\0\0\0\0\0\0"..., 16384, 0) = 16384
      [pid 29937] close(3811)                 = 0
      [pid 29937] open("./demo_b2c15/novinky4_soubory.ibd", O_RDWR) = 3811
      [pid 29937] fcntl(3811, F_SETLK, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0}) = 0
      [pid 29937] pread(3811, "W\352G\231\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\3\351\31`/\0\10\0\0\0\0\0\0"..., 16384, 0) = 16384
      [pid 29937] open("./demo_b2c15/novinky5.isl", O_RDWR) = -1 ENOENT (No such file or directory)
      [pid 29937] open("./demo_b2c15/novinky5.ibd", O_RDONLY) = 4108
      [pid 29937] fstat(4108, {st_mode=S_IFREG|0660, st_size=212992, ...}) = 0
      [pid 29937] pread(4108, "\6\242(\27\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\3\351\32\10\352\0\10\0\0\0\0\0\0"..., 65536, 0) = 65536
      [pid 29937] close(4108)                 = 0
      [pid 29937] close(3812)                 = 0
      [pid 29937] open("./demo_b2c15/novinky5.ibd", O_RDONLY) = 3812
      [pid 29937] lseek(3812, 0, SEEK_CUR)    = 0
      [pid 29937] lseek(3812, 0, SEEK_END)    = 212992
      [pid 29937] lseek(3812, 0, SEEK_SET)    = 0
      [pid 29937] pread(3812, "\6\242(\27\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\3\351\32\10\352\0\10\0\0\0\0\0\0"..., 16384, 0) = 16384
      [pid 29937] close(3812)                 = 0
      [pid 29937] open("./demo_b2c15/novinky5.ibd", O_RDWR) = 3812
      [pid 29937] fcntl(3812, F_SETLK, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0}) = 0
      [pid 29937] pread(3812, "\6\242(\27\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\3\351\32\10\352\0\10\0\0\0\0\0\0"..., 16384, 0) = 16384
      [pid 29937] open("./demo_b2c15/novinky5_formulare.isl", O_RDWR) = -1 ENOENT (No such file or directory)
      [pid 29937] open("./demo_b2c15/novinky5_formulare.ibd", O_RDONLY) = 4108
      [pid 29937] fstat(4108, {st_mode=S_IFREG|0660, st_size=114688, ...}) = 0
      [pid 29937] pread(4108, "\253\270\202\216\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\3\351\32\323)\0\10\0\0\0\0\0\0"..., 65536, 0) = 65536
      [pid 29937] close(4108)                 = 0
      [pid 29937] close(3813)                 = 0
      

      I found the very same problem (https://jira.mariadb.org/browse/MDEV-12610) closed as solved in 10.2.7. This is 10.2.8 and looks like problem appears again.

      This db was 10.0.xx upgraded to 10.1.xx and finally to 10.2.8. Can make this upgrade this issue?

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                marko Marko Mäkelä
                Reporter:
                daniel.rimal@cldn.cz Dan Rimal
              • Votes:
                1 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: