MYSQL DB Optimizing/Tuning
Hi!
I�ve 364 Monitors running on my AM Server 7 Build 7410 professional edition, Load Faktor is 9,5 to 9,7.
I know, it�s too much for one server so i�m getting the Enterprise License in future, but it takes some time for the change.
But if i�m looking what AM servers most usage is, it�s the mysqld-nt.exe.
He works with 4 CPU (on VMware ESX) concurrent 80-90% cpu usage, but memory usage was very less with 400MB the whole server!
I�m not an mysqldb professional, but i guess there is some performance reserves in the default settings of the mysqldb!
The defaults in my.ini are
[mysqld]
set-variable = read_buffer=1M
set-variable = key_buffer=32M
set-variable = sort_buffer=4M
set-variable = myisam_sort_buffer_size=16M
set-variable = bulk_insert_buffer_size=16M
set-variable = table_cache=128
My server(Windows 2003 Sp2) have 4 CPU�s and 4 GB Memory.
Isn�t it better to extend the settings that mysqldb use to grab more of memory to saves Database read/write time from disk and finaly more cpu time?
Now i�ve edited the my.ini file to this settings and now mysqldb use 1,5GB Memory.
set-variable = read_buffer=16M
set-variable = key_buffer=1536M
set-variable = sort_buffer=64M
set-variable = myisam_sort_buffer_size=256M
set-variable = bulk_insert_buffer_size=256M
set-variable = table_cache=1536
I�ve also add this variable to use more of my cpu�s: (don�t know if it have any effect)
set-variable = thread_concurrency=8
Now CPU Usage is reduce to 70-80% concurrent i guess it�s quite better, but again, i�m not an mysqldb specialist.
So do anybody know more about this settings? Should i give him once again more memory to set table_cache=2048?
Is it good to screw on this settings or should i only use the defaults?
I attach the output of "show status" and the output of dir command in amdb\data folder to see the tablesize in MB, ascending down sort order.
The "eventuserprops.MYD" is the biggest one with 1GB size.
I�m glad about some inputs/answers/hints,
kind regards to anybody,
Alexander
I�ve 364 Monitors running on my AM Server 7 Build 7410 professional edition, Load Faktor is 9,5 to 9,7.
I know, it�s too much for one server so i�m getting the Enterprise License in future, but it takes some time for the change.
But if i�m looking what AM servers most usage is, it�s the mysqld-nt.exe.
He works with 4 CPU (on VMware ESX) concurrent 80-90% cpu usage, but memory usage was very less with 400MB the whole server!
I�m not an mysqldb professional, but i guess there is some performance reserves in the default settings of the mysqldb!
The defaults in my.ini are
[mysqld]
set-variable = read_buffer=1M
set-variable = key_buffer=32M
set-variable = sort_buffer=4M
set-variable = myisam_sort_buffer_size=16M
set-variable = bulk_insert_buffer_size=16M
set-variable = table_cache=128
My server(Windows 2003 Sp2) have 4 CPU�s and 4 GB Memory.
Isn�t it better to extend the settings that mysqldb use to grab more of memory to saves Database read/write time from disk and finaly more cpu time?
Now i�ve edited the my.ini file to this settings and now mysqldb use 1,5GB Memory.
set-variable = read_buffer=16M
set-variable = key_buffer=1536M
set-variable = sort_buffer=64M
set-variable = myisam_sort_buffer_size=256M
set-variable = bulk_insert_buffer_size=256M
set-variable = table_cache=1536
I�ve also add this variable to use more of my cpu�s: (don�t know if it have any effect)
set-variable = thread_concurrency=8
Now CPU Usage is reduce to 70-80% concurrent i guess it�s quite better, but again, i�m not an mysqldb specialist.
So do anybody know more about this settings? Should i give him once again more memory to set table_cache=2048?
Is it good to screw on this settings or should i only use the defaults?
I attach the output of "show status" and the output of dir command in amdb\data folder to see the tablesize in MB, ascending down sort order.
The "eventuserprops.MYD" is the biggest one with 1GB size.
I�m glad about some inputs/answers/hints,
kind regards to anybody,
Alexander