GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Maximum number of insertions

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

  • Maximum number of insertions

    I need that mysql support an average of 200 insertions per second and that this value can be scalable, it is possible? ... that would be necessary hardware and configurations? ... thanks

  • #2
    Actually maximum throughput depends on hardware, configuration, database size and schema. But yes - it is really possible - 200 qps is really low threshold. We had some clients with thousands of inserts per second and they have pretty standard mysql. Everything depends on approach and configs )

    Comment


    • #3
      As a small example I can say that I get up to about 8000 inserts/second with InnoDB table on my 3 year old laptop.
      As long as it doesn't have to write to disk to much. Since a laptop disk is so very slow in situations like this.
      Example figures:

      50000 records in: 6.27902913093567 seconds50000 records in: 6.30907201766968 seconds50000 records in: 9.94429898262024 seconds50000 records in: 7.40064191818237 seconds50000 records in: 6.3791720867157 seconds50000 records in: 7.81123185157776 seconds

      (The dip i performance in the middle is due to other applications also running on my laptop.)

      So buy a real server with a lot of RAM and fast CPU then there is no doubt that it can handle a lot more inserts than 200 per second even if your DB is big in size.

      The question is now how heavy selects you will run against this machine at the same time?

      Comment


      • #4
        Accurate, the application is actually a mail server in a duplication enbebido existing aplicaion this nesecita first make 8 inserts a parsing of data that comes in the form of mail, then 6 consultations to validate the operation, and just send the data paraseada another apliacacion this event and register again in the bd with 8 more inserts, this completes the routine has to do as 200 times per second minimum, I tried it on a server with 4 cpu redhat 4, and does not give optimal results , so if I could reach configurations that would have to be done in the bd to make this the most optimal possible by the side of this code in C and optimized. Thanks

        Comment


        • #5
          OK, but you will have to give us some information about your hardware, like RAM, disks etc.

          And you have to tell us if you are you running MyISAM or InnoDB tables? The settings depend a lot on this.

          You also has to tell us if this is a standalone SQL server or if the server is performing a lot of other stuff as well, depends on how much RAM we can allow the MySQL instance to allocate.

          Comment


          • #6
            The tables are MyISAM, my hardware info is:
            Uname-a
            Linux 2.6.9-42.ELsmp # 1 SMP Mon Jul 12 23:27:17 EDT 2006 i686 i686 i386 GNU / Linux
            [Root @ src noparadigma02] # free
            Total used free cached shared buffers
            Mem: 4933844 3089312 1844532 0 76856 2372344
            -- / + Buffers / cache: 640112 4293732
            Swap: 4192956 0 4192956

            This state in normal use. Thanks

            Comment


            • #7
              OK, looks fine and dandy.

              How big are the tables in rows and MB?

              Do you have a lot of selects running against these tables also?
              And more important is the application deleting records during normal working routines?

              Output from:

              SHOW STATUS LIKE 'Table_locks%';

              Comment

              Working...
              X