… used a 10GB key buffer. I used ALTER TABLE DISABLE KEYS and built the keys with sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld] datadir=/mnt…=2048 table_definition_cache=16384 innodb_flush_method=O_DIRECT key_buffer_size=10G # Disabling symbolic-links is recommended to prevent assorted security…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
… corresponding buffers are allocated only as big as needed (key_buffer_size). There are many examples of this. What about join_buffer_size? I saw a my.cnf with a 128M join_buffer_size the other…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… of having multiple key caches. But I’ll give the simple version here. In my opinion, you should choose a key_buffer_size that is…. Raise the key_buffer_size until, when the buffer is full, Key_reads/Uptime reduces to a number you’re comfortable with. Set key_buffer_size really big… authoritative — the “key cache hit ratio.” This is a shame. When you are new to MySQL, trying to configure my.cnf, and you…
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 13 Comments
… can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this…/mysql.sock bind-address=10.234.94.71 skip-locking key_buffer_size = 2000M max_allowed_packet = 32M # table_cache=20M # open-files… #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M #[mysqlhotcopy] interactive…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… InnoDB my.cnf [client] port=3306 socket=/tmp/mysql-inno.sock [mysqld] socket=/tmp/mysql-inno.sock default-storage-engine=INNODB innodb-buffer…-use-sys-stats-table innodb-write-io-threads=4 join-buffer-size=16M key-buffer-size=64M local-infile=on lock-wait-timeout=300 log-error=/var/log/mysqld-innodb.log max-allowed-packet=1M net-buffer…
Comment: MySQL: what read_buffer_size value is optimal ?
September 21, 2008 By 25 Comments
… the buffers to 128 kbyte? I put this in my my.cnf: key_buffer_size = 256M sort_buffer_size = 128K read_buffer_size = 128K read_rnd_buffer_size = 128K join_buffer_size = 128K myisam_sort_buffer_size… regarding to the my.cnf): key_buffer_size 268435456 sort_buffer_size 131064 read_buffer_size 126976 read_rnd_buffer_size 126976 join_buffer_size 126976 myisam_sort_buffer_size 131072 The key_buff shows the…
Comment: Solving RPM installation conflicts in CentOS 5 and CentOS 6
February 21, 2013 By 6 Comments
… /etc/my.cnf,i add wsrep_cluster_address=gcomm:// to fill it ,the mysql server will not running [root@mysql1 ~]# vi /etc/my.cnf… by reporting any bugs at http://bugs.percona.com/ key_buffer_size=0 read_buffer_size=131072 max_used_connections=0 max_threads=151 thread… It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 329809 K bytes of memory Hope that…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…: “It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections”. This was never true — not even when… set sort_buffer_size to 128K — I can quite well connect and say “set @@sort_buffer_size := 1 * 1024 * 1024 * 1024;” and my sort_buffer_size is…’m initially setting up a my.cnf for a server, I usually calculate the Big Ones like the buffer pool, query cache etc…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
… are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches… workload. P.S Note MySQL distribution contains bunch of sample my.cnf files which may be great templates to use. Typically they…
Comment: MySQL File System Fragmentation Benchmarks
July 9, 2008 By 28 Comments
…insert throughput as in this benchmark. Could you post the my.cnf file used and the computer hardware specs that produced …sort_file_size=100G myisam_max_extra_sort_file_size=100G myisam_sort_buffer_size=185M key_buffer_size=157M read_buffer_size=64K read_rnd_buffer_size=256K sort_buffer_size=256K…
