•  

Announcement

Announcement Module
Collapse
No announcement yet.

Strange MySQL behavior

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • Strange MySQL behavior

    #1
    Some days ago I encountered with a strange problem. MySQL and Apache resides both on the same server. In attempt to optimize MySQL someone increased the key buffer too much. Also the number of temp tables has been set too large.

    At first it worked fine, but when the server load increased, Apache used some memory, while MySQL started to create temp tables on disk instead of memory. Queries took more time to complete, and new connections continued to arrive. In less than 20 minutes the whole server nearly stopped. There were about 20 running processes, all in the state "Copying to temp table".

    The question is: how to avoid such situation ? In, for example, MSSQL this scenario is hardly possible.
    Tags: None
  • #2
    AlexN wrote on Tue, 16 October 2007 09:42


    The question is: how to avoid such situation ? In, for example, MSSQL this scenario is hardly possible.


    Not true, if you set the memory usage limits for MSSQL to use more memory than you have RAM in the machine it will also bring the system to it's knees.
    It's a OS problem not a DBMS problem.

    The problem is that you want to give the DBMS as much RAM memory as possible to use as cache to avoid disk IO.

    _BUT_ if you set this value too high then the OS will start to swap and that will degrade performance _a_lot_.

    So it is better to stay on the safe side with the memory settings, especially since you are running Apache/PHP on the same server which also will consume unregular amounts of RAM.

    Comment

    • #3
      sterin wrote on Tue, 16 October 2007 09:21



      So it is better to stay on the safe side with the memory settings, especially since you are running Apache/PHP on the same server which also will consume unregular amounts of RAM.



      Exactly. But where is the safe side ? As you correctly pointed, there are other programs on server that use unregular amounts of RAM. So when the load increases, the memory load can pass some threshold above which the system will go down.
      Is it a possible DOS-attack scenario ?

      Comment

      • #4
        Quote:


        Exactly. But where is the safe side ?
        ...


        That is for the administrator/developer to decide (I guess that you are one of them ).

        The problem is that you can't give a generic answer since it depends on the application.

        As for a DOS attack, that is actually attacking the TCP stack of the OS so the outcome of that depends on how the OS handles it.

        Comment

        • #5
          sterin wrote on Tue, 16 October 2007 12:24


          That is for the administrator/developer to decide (I guess that you are one of them ).



          That's right, but I can not decide without necessary information. That's what I am looking for. The system seems to be tuned correctly, but then it goes down in minutes. It is like an avalanche.

          sterin wrote on Tue, 16 October 2007 12:24


          As for a DOS attack, that is actually attacking the TCP stack of the OS so the outcome of that depends on how the OS handles it.



          Not necessary. The term "Denial Of Service" is more general, as there are many sorts of DOS attacks. Various components of a system could be DOSed. Including MySQL server, of course.

          Comment

          • #6
            AlexN wrote on Wed, 17 October 2007 09:35


            That's right, but I can not decide without necessary information. That's what I am looking for. The system seems to be tuned correctly, but then it goes down in minutes. It is like an avalanche.


            Correct, and the information here is that you don't have enough headroom to properly handle it. Ergo, reduce the settings to give more headroom to handle those cases. And avalanche is the correct name for it because it usually happens very fast when you reach the limit.

            It's like driving a car.
            You can go faster and faster by narrowing the margins. And in the end you go really fast and then suddenly you crash and you are lying upside down in a ditch and wonder why you didn't go slower with more margins.

            AlexN wrote on Wed, 17 October 2007 09:35


            Not necessary. The term "Denial Of Service" is more general, as there are many sorts of DOS attacks. Various components of a system could be DOSed. Including MySQL server, of course.


            Granted that the expression can be used in a broader perspective.
            But how are any malicious DOS'ers going to reach MySQL?
            If you have designed and setup your system properly then you shouldn't expose the DB to the public and that means that all DB access has to go through your application and then it is up to the application to handle it.

            Comment

            • #7
              sterin wrote on Wed, 17 October 2007 19:55


              But how are any malicious DOS'ers going to reach MySQL?



              Easily. Just browse the site and find some slow queries that
              you beleive are handled by MySQL. Usually it could be search,
              price calculation, statistics, etc. Run those queries from
              many connections at once, and you're done. The problem is,
              that to bring down MySQL with badly designed database you
              need sufficiently less connections than to bring down TCP/IP
              stack. Also there exist software that prevents DOS attacks
              on TCP/IP stack, but there is no software to detect attacks
              on MySQL...

              Comment

              • #8
                But that is not a DOS attack targeted against MySQL per se.

                That is an attack targeted against the slow and weak parts of your application and it's design.
                The fact that your application is using a database in the background is another matter.
                Because if you have bad PHP code (or whatever language you are using) you can target it the same way.

                The attacker only knows that the page is slow and that is what he is attacking.

                Comment

                • #9
                  sterin wrote on Thu, 18 October 2007 05:03


                  Because if you have bad PHP code (or whatever language you are using) you can target it the same way.

                  The attacker only knows that the page is slow and that is what he is attacking.



                  Not really. Just PHP code will not cause that 'avalanche' scenario. The response time will increase more or less linear with increasing load.

                  Comment

                  • #10
                    AlexN wrote on Thu, 18 October 2007 12:26


                    Not really. Just PHP code will not cause that 'avalanche' scenario. The response time will increase more or less linear with increasing load.


                    As long as it is CPU bound yes, but the webserver with the PHP interpreter can also allocate a lot of memory and then you will experience the exact same thing.

                    Since the whole reason for the avalanche effect is that you have run out of RAM on the machine and it starts to swap.

                    And when that has happened it starts spending almost all CPU time (worst case) to just swap in and out all active processes to/from RAM trying to execute them and basically nothing gets done.

                    Comment

                    Working...
                    X