MySQL Performance: LAMP => MySQL very slow for big SELECT

MySQL very slow for big SELECT - Optimize MySQL [message #3097]

Mon, 19 May 2008 06:50

Player
Messages: 3
Registered: May 2008

Junior Member

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...

Report message to a moderator