GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Speed problem - Mysql - innodb

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

  • Speed problem - Mysql - innodb

    Hi,

    I am migrating my project from paradox to MySql 5.1. after migration of tables (mysql innodb engine) and also adjusted the coding which is written in Delphi 7.0, i am facing serious problem in speed. compare to Paradox it is 5 times slower.

    In one place where i found very slow speed is in lookup tables. for example, i am using two tables with 20000 records in each. and these two tables used in two table components separately and proper index name is given in the table component. one table component is used with lookup field of key
    information with other table to take the description.

    Now when i navigate the records from first record to last record in both the table components it is taking 15 seconds but the same thing is used with paradox tables it is flashing in 2 secs of 40000 records in total loop.

    when i don't use index name in table components of paradox then it is taking 35 seconds. so here the difference is, when i use index name in the table component then it is flashing, but in mysql whether i use index name or no index name in the table components the result is same.

    Expecting some solutions from our group as soon as possible. I am medding with this problem for the past 1 week.

  • #2
    Do you have anything tangible to present?

    Like what your query looks like?
    What your table looks like (return output from SHOW CREATE TABLE yourTable)?
    The execution plan of your query (return output from EXPLAIN EXTENDED [your query here]?
    InnoDB configuration?
    Hardware specs?
    etc

    Comment


    • #3
      Hi,

      there is no query involved, everything is in table component in Delphi and not a query component with SQL statements.

      There is a problem in speed, so i want to increase the speed like in paradox.

      I don't know where should i fine tune to improve the performance.

      Suggestions are welcome.

      thanks

      Comment


      • #4
        Hi,

        there is no query involved, everything is in table component in Delphi and not a query component with SQL statements.

        There is a problem in speed, so i want to increase the speed like in paradox.

        I don't know where should i fine tune to improve the performance.

        Expecting some suggestions from our group regarding my speed problem.

        innodb configuration : my.ini file (base file is used)
        hardware configuration : 2.4ghz, 2gb RAM, intel pentium 4 CPU


        thanks

        Comment


        • #5
          And with "base file" do you mean my-small.ini or?

          Anyway the main parameter that you have to check is:
          innodb_buffer_pool_size (should be set to 50-80% of the available memory or if you have a smaller database you can just set it large enough so that the database can fit into it.) If this is to small you will have bad performance with InnoDB tables.

          But I would suggest that you turn on query logging in MySQL so you can see what query Delphi is actually sending to MySQL and then you can use this query to find out the execution plan and if you can improve anything on the DB design.

          Because as long as you don't break down where the performance problem actually is, you don't know if it's because Delphi does something odd when it accesses MySQL compared to Paradox, or if it actually is the queries issued against MySQL that is the problem.

          Comment


          • #6
            Thanks I shall check it out

            Comment


            • #7
              I am using a dedicated server for my website ( Magneto webstore) amd mysql database contains more innodb tables. My site has around 5000 vistors/day and handling 8 lakhs apache request/day.

              The server has 8 CPUS
              Xeon(R) CPU 2.67 Ghz
              Memory is 8Gb.

              In mysql configuration file has the entries like
              key_buffer: 128M
              max_allowed_packet: 16M
              table_cache: 4096
              sort_buffer_size: 4M
              read_buffer_size: 4M
              read_rnd_buffer_size: 4M
              myisam_sort_buffer_size: 32M
              thread_cache_size: 128
              query_cache_type: 1
              query_cache_size: 196M
              log_slow_queries=/var/log/mysqld.slowquery.log (not present)
              max_connections: 125
              wait_timeout: 3600
              tmp_table_size: 128M
              max_heap_table_size: 128M
              innodb_buffer_pool_size: (not present)
              innodb_additional_mem_pool_size: (not present)

              But, when online visitors is increases, the server load will increase with range of 50,60,70 and server will going to slow
              and will shows the error as 'Too many connections'. How i set mysql configuration file to increase mysql speed and reduce the server load and connection problem

              Comment


              • #8
                Hi,

                Thanks

                I have configured your kind of settings in my.ini. I have stopped MySql and configured it in my.ini file and then started again. even now the results seems to be same.

                Should i need to run any plug-ins to take the new configurations?

                Please reply

                thanks

                Comment


                • #9
                  Hi,

                  I have configured the same kind of settings, the result was same as before i.e. slow speed. but i have reimported my databases from my old database (paradox) to mysql after configuring the my.ini file, then the result is quite good, i could see lot of improvements in the speed.

                  Now my question is, after configuring the my.ini file, should we necessarily need to reimport the data again to the MySQL database or is there any other way to upgrade our database to suit with adjusted my.ini file?

                  Eagerly waitng for valuable answers from our group.

                  Thanks in advance.

                  Comment


                  • #10
                    No you don't have to reimport the data for settings to take effect, a restart is enough.

                    If things are fast for you after a reimport then the possible reasons is instead that it is fast due to that a reimport automatically "packs" the data in a synchronous fashion.
                    Which means that running an OPTIMIZE TABLE would probably have solved it for you.
                    And generally if a OPTIMIZE TABLE makes such a big difference for you I would say that you are lacking indexes.

                    But my question is what setting you have on the innodb_buffer_pool_size? Since if you are running InnoDB tables then that is the MOST important setting.

                    And beenakms post with the:
                    Quote:


                    innodb_buffer_pool_size: (not present)
                    innodb_additional_mem_pool_size: (not present)


                    I do not know what it means since he says:
                    Quote:


                    mysql database contains more innodb tables


                    he should have a setting for these two variables since the default is far to small for any decent size database.

                    Comment


                    • #11
                      Hi,

                      Thanks for your info. i will check for more index options for my tables.

                      i have set

                      innodb_buffer_pool_size=1g
                      innodb_additional_mem_pool_size = 16M

                      i couldn't set more than 1gb on innodb_buffer_pool_size. if i set more then MySql is not starting up. Do you have more suggestions.

                      Sysem configuration is :
                      intel(R) Xeon(R) CPU 5160 @ 3.00 GHZ, 6GB RAM

                      my.ini settings are :
                      default-character-set=latin1
                      default-storage-engine=INNODB
                      sql-mode=" STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTI ON "
                      max_connections=100
                      max_connect_errors = 10
                      table_open_cache = 2048
                      max_allowed_packet = 16M
                      binlog_cache_size = 1M
                      max_heap_table_size = 64M
                      query_cache_size = 64M
                      query_cache_limit = 2M
                      ft_min_word_len = 4
                      tmp_table_size=64M
                      log-bin=mysql-bin
                      transaction_isolation = REPEATABLE-READ
                      binlog_format=mixed
                      slow_query_log
                      long_query_time = 2
                      thread_cache_size=8
                      innodb_additional_mem_pool_size = 16M
                      innodb_flush_log_at_trx_commit=1
                      innodb_log_buffer_size=8M
                      innodb_buffer_pool_size=1g
                      innodb_data_file_path = ibdata1:10M:autoextend
                      innodb_file_io_threads = 4
                      innodb_log_file_size=24M
                      innodb_thread_concurrency=16
                      thread_concurrency = 8
                      innodb_max_dirty_pages_pct = 90
                      innodb_lock_wait_timeout = 120

                      [mysqldump]
                      # Do not buffer the whole result set in memory before writing it to
                      # file. Required for dumping very large tables
                      quick
                      max_allowed_packet = 16M

                      [mysql]
                      no-auto-rehash

                      # Only allow UPDATEs and DELETEs that use keys.
                      #safe-updates

                      [myisamchk]
                      key_buffer_size = 512M
                      sort_buffer_size = 512M
                      read_buffer = 8M
                      write_buffer = 8M

                      [mysqlhotcopy]
                      interactive-timeout

                      [mysqld_safe]
                      # Increase the amount of open files allowed per process. Warning: Make
                      # sure you have set the global system limit high enough! The high value
                      # is required for a large number of opened tables
                      open-files-limit = 8192

                      ************************************************** *********

                      Give me suggestions to imrpove the speed/performance

                      Thanks

                      Comment

                      Working...
                      X