Web application database optimalisation

  • Filter
  • Time
  • Show
Clear All
new posts

  • Web application database optimalisation


    We have a webapplication for about 5 years that is created by programmers in india.
    It is started as a small apllication and turned into someting larger.

    We have a few performance issues for some queries. Our programmers a now optimizing a few individual queries.

    But i want to optimize the overall performance. I just logged into phpmyadmin and checked the myql status.

    There are a lot of red values, listed below.

    Can someone give me some advice based on information below and the attached documents what to do for optimalisation?

    · Innodb_buffer_pool_reads ************ 1,949 *** The number of logical reads that InnoDB could not* satisfy from buffer pool and had to do a single-page read.*

    · Handler_read_rnd *************************** 7,183 k *************** The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.*

    · Handler_read_rnd_next ** *************** 2,470.36 M ********* The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

    · Created_tmp_disk_tables ************** 8,271 ******************* The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.

    · Select_full_join********************** *********** 2,091 ******************* The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

    · Sort_merge_passes ************************* 154 ********************** The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.

    · Opened_tables ***************** *************** 6,052 ******************* The number of tables that have been opened. If opened tables is big, your table cache value is probably too small. ***

    · Table_locks_waited ************************ 1,242 ******************* The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.