Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2012
    Posts
    1

    Unanswered: select query optimization

    Hi All,
    Can any one please help me how to solve performance issue in mysql database.

    Problem is from java/hibernet i will read a file which contains 10Lakhs records. with hibernet select query i am checking whether data already exist or not.
    From java application logs i can see the select queries are executed very fast till 2 Lakhs records, after that it is pausing for some time and then executing few select statements.if same database is connected by multiple clients it is becoming much slow.Below is my sql file my.cnf configuration ,Please let me know which parameter should add or change values for solving my performance issue.
    my.cnf file contains the following information.
    [mysqld]
    innodb_buffer_pool_size = 2G
    innodb_log_buffer_size=5m
    innodb_flush_log_at_trx_commit=2
    innodb_lock_wait_timeout=120
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    init_connect=’SET collation_connection = utf8_general_ci; SET NAMES utf8;’
    default-character-set=utf8
    character-set-server=utf8
    collation-server=utf8_general_ci
    [client]
    default-character-set=utf8
    set-variable = max_allowed_packet=32M
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

    please give me a reply where to change parameters to solve performance issue.

    Thanks,
    Mallikarjun Reddy.

  2. #2
    Join Date
    Mar 2004
    Posts
    14
    Hi, you should use myisam.

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Are you sure you have the correct indexes on the tables so that accessing the individual records is done efficiently? I would look more closely at your queries rather than the database server settings first. If you are convinced that your queries are efficient and using indexes then look at the settings above.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •