Optimizing MySQL Help

[abazoskib]

+-----------+
| version() |
+-----------+
| 5.0.45    | 
+-----------+

 

machine specs:

 cat /proc/meminfo
MemTotal:      1788724 kB
MemFree:        849172 kB
Buffers:        144580 kB
Cached:         627588 kB
SwapCached:          0 kB
Active:         790900 kB
Inactive:        86064 kB
HighTotal:     1044488 kB
HighFree:       301460 kB
LowTotal:       744236 kB
LowFree:        547712 kB
SwapTotal:      917496 kB
SwapFree:       917496 kB
Dirty:             328 kB
Writeback:           0 kB
AnonPages:      104796 kB
Mapped:          15184 kB
Slab:            24828 kB
SReclaimable:    18352 kB
SUnreclaim:       6476 kB
PageTables:       1940 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:   1811856 kB
Committed_AS:   263756 kB
VmallocTotal:   114680 kB
VmallocUsed:      1360 kB
VmallocChunk:   112936 kB

 

my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-networking
set-variable = max_connections=2000

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

I run queries on a table with at least a million and a half records(and growing) that are pretty slow. I noticed I have no memory optimization in my.cnf. What can I do in terms of my.cnf to speed things up?

Let me know if I need to post any other information. Thanks.

[gassaz]

You can try adding the line

query_cache_size=x

x = this value will depends of the amount of system memory, on my production server i set it up to 64M.

 

If you tables are InnoDb like, you can try adding this too:

innodb_buffer_pool_size=y

y= this value will depends of the amount of system memory, this don't need to be like query_cache_size even it could be more or less.

 

I hope this can help you.

 

[Mchl]

http://pento.net/2009/03/05/a-brief-introduction-to-mysql-performance-tuning/

[abazoskib]

thanks for the help guys. can anyone estimate what i would set my max query cache size from the system specs i posted above?

[gassaz]

I think the only way to get the magic number is increasing the values until get the best performance. For example I started at 32M, now i have a performance that work for my needs.

[Mchl]

It depends if this is a dedicated MySQL machine, or if there are other services running there. Also do not underestimate the possibility, that you can increase performance by adding indexes or optimising the query.