Can I put the MySQL binlogs on a slow disk?

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP











up vote
6
down vote

favorite












We have only 450 GB nvme space on our master server and the binlogs are using a lot of space, even if they are kept for only two days.



Does writing the MySQL binlogs to a slower disk (like a remote directory) slow down the MySQL performance?










share|improve this question



























    up vote
    6
    down vote

    favorite












    We have only 450 GB nvme space on our master server and the binlogs are using a lot of space, even if they are kept for only two days.



    Does writing the MySQL binlogs to a slower disk (like a remote directory) slow down the MySQL performance?










    share|improve this question

























      up vote
      6
      down vote

      favorite









      up vote
      6
      down vote

      favorite











      We have only 450 GB nvme space on our master server and the binlogs are using a lot of space, even if they are kept for only two days.



      Does writing the MySQL binlogs to a slower disk (like a remote directory) slow down the MySQL performance?










      share|improve this question















      We have only 450 GB nvme space on our master server and the binlogs are using a lot of space, even if they are kept for only two days.



      Does writing the MySQL binlogs to a slower disk (like a remote directory) slow down the MySQL performance?







      mysql mysql-replication capacity-planning






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 27 at 18:25









      chicks

      2,98171831




      2,98171831










      asked Nov 23 at 10:34









      the_nuts

      295211




      295211




















          2 Answers
          2






          active

          oldest

          votes

















          up vote
          6
          down vote













          Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done, so I imagine that placing your logs on a slower disk can have an impact as other transactions will be delayed until the current transaction is logged.



          I would keep your binary logs on your fastest storage, but reduce the amount of logs on the flash drive by keeping only those that are still required for replication.



          You can automate and more frequently run the procedure for purging logs as outlined in the manual and delete all logs that are no longer needed because the slaves have processed them.




          1. On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.


          2. Obtain a listing of the binary log files on the master server with SHOW BINARY LOGS.


          3. Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date, this is the last log file on the list.


          4. Make a backup of all the log files you are about to delete. (This step is optional, but always advisable.)


          5. Purge all log files up to but not including the target file.




          If you want to retain more logs, for instance for auditing purposes, in step 4 copy those logs to a slower (spinning) disk before deleting them from your flash drive with the PURGE BINARY LOGS TO or PURGE BINARY LOGS BEFORE MySQL statement.






          share|improve this answer





























            up vote
            5
            down vote













            From MySQL manual:




            By default, the binary log is synchronized to disk at each write
            (sync_binlog=1). If sync_binlog was not enabled, and the operating
            system or machine (not only the MySQL server) crashed, there is a
            chance that the last statements of the binary log could be lost. To
            prevent this, enable the sync_binlog system variable to synchronize
            the binary log to disk after every N commit groups. See Section 5.1.8,
            “Server System Variables”. The safest value for sync_binlog is 1 (the
            default), but this is also the slowest.




            The bold part of the above quote means that a slow storage will put an upper limit on your INSERT rate. As partial mitigation the binlog is sequentially written, so you will not pay for seek latency.



            Using a single 7200 RPM disk as an example of dedicated binlog device: with an average rotational latency of ~4.1 ms, you can expect ~250 write I/O per second. This obviously presumes no NVRAM/writeback cache on the disk side: if such a cache exists, then synched writes are immediately absorbed by it.



            Please also note that you can disable synchronization for the binlog, basically transforming the problem from a latency-bound one to a throughput-bound one. But be sure to understand what it means for data consistency.






            share|improve this answer






















              Your Answer








              StackExchange.ready(function()
              var channelOptions =
              tags: "".split(" "),
              id: "2"
              ;
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function()
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled)
              StackExchange.using("snippets", function()
              createEditor();
              );

              else
              createEditor();

              );

              function createEditor()
              StackExchange.prepareEditor(
              heartbeatType: 'answer',
              convertImagesToLinks: true,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: 10,
              bindNavPrevention: true,
              postfix: "",
              imageUploader:
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              ,
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              );



              );













              draft saved

              draft discarded


















              StackExchange.ready(
              function ()
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f941323%2fcan-i-put-the-mysql-binlogs-on-a-slow-disk%23new-answer', 'question_page');

              );

              Post as a guest















              Required, but never shown

























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes








              up vote
              6
              down vote













              Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done, so I imagine that placing your logs on a slower disk can have an impact as other transactions will be delayed until the current transaction is logged.



              I would keep your binary logs on your fastest storage, but reduce the amount of logs on the flash drive by keeping only those that are still required for replication.



              You can automate and more frequently run the procedure for purging logs as outlined in the manual and delete all logs that are no longer needed because the slaves have processed them.




              1. On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.


              2. Obtain a listing of the binary log files on the master server with SHOW BINARY LOGS.


              3. Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date, this is the last log file on the list.


              4. Make a backup of all the log files you are about to delete. (This step is optional, but always advisable.)


              5. Purge all log files up to but not including the target file.




              If you want to retain more logs, for instance for auditing purposes, in step 4 copy those logs to a slower (spinning) disk before deleting them from your flash drive with the PURGE BINARY LOGS TO or PURGE BINARY LOGS BEFORE MySQL statement.






              share|improve this answer


























                up vote
                6
                down vote













                Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done, so I imagine that placing your logs on a slower disk can have an impact as other transactions will be delayed until the current transaction is logged.



                I would keep your binary logs on your fastest storage, but reduce the amount of logs on the flash drive by keeping only those that are still required for replication.



                You can automate and more frequently run the procedure for purging logs as outlined in the manual and delete all logs that are no longer needed because the slaves have processed them.




                1. On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.


                2. Obtain a listing of the binary log files on the master server with SHOW BINARY LOGS.


                3. Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date, this is the last log file on the list.


                4. Make a backup of all the log files you are about to delete. (This step is optional, but always advisable.)


                5. Purge all log files up to but not including the target file.




                If you want to retain more logs, for instance for auditing purposes, in step 4 copy those logs to a slower (spinning) disk before deleting them from your flash drive with the PURGE BINARY LOGS TO or PURGE BINARY LOGS BEFORE MySQL statement.






                share|improve this answer
























                  up vote
                  6
                  down vote










                  up vote
                  6
                  down vote









                  Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done, so I imagine that placing your logs on a slower disk can have an impact as other transactions will be delayed until the current transaction is logged.



                  I would keep your binary logs on your fastest storage, but reduce the amount of logs on the flash drive by keeping only those that are still required for replication.



                  You can automate and more frequently run the procedure for purging logs as outlined in the manual and delete all logs that are no longer needed because the slaves have processed them.




                  1. On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.


                  2. Obtain a listing of the binary log files on the master server with SHOW BINARY LOGS.


                  3. Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date, this is the last log file on the list.


                  4. Make a backup of all the log files you are about to delete. (This step is optional, but always advisable.)


                  5. Purge all log files up to but not including the target file.




                  If you want to retain more logs, for instance for auditing purposes, in step 4 copy those logs to a slower (spinning) disk before deleting them from your flash drive with the PURGE BINARY LOGS TO or PURGE BINARY LOGS BEFORE MySQL statement.






                  share|improve this answer














                  Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done, so I imagine that placing your logs on a slower disk can have an impact as other transactions will be delayed until the current transaction is logged.



                  I would keep your binary logs on your fastest storage, but reduce the amount of logs on the flash drive by keeping only those that are still required for replication.



                  You can automate and more frequently run the procedure for purging logs as outlined in the manual and delete all logs that are no longer needed because the slaves have processed them.




                  1. On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.


                  2. Obtain a listing of the binary log files on the master server with SHOW BINARY LOGS.


                  3. Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date, this is the last log file on the list.


                  4. Make a backup of all the log files you are about to delete. (This step is optional, but always advisable.)


                  5. Purge all log files up to but not including the target file.




                  If you want to retain more logs, for instance for auditing purposes, in step 4 copy those logs to a slower (spinning) disk before deleting them from your flash drive with the PURGE BINARY LOGS TO or PURGE BINARY LOGS BEFORE MySQL statement.







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Nov 23 at 13:31

























                  answered Nov 23 at 11:21









                  HBruijn

                  52k1086142




                  52k1086142






















                      up vote
                      5
                      down vote













                      From MySQL manual:




                      By default, the binary log is synchronized to disk at each write
                      (sync_binlog=1). If sync_binlog was not enabled, and the operating
                      system or machine (not only the MySQL server) crashed, there is a
                      chance that the last statements of the binary log could be lost. To
                      prevent this, enable the sync_binlog system variable to synchronize
                      the binary log to disk after every N commit groups. See Section 5.1.8,
                      “Server System Variables”. The safest value for sync_binlog is 1 (the
                      default), but this is also the slowest.




                      The bold part of the above quote means that a slow storage will put an upper limit on your INSERT rate. As partial mitigation the binlog is sequentially written, so you will not pay for seek latency.



                      Using a single 7200 RPM disk as an example of dedicated binlog device: with an average rotational latency of ~4.1 ms, you can expect ~250 write I/O per second. This obviously presumes no NVRAM/writeback cache on the disk side: if such a cache exists, then synched writes are immediately absorbed by it.



                      Please also note that you can disable synchronization for the binlog, basically transforming the problem from a latency-bound one to a throughput-bound one. But be sure to understand what it means for data consistency.






                      share|improve this answer


























                        up vote
                        5
                        down vote













                        From MySQL manual:




                        By default, the binary log is synchronized to disk at each write
                        (sync_binlog=1). If sync_binlog was not enabled, and the operating
                        system or machine (not only the MySQL server) crashed, there is a
                        chance that the last statements of the binary log could be lost. To
                        prevent this, enable the sync_binlog system variable to synchronize
                        the binary log to disk after every N commit groups. See Section 5.1.8,
                        “Server System Variables”. The safest value for sync_binlog is 1 (the
                        default), but this is also the slowest.




                        The bold part of the above quote means that a slow storage will put an upper limit on your INSERT rate. As partial mitigation the binlog is sequentially written, so you will not pay for seek latency.



                        Using a single 7200 RPM disk as an example of dedicated binlog device: with an average rotational latency of ~4.1 ms, you can expect ~250 write I/O per second. This obviously presumes no NVRAM/writeback cache on the disk side: if such a cache exists, then synched writes are immediately absorbed by it.



                        Please also note that you can disable synchronization for the binlog, basically transforming the problem from a latency-bound one to a throughput-bound one. But be sure to understand what it means for data consistency.






                        share|improve this answer
























                          up vote
                          5
                          down vote










                          up vote
                          5
                          down vote









                          From MySQL manual:




                          By default, the binary log is synchronized to disk at each write
                          (sync_binlog=1). If sync_binlog was not enabled, and the operating
                          system or machine (not only the MySQL server) crashed, there is a
                          chance that the last statements of the binary log could be lost. To
                          prevent this, enable the sync_binlog system variable to synchronize
                          the binary log to disk after every N commit groups. See Section 5.1.8,
                          “Server System Variables”. The safest value for sync_binlog is 1 (the
                          default), but this is also the slowest.




                          The bold part of the above quote means that a slow storage will put an upper limit on your INSERT rate. As partial mitigation the binlog is sequentially written, so you will not pay for seek latency.



                          Using a single 7200 RPM disk as an example of dedicated binlog device: with an average rotational latency of ~4.1 ms, you can expect ~250 write I/O per second. This obviously presumes no NVRAM/writeback cache on the disk side: if such a cache exists, then synched writes are immediately absorbed by it.



                          Please also note that you can disable synchronization for the binlog, basically transforming the problem from a latency-bound one to a throughput-bound one. But be sure to understand what it means for data consistency.






                          share|improve this answer














                          From MySQL manual:




                          By default, the binary log is synchronized to disk at each write
                          (sync_binlog=1). If sync_binlog was not enabled, and the operating
                          system or machine (not only the MySQL server) crashed, there is a
                          chance that the last statements of the binary log could be lost. To
                          prevent this, enable the sync_binlog system variable to synchronize
                          the binary log to disk after every N commit groups. See Section 5.1.8,
                          “Server System Variables”. The safest value for sync_binlog is 1 (the
                          default), but this is also the slowest.




                          The bold part of the above quote means that a slow storage will put an upper limit on your INSERT rate. As partial mitigation the binlog is sequentially written, so you will not pay for seek latency.



                          Using a single 7200 RPM disk as an example of dedicated binlog device: with an average rotational latency of ~4.1 ms, you can expect ~250 write I/O per second. This obviously presumes no NVRAM/writeback cache on the disk side: if such a cache exists, then synched writes are immediately absorbed by it.



                          Please also note that you can disable synchronization for the binlog, basically transforming the problem from a latency-bound one to a throughput-bound one. But be sure to understand what it means for data consistency.







                          share|improve this answer














                          share|improve this answer



                          share|improve this answer








                          edited Nov 23 at 15:21

























                          answered Nov 23 at 14:39









                          shodanshok

                          24.8k34082




                          24.8k34082



























                              draft saved

                              draft discarded
















































                              Thanks for contributing an answer to Server Fault!


                              • Please be sure to answer the question. Provide details and share your research!

                              But avoid


                              • Asking for help, clarification, or responding to other answers.

                              • Making statements based on opinion; back them up with references or personal experience.

                              To learn more, see our tips on writing great answers.





                              Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                              Please pay close attention to the following guidance:


                              • Please be sure to answer the question. Provide details and share your research!

                              But avoid


                              • Asking for help, clarification, or responding to other answers.

                              • Making statements based on opinion; back them up with references or personal experience.

                              To learn more, see our tips on writing great answers.




                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function ()
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f941323%2fcan-i-put-the-mysql-binlogs-on-a-slow-disk%23new-answer', 'question_page');

                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown






                              Popular posts from this blog

                              How to check contact read email or not when send email to Individual?

                              Displaying single band from multi-band raster using QGIS

                              How many registers does an x86_64 CPU actually have?