@Web Dev @Deorro +Everyone versant
Niko na shida hapa, I have a mysql db that is handling more 10 or more transactions per second…(I have some really huge analytics queries as well). From your experience what do you think can help me get better performance of this system? its all of a sudden slower after i added about a hundred new remote devices , each device does a handshake every 5 minutes, a couple of users reading from the db as well. what key changes should i look at changing in my MySQl configuration?
More RAM. Index. Cluster.
master->slave for read requests. Its a complicated thing. you do research about it on the net.
Stack overflow would be a better forum because database scaling is more complicated than server machine scaling because the thing deals with permanent records and dates.
distributing database system for write operations really needs an pro. It isnt something a regular website would do especially if you are dealing with money transactions.
Tough I think there are custom softwares for this if your system isnt very complicated
But a good specific detailed question on stack overflow will get you an answer.
Indexing your db may help though I have not done hard research on it.
However @Ingia also has a point. …but it is more of what you are willing to trade off and whether its really worth the effort
inakaa kila mtu apa kwaq hii kijiji ni mtu wa IT no wonder you idlers are always online
am coming back here after i worked and found a solution- this is just for record and to assist someone in future: before getting to the point of considering most of the above, a couple of things that can be changed that will see tremendeous difference are on cache sizes, max heap size, innodb buffer size etc… once these have been adjusted , you will be able to witness better perfomance.
Be more specific. What went down what went up
Below is my final my.cnf , working smoothly!
max_allowed_packet=1000M
max_connections=5000
slow_query_log=ON
query_cache_limit=1048576000
[B]query_cache_size=0
tmp_table_size=2048576000
max_heap_table_size=2088576000[/B]
general_log_file = /var/log/mysql/mysql.log
general_log = 0
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 60
log_queries_not_using_indexes = 1
log-queries-not-using-indexes
thread_cache_size=16
local_infile=0
[B]innodb_buffer_pool_size=2048576000
innodb_additional_mem_pool_size=1048576000
innodb_log_buffer_size=256576000
table_open_cache=256
join_buffer_size=268435456
key_buffer_size=2048576000
[/B]
The once in bold are critical ones.[/CODE]