gozer
October 29, 2007, 7:12am
1
Hi,
Im running a Intel Xeon Duall cpu 32bit 3ghz, 4gb ram , RAID1 with Linux Debian OS on a Gbit network.
With 22databases each db is around 100mb. The thing is it was first fast at the beginning now its slower. I have edited some values in my.cnf. Now im wondering how can i do some more optimalisation. Does this look OK? Im running on RAID 1 with DAS SCSI 10.000RPM discs with all partitions on 1 disc.
top - 13:46:00 up 18 days, 16:21, 1 user, load average: 0.21, 0.11, 0.08
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
Server is pretty cold now, but to make it even colder I’d suggest to give more memory to innodb (show me your “show global status” command results please to let me figure out an optimal value).
gozer
November 1, 2007, 6:21am
3
mysql> show global status;
The innodb buffer pool size is set to 1024MB should i give more, howmuch more ?
Thanks in advance
You can try to increase it up to 1.5Gb.
gozer
November 1, 2007, 9:55am
5
Ok ill do that tonight. Anything else looks OK? When i run on XFS filesystem i will noticeable performance?
sterin
November 1, 2007, 12:22pm
6
[B]gozer[/B]
Ok ill do that tonight. Anything else looks OK? When i run on XFS filesystem i will noticeable performance?
No, changing to XFS will most probably never be noticed.
When looking at performance for different file systems some parts can sometimes be very fast. But these things are usually marginal because at the end of the day the data is stored/retrieved from physical disks and that hardware is so incredibly slow in comparison with RAM and CPU, that any optimizations you try to do in file system software is usually not noticed.
Now to some things that you should do to speed things up (tried to arrange them in order of impact on performance):
This:
And since 1/5 of your queries is an update/insert command you could think about setting:
This:
There, now you have some things to play with. )
gozer
November 2, 2007, 3:52am
7
Running with the new setting on the MYSQL.
top - 10:18:21 up 10:16, 1 user, load average: 0.00, 0.03, 0.00
2256 mysql 15 0 1921m 1.8g 5980 S 2 47.9 139:06.71 mysqld
Edited some settings in my.cnf
innodb_buffer_pool_size = 1500M
innodb_additional_mem_pool_size = 20M
table_cache = 2000
tmp_table_size = 75M
Now i see my free RAM = 488mb left isnt this kinda to low?
sterin
November 2, 2007, 6:12am
8
You forgot increasing the sort_buffer_size.
[B]gozer wrote on Fri, 02 November 2007 10:22[/B]
Now i see my free RAM = 488mb left isnt this kinda to low?
No, it is a bit difficult to see but you are reading the output from top wrong.
All OS is using free RAM as a file cache to avoid reading/writing to the disks.A|B|… below:
[B]gozer wrote on Fri, 02 November 2007 10:22[/B]
Running with the new setting on the MYSQL.
top - 10:18:21 up 10:16, 1 user, load average: 0.00, 0.03, 0.00A 3897492k total, B 3408788k used, C 488704k free, 65144k buffersD 1227320k cached
2256 mysql 15 0 1921m 1.8g 5980 S 2 47.9 139:06.71 mysqld
So in this case C is reporting how much free memory you have and this is usually a very low figure because Linux tries to use all free RAM as OS file cache.
But since the OS file cache gives back memory if some other process needs it better your actual free memory should be calculated by:
So you are pretty safe.
I have to ask.
sterin
November 14, 2007, 1:45am
9
Depends on how large the result sets for your queries are.
But generally if you increase it to maybe 8M then you usually get a good result.
gozer
November 14, 2007, 3:19am
10
does every user get a sort buffer size of 8M then? i have something like 150 user connecting to the dbs.