I need to optimize mysql server to manage a big tables (now about 78Mb, with …] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients….2. Mysql ver. 5.0.45 via socket on x86_64 centos 5.2, 1 CPU 4core with 4 Gb RAM, 3Tb…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… is rather old box with 8GB of RAM and 4*SATA disks in RAID10. I …following in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row ***************************… ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable expand_…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… available RAM. I would guess that the number varied more, depending on the availability of RAM? On a dedicated server for Mysql you…, backup, cron, syslog, etc). But the ratio of RAM used for these non-mysql processes drops rapidly when you increase the amount… any table / index fragmentation, without having to run the OPTIMIZE? HT: Since OPTIMIZE rebuilds the whole table, it may last way too…
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 18 Comments
…. There are workloads other than OLTP and the recent optimizations to MySQL still leave a lot of low hanging fruit where these… data. Since a single query is single threaded in MySQL, the new optimizations don’t really help with this workload. The following… of ram. The amount of ram is significantly larger than my already hefty 55GB testing data set, so this means that if MySQL…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
… this is complicated. If I have 8GB of RAM I can assume I want 4GB allocated …should have been implemented outside of MySQL Storage Engine level for MySQL – reading rows in their physical order… would be very interesting to see how these optimizations will show themselves in practice. The other …
Comment: Why do you need many apache children ?
February 17, 2008 By 15 Comments
… on optimal concurrent mysql connection in active state. If all the queries required in each php script on a web site are optimized best, which is the optimal concurrent mysql connections in busy state? If single query is optimized well, it takes… sorting at all. Of course, the optimal concurrent mysql connections may be dependent on the available RAM and speed of disks. I just…
Comment: Why do you need many apache children ?
February 17, 2008 By 15 Comments
… in slow HTTP connection reaches to wait_timeout and MySQL aborts those mysql connections. There were about 200 Aborted_Clients per hour… of web server only serving php scripts is CPU not RAM. Modern best dual CPUs can handle at most 200 concurrent…). In China, it is really hard to find guru on mysql, optimization because there are so many web sites cheating traffic. Many…
Comment: MySQL: what read_buffer_size value is optimal ?
September 20, 2007 By 25 Comments
… harddrive into the cpu. Pretty much similar why a cpu:ram ratio of 1:1 regarding fsb is often better than… the mysql itself. It sounds more that you want to protect your consulting business to help others to find their optimal values… think the same applies in order to find optimal values for different setup. Optimization by obscurity doesnt work either – so lets share…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… of RAM MySQL version We used MySQL 5.1.14-beta sources for MyISAM / InnoDB and MySQL 5.1.14-falcon bitkeeper tree bk://mysql.bkbits.net/mysql-5… comparable and I think this shows both engines are maximally optimized and the result is maximal that can be reached for…
Post: KISS KISS KISS
March 1, 2009 By 7 Comments
…you can get a box with 128G of RAM within $15K. Keeping working set in memory … becomes bottleneck so they are using various optimizations including tricky prefetch approaches suggested by YouTube. … the application is only related to the MySQL redundancy for very high quality/high scale applications…
