MySQL very slow for big SELECT - Optimize MySQL

MySQL & MariaDB Other MySQL® Questions
1

Hello,

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 :

[mysqld]
skip-locking
skip-innodb
skip-networking
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

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 32M
write_buffer = 32M

[myisamchk]
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.