# Advice pls my.cnf settings for my system

**URL:** <https://forums.percona.com/t/advice-pls-my-cnf-settings-for-my-system/479>\
**Category:** Other MySQL® Questions\
**Created:** [October 6, 2007, 2:40am UTC](https://forums.percona.com/t/advice-pls-my-cnf-settings-for-my-system/479 "2007-10-06T02:40:16Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![saddy](https://avatars.discourse-cdn.com/v4/letter/s/59ef9b/32.png) [@saddy](https://forums.percona.com/u/saddy)\
**Post date:** [October 6, 2007, 2:40am UTC](https://forums.percona.com/t/advice-pls-my-cnf-settings-for-my-system/479/1 "2007-10-06T02:40:16Z")

</div>

I have Core2Duo E6420 (dual core) + 4Gb RAM server. Mainly it used for two process - game server any mysql DB for game server. I don’t have much expirience in mysql so my mysql settings calls lags in game server. I tried several configs but cant understand what specifically influents on it. (but I saw difference in game with different settings).

Size of my DB in mysqldump’s file is about 260Mb. Half of tables is in INNODB, others in MyISAM. Game proccess always have 3 connections to mysql and several (not important) (2-20) from outside.

I’d like to mysql not have more than 300-500M RAM and not have 50% CPU…

Help please to tune config!

show status

| [B]Quote:[/B] |
| +-----------------------------------+------------+ | Variable\_name | Value | +-----------------------------------+------------+ | Aborted\_clients | 0 | | Aborted\_connects | 4 | | Binlog\_cache\_disk\_use | 0 | | Binlog\_cache\_use | 0 | | Bytes\_received | 115 | | Bytes\_sent | 180 | | Com\_admin\_commands | 0 | | Com\_alter\_db | 0 | | Com\_alter\_event | 0 | | Com\_alter\_table | 0 | | Com\_analyze | 0 | | Com\_backup\_table | 0 | | Com\_begin | 0 | | Com\_call\_procedure | 0 | | Com\_change\_db | 0 | | Com\_change\_master | 0 | | Com\_check | 0 | | Com\_checksum | 0 | | Com\_commit | 0 | | Com\_create\_db | 0 | | Com\_create\_event | 0 | | Com\_create\_function | 0 | | Com\_create\_index | 0 | | Com\_create\_table | 0 | | Com\_create\_user | 0 | | Com\_dealloc\_sql | 0 | | Com\_delete | 0 | | Com\_delete\_multi | 0 | | Com\_do | 0 | | Com\_drop\_db | 0 | | Com\_drop\_event | 0 | | Com\_drop\_function | 0 | | Com\_drop\_index | 0 | | Com\_drop\_table | 0 | | Com\_drop\_user | 0 | | Com\_execute\_sql | 0 | | Com\_flush | 0 | | Com\_grant | 0 | | Com\_ha\_close | 0 | | Com\_ha\_open | 0 | | Com\_ha\_read | 0 | | Com\_help | 0 | | Com\_insert | 0 | | Com\_insert\_select | 0 | | Com\_kill | 0 | | Com\_load | 0 | | Com\_load\_master\_data | 0 | | Com\_load\_master\_table | 0 | | Com\_lock\_tables | 0 | | Com\_optimize | 0 | | Com\_preload\_keys | 0 | | Com\_prepare\_sql | 0 | | Com\_purge | 0 | | Com\_purge\_before\_date | 0 | | Com\_rename\_table | 0 | | Com\_repair | 0 | | Com\_replace | 0 | | Com\_replace\_select | 0 | | Com\_reset | 0 | | Com\_restore\_table | 0 | | Com\_revoke | 0 | | Com\_revoke\_all | 0 | | Com\_rollback | 0 | | Com\_savepoint | 0 | | Com\_select | 1 | | Com\_set\_option | 0 | | Com\_show\_binlog\_events | 0 | | Com\_show\_binlogs | 0 | | Com\_show\_charsets | 0 | | Com\_show\_collations | 0 | | Com\_show\_column\_types | 0 | | Com\_show\_create\_db | 0 | | Com\_show\_create\_event | 0 | | Com\_show\_create\_table | 0 | | Com\_show\_databases | 0 | | Com\_show\_engine\_logs | 0 | | Com\_show\_engine\_mutex | 0 | | Com\_show\_engine\_status | 0 | | Com\_show\_errors | 0 | | Com\_show\_events | 0 | | Com\_show\_fields | 0 | | Com\_show\_grants | 0 | | Com\_show\_keys | 0 | | Com\_show\_master\_status | 0 | | Com\_show\_new\_master | 0 | | Com\_show\_open\_tables | 0 | | Com\_show\_plugins | 0 | | Com\_show\_privileges | 0 | | Com\_show\_processlist | 0 | | Com\_show\_slave\_hosts | 0 | | Com\_show\_slave\_status | 0 | | Com\_show\_status | 1 | | Com\_show\_storage\_engines | 0 | | Com\_show\_tables | 0 | | Com\_show\_triggers | 0 | | Com\_show\_variables | 0 | | Com\_show\_warnings | 0 | | Com\_slave\_start | 0 | | Com\_slave\_stop | 0 | | Com\_stmt\_close | 0 | | Com\_stmt\_execute | 0 | | Com\_stmt\_fetch | 0 | | Com\_stmt\_prepare | 0 | | Com\_stmt\_reset | 0 | | Com\_stmt\_send\_long\_data | 0 | | Com\_truncate | 0 | | Com\_unlock\_tables | 0 | | Com\_update | 0 | | Com\_update\_multi | 0 | | Com\_xa\_commit | 0 | | Com\_xa\_end | 0 | | Com\_xa\_prepare | 0 | | Com\_xa\_recover | 0 | | Com\_xa\_rollback | 0 | | Com\_xa\_start | 0 | | Compression | OFF | | Connections | 126 | | Created\_tmp\_disk\_tables | 0 | | Created\_tmp\_files | 5 | | Created\_tmp\_tables | 0 | | Delayed\_errors | 0 | | Delayed\_insert\_threads | 0 | | Delayed\_writes | 0 | | Flush\_commands | 1 | | Handler\_commit | 0 | | Handler\_delete | 0 | | Handler\_discover | 0 | | Handler\_prepare | 0 | | Handler\_read\_first | 0 | | Handler\_read\_key | 0 | | Handler\_read\_next | 0 | | Handler\_read\_prev | 0 | | Handler\_read\_rnd | 0 | | Handler\_read\_rnd\_next | 0 | | Handler\_rollback | 0 | | Handler\_savepoint | 0 | | Handler\_savepoint\_rollback | 0 | | Handler\_update | 0 | | Handler\_write | 0 | | Innodb\_buffer\_pool\_pages\_data | 12903 | | Innodb\_buffer\_pool\_pages\_dirty | 88 | | Innodb\_buffer\_pool\_pages\_flushed | 110123 | | Innodb\_buffer\_pool\_pages\_free | 11395 | | Innodb\_buffer\_pool\_pages\_latched | 0 | | Innodb\_buffer\_pool\_pages\_misc | 278 | | Innodb\_buffer\_pool\_pages\_total | 24576 | | Innodb\_buffer\_pool\_read\_ahead\_rnd | 1 | | Innodb\_buffer\_pool\_read\_ahead\_seq | 157 | | Innodb\_buffer\_pool\_read\_requests | 66909947 | | Innodb\_buffer\_pool\_reads | 6447 | | Innodb\_buffer\_pool\_wait\_free | 0 | | Innodb\_buffer\_pool\_write\_requests | 1288893 | | Innodb\_data\_fsyncs | 26630 | | Innodb\_data\_pending\_fsyncs | 0 | | Innodb\_data\_pending\_reads | 0 | | Innodb\_data\_pending\_writes | 0 | | Innodb\_data\_read | 210063360 | | Innodb\_data\_reads | 7486 | | Innodb\_data\_writes | 95299 | | Innodb\_data\_written | 3742815744 | | Innodb\_dblwr\_pages\_written | 110123 | | Innodb\_dblwr\_writes | 2362 | | Innodb\_log\_waits | 0 | | Innodb\_log\_write\_requests | 285121 | | Innodb\_log\_writes | 6218 | | Innodb\_os\_log\_fsyncs | 7599 | | Innodb\_os\_log\_pending\_fsyncs | 0 | | Innodb\_os\_log\_pending\_writes | 0 | | Innodb\_os\_log\_written | 133597696 | | Innodb\_page\_size | 16384 | | Innodb\_pages\_created | 215 | | Innodb\_pages\_read | 12688 | | Innodb\_pages\_written | 110123 | | Innodb\_row\_lock\_current\_waits | 0 | | Innodb\_row\_lock\_time | 0 | | Innodb\_row\_lock\_time\_avg | 0 | | Innodb\_row\_lock\_time\_max | 0 | | Innodb\_row\_lock\_waits | 0 | | Innodb\_rows\_deleted | 180898 | | Innodb\_rows\_inserted | 179196 | | Innodb\_rows\_read | 299218310 | | Innodb\_rows\_updated | 38186 | | Key\_blocks\_not\_flushed | 0 | | Key\_blocks\_unused | 213133 | | Key\_blocks\_used | 1209 | | Key\_read\_requests | 350256 | | Key\_reads | 1209 | | Key\_write\_requests | 17726 | | Key\_writes | 15910 | | Last\_query\_cost | 0.000000 | | Max\_used\_connections | 7 | | Not\_flushed\_delayed\_rows | 0 | | Open\_files | 166 | | Open\_streams | 0 | | Open\_table\_definitions | 123 | | Open\_tables | 118 | | Opened\_files | 316 | | Opened\_tables | 0 | | Prepared\_stmt\_count | 0 | | Qcache\_free\_blocks | 706 | | Qcache\_free\_memory | 5104056 | | Qcache\_hits | 34134 | | Qcache\_inserts | 360250 | | Qcache\_lowmem\_prunes | 0 | | Qcache\_not\_cached | 77776 | | Qcache\_queries\_in\_cache | 3682 | | Qcache\_total\_blocks | 8158 | | Questions | 797430 | | Rpl\_status | NULL | | Select\_full\_join | 0 | | Select\_full\_range\_join | 0 | | Select\_range | 0 | | Select\_range\_check | 0 | | Select\_scan | 0 | | Slave\_open\_temp\_tables | 0 | | Slave\_retried\_transactions | 0 | | Slave\_running | OFF | | Slow\_launch\_threads | 0 | | Slow\_queries | 0 | | Sort\_merge\_passes | 0 | | Sort\_range | 0 | | Sort\_rows | 0 | | Sort\_scan | 0 | | Table\_locks\_immediate | 744811 | | Table\_locks\_waited | 626 | | Tc\_log\_max\_pages\_used | 0 | | Tc\_log\_page\_size | 0 | | Tc\_log\_page\_waits | 0 | | Threads\_cached | 1 | | Threads\_connected | 6 | | Threads\_created | 7 | | Threads\_running | 1 | | Uptime | 11994 | +-----------------------------------+------------+ |

show variables

| [B]Quote:[/B] |
| 

±--------------------------------±------------------------ ----------+  
| Variable\_name | Value |  
±--------------------------------±------------------------ ----------+  
| auto\_increment\_increment | 1 |  
| auto\_increment\_offset | 1 |  
| autocommit | ON |  
| automatic\_sp\_privileges | ON |  
| back\_log | 50 |  
| basedir | /usr/local/ |  
| big\_tables | OFF |  
| binlog\_cache\_size | 32768 |  
| binlog\_format | MIXED |  
| bulk\_insert\_buffer\_size | 8388608 |  
| character\_set\_client | cp1251 |  
| character\_set\_connection | cp1251 |  
| character\_set\_database | utf8 |  
| character\_set\_filesystem | binary |  
| character\_set\_results | cp1251 |  
| character\_set\_server | utf8 |  
| character\_set\_system | utf8 |  
| character\_sets\_dir | /usr/local/share/mysql/charsets/ |  
| collation\_connection | cp1251\_bin |  
| collation\_database | utf8\_general\_ci |  
| collation\_server | utf8\_general\_ci |  
| completion\_type | 0 |  
| concurrent\_insert | 1 |  
| connect\_timeout | 5 |  
| datadir | /var/db/mysql/ |  
| date\_format | %Y-%m-%d |  
| datetime\_format | %Y-%m-%d %H:%i:%s |  
| default\_week\_format | 0 |  
| delay\_key\_write | OFF |  
| delayed\_insert\_limit | 100 |  
| delayed\_insert\_timeout | 300 |  
| delayed\_queue\_size | 1000 |  
| div\_precision\_increment | 4 |  
| engine\_condition\_pushdown | ON |  
| error\_count | 0 |  
| event\_scheduler | OFF |  
| expire\_logs\_days | 0 |  
| flush | OFF |  
| flush\_time | 0 |  
| foreign\_key\_checks | ON |  
| ft\_boolean\_syntax | + -\>\<()~\*:“”&| |  
| ft\_max\_word\_len | 84 |  
| ft\_min\_word\_len | 4 |  
| ft\_query\_expansion\_limit | 20 |  
| ft\_stopword\_file | (built-in) |  
| general\_log | OFF |  
| general\_log\_file | /var/db/mysql/myhost.log |  
| group\_concat\_max\_len | 1024 |  
| have\_compress | YES |  
| have\_crypt | YES |  
| have\_csv | YES |  
| have\_dynamic\_loading | NO |  
| have\_geometry | YES |  
| have\_innodb | YES |  
| have\_ndbcluster | NO |  
| have\_openssl | NO |  
| have\_partitioning | YES |  
| have\_query\_cache | YES |  
| have\_rtree\_keys | YES |  
| have\_ssl | NO |  
| have\_symlink | YES |  
| hostname | myhost |  
| identity | 0 |  
| init\_connect | |  
| init\_file | |  
| init\_slave | |  
| innodb\_additional\_mem\_pool\_size | 20971520 |  
| innodb\_autoextend\_increment | 8 |  
| innodb\_buffer\_pool\_size | 402653184 |  
| innodb\_checksums | ON |  
| innodb\_commit\_concurrency | 0 |  
| innodb\_concurrency\_tickets | 500 |  
| innodb\_data\_file\_path | ibdata1:400M:autoextend |  
| innodb\_data\_home\_dir | /var/db/mysql\_innodb/ |  
| innodb\_doublewrite | ON |  
| innodb\_fast\_shutdown | 1 |  
| innodb\_file\_io\_threads | 4 |  
| innodb\_file\_per\_table | ON |  
| innodb\_flush\_log\_at\_trx\_commit | 0 |  
| innodb\_flush\_method | O\_DIRECT |  
| innodb\_force\_recovery | 0 |  
| innodb\_lock\_wait\_timeout | 50 |  
| innodb\_locks\_unsafe\_for\_binlog | OFF |  
| innodb\_log\_buffer\_size | 6291456 |  
| innodb\_log\_file\_size | 104857600 |  
| innodb\_log\_files\_in\_group | 2 |  
| innodb\_log\_group\_home\_dir | /var/db/mysql\_innodb/ |  
| innodb\_max\_dirty\_pages\_pct | 90 |  
| innodb\_max\_purge\_lag | 0 |  
| innodb\_mirrored\_log\_groups | 1 |  
| innodb\_open\_files | 300 |  
| innodb\_rollback\_on\_timeout | OFF |  
| innodb\_support\_xa | ON |  
| innodb\_sync\_spin\_loops | 20 |  
| innodb\_table\_locks | ON |  
| innodb\_thread\_concurrency | 8 |  
| innodb\_thread\_sleep\_delay | 10000 |  
| insert\_id | 0 |  
| interactive\_timeout | 28800 |  
| join\_buffer\_size | 268431360 |  
| keep\_files\_on\_create | OFF |  
| key\_buffer\_size | 268435456 |  
| key\_cache\_age\_threshold | 300 |  
| key\_cache\_block\_size | 1024 |  
| key\_cache\_division\_limit | 100 |  
| language | /usr/local/share/mysql/english/ |  
| large\_files\_support | ON |  
| large\_page\_size | 0 |  
| large\_pages | OFF |  
| last\_insert\_id | 0 |  
| lc\_time\_names | en\_US |  
| license | GPL |  
| local\_infile | ON |  
| locked\_in\_memory | OFF |  
| log | OFF |  
| log\_bin | OFF |  
| log\_bin\_trust\_function\_creators | OFF |  
| log\_bin\_trust\_routine\_creators | OFF |  
| log\_error | /var/db/mysql/mysql-err.log |  
| log\_output | FILE |  
| log\_queries\_not\_using\_indexes | OFF |  
| log\_slave\_updates | OFF |  
| log\_slow\_queries | ON |  
| log\_warnings | 1 |  
| long\_query\_time | 3.000000 |  
| low\_priority\_updates | OFF |  
| lower\_case\_file\_system | OFF |  
| lower\_case\_table\_names | 0 |  
| max\_allowed\_packet | 33553408 |  
| max\_binlog\_cache\_size | 18446744073709551615 |  
| max\_binlog\_size | 1073741824 |  
| max\_connect\_errors | 999999 |  
| max\_connections | 40 |  
| max\_delayed\_threads | 20 |  
| max\_error\_count | 64 |  
| max\_heap\_table\_size | 536870912 |  
| max\_insert\_delayed\_threads | 20 |  
| max\_join\_size | 536870912 |  
| max\_length\_for\_sort\_data | 1024 |  
| max\_prepared\_stmt\_count | 16382 |  
| max\_relay\_log\_size | 0 |  
| max\_seeks\_for\_key | 18446744073709551615 |  
| max\_sort\_length | 1024 |  
| max\_sp\_recursion\_depth | 0 |  
| max\_tmp\_tables | 32 |  
| max\_user\_connections | 0 |  
| max\_write\_lock\_count | 18446744073709551615 |  
| min\_examined\_row\_limit | 0 |  
| multi\_range\_count | 256 |  
| myisam\_data\_pointer\_size | 6 |  
| myisam\_max\_sort\_file\_size | 9223372036854775807 |  
| myisam\_recover\_options | OFF |  
| myisam\_repair\_threads | 1 |  
| myisam\_sort\_buffer\_size | 8388608 |  
| myisam\_stats\_method | nulls\_unequal |  
| myisam\_use\_mmap | OFF |  
| net\_buffer\_length | 16384 |  
| net\_read\_timeout | 30 |  
| net\_retry\_count | 1000000 |  
| net\_write\_timeout | 60 |  
| new | OFF |  
| old | OFF |  
| old\_alter\_table | OFF |  
| old\_passwords | OFF |  
| open\_files\_limit | 11095 |  
| optimizer\_prune\_level | 1 |  
| optimizer\_search\_depth | 62 |  
| pid\_file | /var/db/mysql/myhost.pid |  
| plugin\_dir | /usr/local/lib/mysql |  
| port | 3306 |  
| preload\_buffer\_size | 32768 |  
| protocol\_version | 10 |  
| pseudo\_thread\_id | 124 |  
| query\_alloc\_block\_size | 8192 |  
| query\_cache\_limit | 4194304 |  
| query\_cache\_min\_res\_unit | 4096 |  
| query\_cache\_size | 23068672 |  
| query\_cache\_type | ON |  
| query\_cache\_wlock\_invalidate | OFF |  
| query\_prealloc\_size | 8192 |  
| rand\_seed1 | |  
| rand\_seed2 | |  
| range\_alloc\_block\_size | 2048 |  
| read\_buffer\_size | 4190208 |  
| read\_only | OFF |  
| read\_rnd\_buffer\_size | 4190208 |  
| relay\_log\_purge | ON |  
| relay\_log\_space\_limit | 0 |  
| rpl\_recovery\_rank | 0 |  
| secure\_auth | OFF |  
| secure\_file\_priv | |  
| server\_id | 1 |  
| skip\_external\_locking | ON |  
| skip\_networking | OFF |  
| skip\_show\_database | OFF |  
| slave\_compressed\_protocol | OFF |  
| slave\_load\_tmpdir | /var/tmp/ |  
| slave\_net\_timeout | 3600 |  
| slave\_skip\_errors | OFF |  
| slave\_transaction\_retries | 10 |  
| slow\_launch\_time | 2 |  
| slow\_query\_log | ON |  
| slow\_query\_log\_file | /var/db/mysql/mysql-slow.log |  
| socket | /tmp/mysql.sock |  
| sort\_buffer\_size | 4194296 |  
| sql\_auto\_is\_null | ON |  
| sql\_big\_selects | OFF |  
| sql\_big\_tables | OFF |  
| sql\_buffer\_result | OFF |  
| sql\_log\_bin | ON |  
| sql\_log\_off | OFF |  
| sql\_log\_update | ON |  
| sql\_low\_priority\_updates | OFF |  
| sql\_max\_join\_size | 536870912 |  
| sql\_mode | |  
| sql\_notes | ON |  
| sql\_quote\_show\_create | ON |  
| sql\_safe\_updates | OFF |  
| sql\_select\_limit | 18446744073709551615 |  
| sql\_slave\_skip\_counter | |  
| sql\_warnings | OFF |  
| ssl\_ca | |  
| ssl\_capath | |  
| ssl\_cert | |  
| ssl\_cipher | |  
| ssl\_key | |  
| storage\_engine | InnoDB |  
| sync\_binlog | 0 |  
| sync\_frm | ON |  
| system\_time\_zone | MSD |  
| table\_definition\_cache | 128 |  
| table\_lock\_wait\_timeout | 50 |  
| table\_open\_cache | 1024 |  
| table\_type | InnoDB |  
| thread\_cache\_size | 64 |  
| thread\_handling | one-thread-per-connection |  
| thread\_stack | 131072 |  
| time\_format | %H:%i:%s |  
| time\_zone | SYSTEM |  
| timed\_mutexes | OFF |  
| timestamp | 1191657738 |  
| tmp\_table\_size | 536870912 |  
| tmpdir | /var/tmp/ |  
| transaction\_alloc\_block\_size | 8192 |  
| transaction\_prealloc\_size | 4096 |  
| tx\_isolation | REPEATABLE-READ |  
| unique\_checks | ON |  
| updatable\_views\_with\_limit | YES |  
| version | 5.1.21-beta-log |  
| version\_comment | FreeBSD port: mysql-server-5.1.21 |  
| version\_compile\_machine | amd64 |  
| version\_compile\_os | portbld-freebsd6.2 |  
| wait\_timeout | 28800 |  
| warning\_count | 0 |  
±--------------------------------±------------------------ ----------+

 |

mysqlreport

| [B]Quote:[/B] |
| MySQL 5.1.21-beta-log uptime 0 2:54:39 Sat Oct 6 11:28:15 2007

\_\_ Key \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_\_\_\_  
Buffer used 1.13M of 256.00M %Used: 0.44  
Current 47.81M %Usage: 18.68  
Write hit 11.33%  
Read hit 99.66%

\_\_ Questions \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
Total 680.74k 65.0/s  
DMS 637.30k 60.8/s %Total: 93.62  
QC Hits 26.75k 2.6/s 3.93  
Com\_ 16.60k 1.6/s 2.44  
COM\_QUIT 92 0.0/s 0.01  
-Unknown 5 0.0/s 0.00  
Slow 0 0/s 0.00 %DMS: 0.00  
DMS 637.30k 60.8/s 93.62  
SELECT 367.03k 35.0/s 53.92 57.59  
INSERT 163.87k 15.6/s 24.07 25.71  
DELETE 74.45k 7.1/s 10.94 11.68  
UPDATE 31.95k 3.0/s 4.69 5.01  
REPLACE 5 0.0/s 0.00 0.00  
Com\_ 16.60k 1.6/s 2.44  
commit 8.24k 0.8/s 1.21  
begin 8.24k 0.8/s 1.21  
change\_db 86 0.0/s 0.01

\_\_ SELECT and Sort \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
Scan 3.70k 0.4/s %SELECT: 1.01  
Range 6 0.0/s 0.00  
Full join 0 0/s 0.00  
Range check 0 0/s 0.00  
Full rng join 0 0/s 0.00  
Sort scan 501 0.0/s  
Sort range 1 0.0/s  
Sort mrg pass 0 0/s

\_\_ Query Cache \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
Memory usage 16.73M of 22.00M %Used: 76.05  
Block Fragmnt 9.70%  
Hits 26.75k 2.6/s  
Inserts 304.52k 29.1/s  
Insrt:Prune 304.52k:1 29.1/s  
Hit:Insert 0.09:1

\_\_ Table Locks \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
Waited 497 0.0/s %Total: 0.08  
Immediate 638.24k 60.9/s

\_\_ Tables \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_  
Open 118 of 1024 %Cache: 11.52  
Opened 124 0.0/s

\_\_ Connections \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
Max used 4 of 40 %Max: 10.00  
Total 94 0.0/s

\_\_ Created Temp \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
Disk table 0 0/s  
Table 10 0.0/s  
File 5 0.0/s

\_\_ Threads \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_  
Running 1 of 4  
Cached 0 of 64 %Hit: 95.74  
Created 4 0.0/s  
Slow 0 0/s

\_\_ Aborted \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_  
Clients 0 0/s  
Connects 2 0.0/s

\_\_ Bytes \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_\_  
Sent 193.31M 18.4k/s  
Received 97.55M 9.3k/s

\_\_ InnoDB Buffer Pool \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
Usage 202.38M of 384.00M %Used: 52.70  
Read ratio 0.000  
Pages  
Free 11.62k %Total: 47.30  
Data 12.70k 51.68 %Drty: 0.39  
Misc 250 1.02  
Latched 0 0.00  
Reads 56.10M 5.4k/s  
From file 6.27k 0.6/s 0.01  
Ahead Rnd 1 0.0/s  
Ahead Sql 157 0.0/s  
Writes 1.13M 108.3/s  
Flushes 95.63k 9.1/s  
Wait Free 0 0/s

\_\_ InnoDB Lock \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
Waits 0 0/s  
Current 0  
Time acquiring  
Total 0 ms  
Average 0 ms  
Max 0 ms

\_\_ InnoDB Data, Pages, Rows \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_  
Data  
Reads 7.31k 0.7/s  
Writes 82.66k 7.9/s  
fsync 23.24k 2.2/s  
Pending  
Reads 0  
Writes 0  
fsync 0

Pages  
Created 190 0.0/s  
Read 12.51k 1.2/s  
Written 95.63k 9.1/s

Rows  
Deleted 160.50k 15.3/s  
Inserted 158.81k 15.2/s  
Read 251.32M 24.0k/s  
Updated 34.41k 3.3/s

 |

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [October 6, 2007, 4:07am UTC](https://forums.percona.com/t/advice-pls-my-cnf-settings-for-my-system/479/2 "2007-10-06T04:07:50Z")

</div>

| [B]Quote:[/B] |
| 

I’d like to mysql not have more than 300-500M RAM and not have 50% CPU…

 |

What I can see from your posting it doesn't look like MySQL is using more RAM than that. But the reason for this is that your DB is in fact so small that it doesn't have to. The main variables for memory usage are usually: innodb\_buffer\_pool\_size key\_buffer\_pool\_size

Then you have per connection sizes:  
sort\_buffer\_size  
join\_buffer\_size  
( which in your case set to a really strange large value which indicates on a poor design of DB)

As for the CPU there is no solution, because the DB will consume 100% CPU in each thread if it needs to.  
BUT if the DB consumes 100% CPU it usually always indicate a poorly designed DB with lack of indexes etc.

Optimization of DB and application is the way out instead of trying to limit the resources that MySQL needs.

---

<div class="post-metadata">

**Author:** ![saddy](https://avatars.discourse-cdn.com/v4/letter/s/59ef9b/32.png) [@saddy](https://forums.percona.com/u/saddy)\
**Post date:** [October 6, 2007, 6:11am UTC](https://forums.percona.com/t/advice-pls-my-cnf-settings-for-my-system/479/3 "2007-10-06T06:11:01Z")

</div>

Let’s me add some info.  
First, I can’t modify DB structure, second - I moved from Win2003 to FreeBSD and got mysql troubles. Earlier, on Win2003, I have no problem with DB.

So I need settings max optimal to work with this database…  
Can anybody advice specifically values?