Announcement Module
No announcement yet.

MySQL very slow for big SELECT - Optimize MySQL

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

  • MySQL very slow for big SELECT - Optimize MySQL


    I have problem to display many result of big SELECT in my webpages.
    It's an php-application with very big read on the sql (SELECT).

    I have already made lot of changes, settings,... add support of hugepages on the kernel and turn on in MySQL.

    My server :
    - Intel Core2 Duo E6550 2.33GHz
    - 4GO DDR2
    - 750 GO SATA
    - RAID 1

    After installed all i need on my server and enable hugepages with 512, my memory available is 2800MO.

    My tables grow more and more, about 600 insert by day (one by one).
    There are only 50-100 table in SQL.

    In one table, there are 120 000 rows (80MO), and it's slow when i ask him for display 600 rows in the web browser, it take 15 seconds to finish. When i am on my old server, where there are lot of domain (the server aren't only for me), it takes 7 seconds for the same SELECT.
    (by display, i would say that i make an echo with php)

    I have read lot of decumentation, i don't understand what i have forgot or bad do...

    What can i do for optimize MySQL ??

    My configuration file after many changes :

    large_pages = true
    default-storage-engine = MyISAM
    connect_timeout= 10
    join_buffer_size= 1M
    key_buffer = 512M
    long_query_time = 25
    max_allowed_packet = 64M
    max_heap_table_size = 64M
    myisam_sort_buffer_size = 64M
    net_buffer_length = 65536
    query_cache_limit = 16M
    query_cache_size= 512M
    query_prealloc_size = 65536
    query_alloc_block_size = 131072
    read_buffer_size = 1M
    read_rnd_buffer_size = 4M
    sort_buffer_size = 1M
    table_cache = 100
    thread_cache_size = 384
    thread_concurrency = 8
    tmp_table_size = 64M

    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 32M
    write_buffer = 32M

    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 32M
    write_buffer = 32M

    Very thanks...

  • #2
    I think it would be good to ensure that the problem is really in MySQL. Please try doing the same select from command-line client, and see how much time it will take.


    • #3
      Thanks debug,
      I have try the sames querys in the terminal and the result is good, about 2 seconds to finish.
      In the webpage too, if i launch the SELECT querys without display the result (by default i display with an "echo", and it's just one "echo" of all, at the end of the traitements)

      I have reoptimize my php script for delete all i can delete, rename less long, rebuild... to gain ko (the file to load weight now 1mo)

      I have compile apache2 with the mpm prefork support, what settings do you recommend about them and my server ?

      When i echo, many results where loaded directly, and in the middle, it's begin to display block by block of about 30 results.

      It's look like, in a moment, mysql don't would send enough ko, or maybe php, or apache... (like an little buffer)

      If someone have an idea for fix my problems of slowly...


      • #4
        Maybe if you post the php script (minus any confidential stuff), someone might have an idea whats causing the problem.