hot to set /etc/my.cnf for this server

Portal Home > Knowledgebase > Articles Database > hot to set /etc/my.cnf for this server

Posted by ezak, 10-07-2008, 10:22 AM
I have a high load on SQL and I want to set a good rules for my.cnf Processor Information Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel(R) Core(TM)2 Quad CPU Q9300 @ 2.50GHz Processor #1 speed: 2000.000 MHz Processor #1 cache size: 3072 KB Processor #2 Vendor: GenuineIntel Processor #2 Name: Intel(R) Core(TM)2 Quad CPU Q9300 @ 2.50GHz Processor #2 speed: 2000.000 MHz Processor #2 cache size: 3072 KB Processor #3 Vendor: GenuineIntel Processor #3 Name: Intel(R) Core(TM)2 Quad CPU Q9300 @ 2.50GHz Processor #3 speed: 2000.000 MHz Processor #3 cache size: 3072 KB Processor #4 Vendor: GenuineIntel Processor #4 Name: Intel(R) Core(TM)2 Quad CPU Q9300 @ 2.50GHz Processor #4 speed: 2000.000 MHz Processor #4 cache size: 3072 KB Memory Information Memory for crash kernel (0x0 to 0x0) notwithin permissible range Memory: 8297204k/9175040k available (2093k kernel code, 79720k reserved, 872k data, 228k init, 7460736k highmem) System Information Linux server.rock2host.com 2.6.18-92.1.13.el5PAE #1 SMP Wed Sep 24 20:07:49 EDT 2008 i686 i686 i386 GNU/Linux Physical Disks SCSI device sda: 1465147055 512-byte hdwr sectors (750155 MB) sda: Write Protect is off sda: Mode Sense: 00 3a 00 00 SCSI device sda: drive cache: write back SCSI device sda: 1465147055 512-byte hdwr sectors (750155 MB) sda: Write Protect is off sda: Mode Sense: 00 3a 00 00 SCSI device sda: drive cache: write back sd 0:0:0:0: Attached scsi disk sda sd 0:0:0:0: Attached scsi generic sg0 type 0 Current Memory Usage total used free shared buffers cached Mem: 8301400 7984756 316644 0 308676 5470164 -/+ buffers/cache: 2205916 6095484 Swap: 2031608 68 2031540 Total: 10333008 7984824 2348184 Current Disk Usage Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 675G 196G 445G 31% / /dev/sda1 99M 18M 77M 19% /boot tmpfs 4.0G 0 4.0G 0% /dev/shm /usr/tmpDSK 2.0G 73M 1.8G 4% /tmp
Posted by dvashishtha, 10-07-2008, 02:42 PM
Apart from optimizing mysql,you need to look in to hardware part as well. Try to collect some data when you see load on the server like memory status, Disk I/O (this is very critical on mysql sever).Also enable long-queies logs to see what exactly is going on. What kind of queries (select or insert/update)are mostly being used in mysql? Also if you could attach your current my.cnf, this will also help in it's optimization.
Posted by ServerSurgeon Doru, 10-07-2008, 03:27 PM
Basically a good and tuned up configuration of my.cnf you can see in my-large.cnf or my-huge.cnf. Try to replace the current one with one of them, remove the log-bin setting, and put the max_connections=1000 and let us know if the issue persists.
Posted by ezak, 10-08-2008, 06:21 AM
there is no my-large.cnf or my-huge.cnf and my current my.cnf [mysqld] set-variable = max_connections=500 safe-show-database how to optimize this setting when I click on CPU/Memory/MySQL Usage in whm give me this bar in red User Domain %CPU %MEM MySQL Processes mysql 50.66 1.40 0.0
Posted by arbet, 10-08-2008, 06:57 AM
You will have to create the log files, here is a step by step tutorial on how to do identify slow queries. Do not depend on WHM for the CPU/Memory/MySQL usage, use top instead.

Add to Favourites Print this Article