… clogs replication stream and small and fast updates which go after it in MySQL binary log can’… Query Chopping – this can be running update/delete with LIMIT in the loop, controlling maximum amount of… you’re planning to update/delete and having multiple queries to delete it (see example below) This …
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
….0454 0.6% 260 0.0002 1.00 0.00 DELETE poller_output # 20 0x428A588445FE580B 0.0449 0.6% 260 0… underlying tables involved and the query execution plan used by MySQL. The end result might be that you end up limiting the number of results returned by the query, by using a LIMIT…
Post: Percona XtraDB Cluster - installation and setup webinar follow up Q&A
October 28, 2012 By 4 Comments
…despite the response time limitations. Even with parallel replication, the response time limits the number of transactions…you would like to switch back and forth between galera and built-in mysql replication (asynchronous) for a … an SST it’s enough if you delete grastate.dat). So, it is appropriate …
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
…. What kind of outages can happen? Someone runs UPDATE or DELETE and forgets the where clause or filters weren’t quite right… corrupt and mysql shuts down Your server or RAID controller crashes and all data is lost on that server A disk failed, and RAID… XtraBackup for MySQL for binary backups. Strong Points: It can restore an entire server very fast. Often the limiter of how fast…
Post: SQL Injection Questions Followup
July 26, 2012 By 2 Comments
…-printing characters. The standard API function mysql_real_escape_string() is aware of binary bytes, and will escape appropriately. SQL also has… of the limits of the procedure language MySQL offers, and also it moves too much computation load onto their database server. Their MySQL server… laborious and time-consuming to do code review to find all these cases. Andrey M. also asked: Q: Should I delete all…
Post: MLC SSD card lifetime and write amplification
November 15, 2011 By 8 Comments
… unusable. And obviously the SSD card based on MLC NAND has a limited lifetime. There is a lot of misconceptions and misunderstanding on… write load during this hour. MySQL handled 25,000 updates/sec, 20,000 inserts/sec and 1,500 deletes/sec, which corresponds to write throughput 273.45MB/sec from MySQL to disk. And it helps to…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… this into a query log, and examine the log. In MySQL 5.1 and newer with the InnoDB plugin, … fill up the disk. #!/bin/bash # Begin by deleting things more than 7 days old find /root/tcpdumps/…_schema.processlist p on b.trx_mysql_thread_id = p.ID LIMIT 1′) if [ "${host}" ]; then echo “Host ${host…
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
…DELETE: 18127219 AVG_TIMER_DELETE: 303505097 MAX_TIMER_DELETE: 62494969560 Or using this data we can TOP 5 accessed tables via mysql…it does not mention UPDATEs and DELETEs. 3. Beside logical … FILE_NAME like ‘%sbtest55%’ limit 5G *************************** 1. row *************************** FILE_…
Post: Filling the tmp partition with persistent connections
August 6, 2012 By 14 Comments
… disk space so those kind of partitions are very limited in size and there are some cases were we can run out… files has MySQL opened: # lsof -p 6112|grep ML mysqld 6112 root 38u REG 7,0 106594304 18 /tmp/MLjw4ecJ (deleted) mysqld… REG 7,0 62390272 26 /tmp/ML19sFCA (deleted) There are lot of files in deleted status that are filling the partition. The…
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
… 100 Warnings: 0 mysql> insert ignore into test2 select * from test limit 300; ERROR 2013 (HY000): Lost connection to MySQL server during query…deleted rows or dropped table. I should also mention at Percona we offer assistance in MySQL Recovery, including recovery from Innodb corruptions and deleted…
