GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL Database performance issue-urgent help plz

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

  • MySQL Database performance issue-urgent help plz

    Hi,

    We have MYSQL database and having very bad performance issue. Please help me to resolve.

    Here is configuration details
    =================================================
    Server: Windows Server 2003

    Edition: Standard Edition

    Services Pack: Service pack 1

    CPU: Intel Xenon(R) CPU E5320

    Processor: 1.86 GHz

    RAM: 16 GB
    =================================================

    Software Configuration:
    ======================

    MYSQL server database Version: 5.0

    Here is my.ini file which is located under the C:\Program Files\MySQL\MySQL Server 5.0\

    [mysql]

    default-character-set=latin1

    [mysqld]

    # The default storage engine that will be used when create new tables when

    default-storage-engine=INNODB

    # Set the SQL mode to strict

    sql-mode=" STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTI ON "

    max_connections=100
    query_cache_size=0
    table_cache=256
    tmp_table_size=18M
    thread_cache_size=8

    #*** MyISAM Specific options

    myisam_max_sort_file_size=100G
    myisam_max_extra_sort_file_size=100G
    myisam_sort_buffer_size=35M
    key_buffer_size=25M
    read_buffer_size=64K
    read_rnd_buffer_size=256K
    sort_buffer_size=256K

    #*** INNODB Specific options ***

    #skip-innodb
    innodb_additional_mem_pool_size=2M
    innodb_flush_log_at_trx_commit=1
    innodb_log_buffer_size=1M
    innodb_buffer_pool_size=47M
    innodb_log_file_size=24M
    innodb_thread_concurrency=10
    =================================
    =================
    Database Details:
    =================

    Size of Database: around 2 GB

    Databaes Engine: INNODB

    Number of Tables: 15

    Maximum count row in each table:

    >select count(*) from audit_trail:

    output:

    385567

    table size: 91 MB and Index Size is 59 MB
    ================
    select count(*) from follwup_complaints

    353988

    table size: 73 MB and Index Size is 50 MB
    ================
    select count(*) from mail_audit;

    447237

    table Size: 597 MB and index size is 35 MB
    ================
    select count(*) from mis_reports;

    148959
    ============================================

    Please let me know what will be the optimal memory parameters need to be setup to get good performance.

    We are using default my.ini parameter file.

    The database response is very poor,

    Thanks

    Mohammed.

  • #2
    The by far most important setting for you is the innodb_buffer_pool_size which in your case is ridiculously small (47M), it defines how much RAM you allow MySQL to use for cache of InnoDB data. Max recommended size on a dedicated DB server is about 80% of RAM or if the DB is smaller than RAM (as in your case) then you could set it to a smaller value more representative of the actual size of the DB size.

    Start by changing to these and restart MySQL:

    innodb_buffer_pool_size=2Ginnodb_additional_mem_po ol_size = 16M


    And see if it solves your problem.
    Remember that you can look at the example my.ini that comes with the MySQL distribution to get an idea about suitable configuration values.

    Comment


    • #3
      Sterin,

      Thanks for your response and looks like you are expert in MYSQL db because I am looking your all articals and it was very fantastic.

      Already I have implemented this parameters from your articals.

      Please let me know is there any parameter for all DML statement to improve the performance of database and is there any best practies document available? for database maintanance on weekly basis.

      like rebuild indexes and what method we need to use to rebuild the indexes or update the statistics or optimize the table.

      Please request you to provide some more details on this.

      Thanks in advance for your help.

      Thanks

      Mohammed.

      Comment


      • #4
        Hi,

        First - @sterin is right on, if you can fit your tables in the innodb buffer pool you will see much improved performance.

        Second - I don't see that you have the slowquery log turned on, you should so you can see which queries are take a long time to run.


        [mysqld]log-slow-queries = /tmp/slowqueries.log


        Third - you didn't give much info about your system. Is this a dedicated MySQL server? What is the hard drive configuration, are you running RAID with BBU? How man CPUs?

        Without fully understand your configuration, I can't say that the following will help performance or give you the level of data integrity, you should read each configuration setting before trying.


        [mysqld]# Read and Understand what this variable does!# http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_com mitinnodb_flush_log_at_trx_commit = 2# extensions of percona build# If you have a lot of cpus, increase the number of read / write threads.innodb_read_io_threads=4# extensions of percona build# It appears that you are capturing audit logs, which in general heavy writes than reads.# Added more write threads maybe helpful to youinnodb_write_io_threads=4# extensions of percona build# tell innodb that the system is capable of performing more IOPS than the default.innodb_io_capacity=1200 # 12 drives in system * 100, should be a good starting point.

        Comment

        Working...
        X