Welcome! Log In Create A New Profile

Advanced

How to optimize large database?

Posted by netul 
How to optimize large database?
March 29, 2007 01:55AM
My database have over 50,000,000 records and over 2GB size.
I have a dedicated server for MySQL.
How can I optimize mysql for faster search results? (now is over 5-6 seconds on localhost).

mysql configuration now:

key_buffer = 128M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 1M
read_buffer_size = 512K
read_rnd_buffer_size = 256K
net_buffer_length = 16K
thread_stack = 128K
myisam_sort_buffer_size=64M
join_buffer=2M
record_buffer=2M
sort_buffer=4M
thread_cache_size=512
tmp_table_size = 128M

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet=16M

[mysqlcheck]
key_buffer=128M
sort_buffer=128M
read_buffer=64M
write_buffer=64M

[myisamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=32M
write_buffer=32M

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 32M
sort_buffer_size = 16M
read_buffer = 8M
write_buffer = 8M

Thanks.

http://www.netul.ro - Romanian portal
http://director.netul.ro - Romanian web directory



Edited 2 time(s). Last edit at 03/29/2007 01:59AM by netul.
Re: How to optimize large database?
March 29, 2007 02:11AM
I add FULLTEXT index in table 'links'.
The table size is bigger now, but the querys (search results) is faster.
The database size is not a problem for me, I have a lot of space. I want to optimize database for performance (speed).
Another ideas?

http://www.netul.ro - Romanian portal
http://director.netul.ro - Romanian web directory
Re: How to optimize large database?
April 03, 2007 09:54AM
netul Wrote:
-------------------------------------------------------
> I add FULLTEXT index in table 'links'.
> The table size is bigger now, but the querys
> (search results) is faster.
> The database size is not a problem for me, I have
> a lot of space. I want to optimize database for
> performance (speed).
> Another ideas?

tradu`mi si mie in romana comanda pentru aceasta tabela ,
ca sunt si eu pe la 5 GB
Re: How to optimize large database?
April 08, 2007 12:28PM
Hi,

I see that you are trying to create a serach engine. Maybe you could just use the costom APIs from here - http://www.dhoondho.com

This might be of help.

Sphider woudl eb suitable for only a small site which has maximum 50,000 pages.....any more than that...you woudl need to go in for a professionally backed solution.

Regards,
Dhoondho
Anonymous User
Re: How to optimize large database?
April 08, 2007 11:00PM
Try this

(make sure to backup database before)

https://.../sphider/admin/admin.php?f=clean
https://.../sphider/admin/admin.php?f=15
https://.../sphider/admin/admin.php?f=16
https://.../sphider/admin/admin.php?f=17
https://.../sphider/admin/admin.php?f=23
Sorry, only registered users may post in this forum.

Click here to login