Jumping back in (late) in this thread... On Mon, 2012-04-09 at 19:51 +0100, Bob Proulx wrote:
> J. Bakshi wrote: > > Karl E. Jorgensen wrote: > > > J. Bakshi wrote: > > > > I have been provided a muscular linux server to use as a Mysql server > > > > in our organization. The server is located just beside the web server > > > > and within the same network. This dedicated server has 8GB RAM, i5 > > > > processors > > > > and running mysql as service. No apache, php ..... nothing. All > > > > resources are > > > > dedicated to mysql only. > > That does sound big and muscular and should do a good job of providing > database services. > > > > > The BIG hitch is; when we connect with this box the web sites become > > > > too slow. > > Unfortunately subjective descriptions such as "too slow" are not > useful descriptions. One person's very fast is another person's too > slow. Objective benchmark data is needed in order to make forward > progress. Also when you change something to improve the performance > if you don't know how much you changed things you might actually make > something worse without knowing it. > > The other suggestions that people gave you were good. They were > better than anything I could suggest about mysql specifically. But > for performance tuning in general I strongly recommend that you use or > create a benchmark that illustrates the type of operation you are > trying to optimize. Benchmarks are best when they can be shared with > other people so that they can recreate your environment. It might be > useful to create a benchmark using synthetic data (created fake data) > so that others can share your environment and recreate it. > > Then collect data on that benchmark. Then make performance tuning > changes. Then run the benchmarkmark again and determine if your > change improved things and if so by how much. > > > > > The sql connection becomes little faster but still it is considerably > > > > slow; specially with such a muscular dedicated linx box just for Mysql. > > > > Is there anything else which I can add/configure to make the network > > > > latecy > > > > small or any such mechanism to make the query fast ? > > Please show us data that tells us how slow is slow and how fast is > fast. > > I am not a mysql performance expert. I won't be able to help too > much. Sorry. But I can tell you that if you don't have actual data > on the existing performance then you also won't know if you have > improved it or if you have made it worse or if you haven't changed > anything. Benchmarking when performance tuning is critically > important. > > > # free -m > > total used free shared buffers cached > > Mem: 7986 7913 73 0 224 6133 > > -/+ buffers/cache: 1554 6431 > > Swap: 3813 0 3813 > > Shows 8G of ram. Good. Shows no swap used. Also good. (But not > necessarily bad if some swap is used. So if you see some swap being > used that isn't necessarily a problem.) Mostly good. Yes. But 6133 Mb Cached is not what I would expect - this indicates that the linux kernel is doing the caching - rather than MySQL. And the MySQL cache for this stuff is (almost) always more efficient. If you use InnoDB tables, you can increase innodb_buffer_pool to e.g. 4Gb more. If are using MyISAM tables, increase key_buffer_size (affects caching of indexes) and probably others - I don't use MyISAM tables much... > > > # vmstat 5 10 > > procs -----------memory---------- ---swap-- -----io---- -system-- > > ----cpu---- > > r b swpd free buff cache si so bi bo in cs us sy id > > wa > > 0 0 0 56328 230440 6299676 0 0 25 74 6 6 0 0 98 > > 2 > > 0 0 0 55700 230440 6299744 0 0 0 22 226 272 0 0 99 > > 1 > > 0 0 0 55964 230440 6299856 0 0 0 314 348 388 0 0 94 > > 5 > > 0 0 0 55452 230440 6299956 0 0 0 61 304 364 0 0 97 > > 2 > > 0 1 0 55592 230440 6300424 0 0 0 271 199 257 0 0 96 > > 4 > > 0 0 0 54584 230440 6300908 0 0 0 338 342 428 0 0 92 > > 8 > > 0 0 0 54800 230440 6301072 0 0 0 77 119 133 0 0 98 > > 2 > > 0 0 0 53964 230440 6301532 0 0 0 617 267 327 0 0 95 > > 4 > > 0 0 0 54468 230440 6301544 0 0 0 296 119 116 0 0 99 > > 0 > > 0 0 0 54212 230440 6301648 0 0 0 183 361 435 0 0 95 > > 4 > > The 'si' and 'so' fields show zero swap-in / swap-out rate. That is > good. A very low rate of 1 or 2 also would not be a problem. When > machines start to swap excessively you will see rate numbers like 10 > or 20 in that field and that would be a bad indication. > > Bob
