•  

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL - performance problem

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

  • MySQL - performance problem

    #1
    Hi,

    I have problem with opening a table through table component in Delphi through BDEODBCMySQL.

    I have a table with size of 180 MB. It is imported from paradox table to Mysql – innodb plugin. It has around 2.5 lakh records with 150 fields.

    It takes minimum 22 seconds to just opening the table through select * from .

    My hard configuration is :
    4x Intel(R) Xeon(R) CPU 5160 @ 3.00 GHZ, 3.2GB RAM
    And I am using innodb plugin.

    My current my.ini configuration is :
    [mysql]
    default-character-set=latin1
    [mysqld]
    character_set_server = utf8
    default-storage-engine=INNODB
    ignore-builtin-innodb
    plugin-load=innodb=ha_innodb_plugin.dll;innodb_trx=ha_inn odb _plugin.dll;innodb_locks=ha_innodb_plugin.dll;inno db_lock_wa its=h
    a_innodb_plugin.dll;innodb_cmp=ha_innodb_plugin.dl l;innodb_c mp_reset=ha_innodb_plugin.dll;innodb_cmpmem=ha_inn odb_plugin .dll;
    innodb_cmpmem_reset=ha_innodb_plugin.dll
    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_type=0
    query_cache_size=0
    query_cache_limit=0
    table_cache=64
    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=0
    thread_cache_size=8
    innodb_additional_mem_pool_size=2M
    innodb_flush_log_at_trx_commit=0
    innodb_log_buffer_size=8M
    innodb_buffer_pool_size=1G
    innodb_read_io_threads=1
    innodb_write_io_threads=1
    innodb_data_file_path=ibdata1:400M: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=50
    innodb_doublewrite=0
    skip_name_resolve
    [mysqldump]
    quick
    max_allowed_packet=16M
    [mysql]
    no-auto-rehash
    [mysqlhotcopy]
    interactive-timeout
    [mysqld_safe]
    open-files-limit=8192

    Can anybody please suggest me good my.ini configuration ?

    Thanks in advance.
    Tags: None

  • #2
    • sridars wrote on Wed, 13 October 2010 15:41


    It takes minimum 22 seconds to just opening the table through select * from .


    1.
    Do you really want _all_ data from that table?
    That means that all this data has to be exported from MySQL through ODBC to BDE, usually with a bit of type conversion on the way.
    Use WHERE or LIMIT to limit the number of rows.

    2.
    Try and run that query directly on the MySQL server without BDE->ODBC and check the speed.
    Because my initial feeling is that I don't think that MySQL config is your problem in this case.

    I don't use BDE but there has been quite a few posts here with people having performance issues with it so try searching this forum.

    Good luck!

    Comment


    • #3
      Hi,

      Thanks for your reply.

      Actually i need all the data, whether i am using it or not. i need to open a table component which is connected with a table, so there is no restrictions to be given.

      also i have checked directly the query in Mysql server(query browser) with 'select * from it takes mininum 20 seconds to fetch all the records.

      Anyway i will try checking any configuration to be done in Odbc or BDE.

      Thanks

      Comment


      • #4
        • sridars wrote on Fri, 15 October 2010 08:41


        also i have checked directly the query in Mysql server(query browser) with 'select * from it takes mininum 20 seconds to fetch all the records.


        And that is when you run the query browser on the same machine as the database, right?

        What happens on the server when you issue the query?
        High CPU or high I/O?

        Are you sure that the table size is 180MB?
        With a fast calculation I get:
        180MB / 250,000 (if I understand lakhs right) / 150 fields = 4.8 bytes per field which sounds very small.

        Comment


        • #5
          Hi,

          I have 4 CPU's, so i don't see exact CPU usage. i have local machine which is single CPU there with same kind of data, i see it is showing 100% CPU Usage when i run the query.

          It is the same machine with client/server.

          Do you have any idea of ODBC advance options settings. there i could see an option called "Don't cache result (forward only cursors)", when i tag this option, it is very very speed, even in 1 sec i could open the table. but the problem is, i cannot open another table at the same moment, i need to close the first one then only i could open the second table. also while closing the first table, it takes minimum 1 seconds.

          Kindly give me some idea related ODBC and also speed.

          Thanks

          Comment

            • Working...
            X