… same table as I used for MySQL Group by Performance Tests to see how much MySQL can sort 1.000.000 rows… temporary sort files on tmpfs (/dev/shm) to avoid disk IO as a variable as my data set fits in memory… sort_buffer_size is 32K which gives us the following speed: mysql> select * from gt order by i desc limit 10; +——–+——————————————+ | i…
Post: Getting real life query speeds with MySQL
January 10, 2007 By 10 Comments
… check for query performance improvements followed indexing/query changes or MySQL configuration changes our customers often decide to run the query… second and further runs, especially when it comes to disk IO workloads. The attempt to fix it is often using SELECT… by MySQL restart. Even if you restart MySQL server there is significant chance data will still remain in Operation System cache speeding up…
Post: Implementing efficient counters with MySQL
July 1, 2007 By 16 Comments
…fetched it does not cause any synchronous IO and background IO can be batched rather effectively. As …but writes still have to happen at full speed. It also really hurts if you choose replication…use existing solutions you can use memcache + another mysql instance (or simply the database which is not …
Post: MySQL Partitioning - can save you or kill you
December 11, 2010 By 45 Comments
… a while to write about using MySQL Partitioning for Performance Optimization and I just got … table recently. Using this trip replication could be speed up about 10 times as couple of partitions…replication became CPU bound (single thread) instead of IO bound. You could celebrate but hey…. you need…
Post: How network can impact MySQL Operations ?
September 1, 2008 By 5 Comments
… window which involved a lot of data copying around between MySQL boxes. We had prepared well and had measured how fast… not overly fragmented tablespace, or it is easy to become IO bound rather than network bound. As I mentioned you can… copy process we saw the copy speed about 20MB/sec instead of projected 80MB/sec. The IO and CPU usage both on…
Post: TPC-H Run on MySQL 5.1 and 6.0
April 10, 2008 By 16 Comments
… 10G and 100G data sets which was CPU bound and IO bound box on the Dell 2950 box w 16G of… and MySQL 6.0 can complete all queries within an hour. We can also see MySQL 6.0 improving query 20 speed dramatically, the rest of the queries is however close and MySQL 6.0…
Post: How much overhead DRDB could cause ?
June 2, 2008 By 4 Comments
… expect. First lets talk what kind of IO you performance you care about while running MySQL over DRBD. Your reads are going… cause (depending on file system and configuration) more synchronous IO operations – MySQL binary logs are not preallocated as Innodb logs are which… settings. So overhead can vary a lot depending on the speed of the disk subsystem and network. If you do not…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… do not stop IO pressure and OS may swap out not buffer pool but some other important parts of MySQL process. Q20… is serial so that we can see the full write speed of the disk for throughput. INNODB uses fuzzy checkpointing to… high IO scenearios is that it commits too often and we’re only seeing 33% of the raw disk write speed. Since…
Comment: Multi Column indexes vs Index Merge
October 20, 2009 By 31 Comments
… results for a vehicle. Tried (date_time,vehicle_id) and MYSQL won’t use it, (date_time) and its bringing back….date_time, g.speed, g.odometer, gl.building_number_name, gl.street, gl.town_city, gl.region, gl.postcode, io.high, i.id as is_idle, m.id as is_moving, o.id as over_speed, l.name as…
Post: Here's a quick way to Foresee if Replication Slave is ever going to catch up and When!
August 29, 2012 By 11 Comments
…cold and there’s a lot of IO happening, Not all queries are created equal…that something in awk: delay=60 cmd=”mysql -e ‘show slave status\G’ | grep Seconds_Behind_…speed = (delay-($1-prev))/delay; o_speed = (start-($1-passed))/passed if (speed == 0) speed_d = 1; else speed_d = speed; eta = $1/speed…
