Hello,
Dear I'm facing an issue with MYSQL optimization .
I'm running an SQL Server to process small queries
Server is
24 Proc / 24 GB Ram / NAS + Raid 10 / SSD
Server connections each 3 mins get to 2000 connection currently and will increase very soon.
MYSQL is closing connections perfectly
load average: 62.41, 50.75, 43.41
Free-m
The queries i run are small
for example
update number x - ( sent-not sent )
that is it, the queries won't take more than 64K tops
I'm trying to switch the load from CPU to Ram
My current configuration are
I'm looking to maximize the connections to get more than 10K each 3 mins without facing such loads .
my tables are in InnoDB it's only 2 tables .
Any help is really needed here .
Regards
Dear I'm facing an issue with MYSQL optimization .
I'm running an SQL Server to process small queries
Server is
24 Proc / 24 GB Ram / NAS + Raid 10 / SSD
Server connections each 3 mins get to 2000 connection currently and will increase very soon.
MYSQL is closing connections perfectly
load average: 62.41, 50.75, 43.41
Free-m
total used free shared buffers cached
Mem: 24028 6468 17559 0 235 3553
The queries i run are small
for example
update number x - ( sent-not sent )
that is it, the queries won't take more than 64K tops
I'm trying to switch the load from CPU to Ram
My current configuration are
[mysqld]
long_query_time = 1
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time=0.1
max_connections = 3000
max_user_connections= 2850
key_buffer_size = 2G
myisam_sort_buffer_size = 512M
myisam_max_sort_file_size = 1G
join_buffer_size = 256K
wait_timeout = 10
interactive_timeout = 10
connect_timeout = 15
table_cache = 1
tmp_table_size = 1024M
thread_cache_size = 1
max_heap_table_size = 2048M
table_open_cache = 102400
net_buffer_length = 4096
max_connect_errors = 1000
#thread_concurrency = 2
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 2M
query_cache_limit = 1048576
query_cache_size = 8G
query_cache_type = 1
query_prealloc_size = 1M
query_alloc_block_size = 1M
range_alloc_block_size = 4096
transaction_alloc_block_size = 1024
transaction_prealloc_size = 1024
default-storage-engine = MyISAM
max_write_lock_count = 64
innodb_buffer_pool_size = 2G
innodb_log_buffer_size= 1024M
innodb_flush_log_at_trx_commit=2
skip_name_resolve
slave_net_timeout = 90
delayed_insert_timeout = 120
innodb_flush_method = O_DIRECT
[mysqld_safe]
nice = -10
open_files_limit = 10000
[mysqldump]
quick
max_allowed_packet = 1M
[myisamchk]
sort_buffer_size = 256K
read_buffer_size = 256K
write_buffer_size = 256K
[mysqlhotcopy]
local-infile=0
I'm looking to maximize the connections to get more than 10K each 3 mins without facing such loads .
my tables are in InnoDB it's only 2 tables .
Any help is really needed here .
Regards