… individually Sysbench gives about 330 req/sec and mysqldump for large table completes in about 95 seconds. If we run them concurrently… innodb_old_blocks_time variable to some value will prevent important data to be pushed out of buffer pool. Lets repeat… can see in default configuration MySQL has buffer pool which can be easily washed away by large table scans or heavy batch jobs…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
…. Using multiple tables gives some very important benefits – your data becomes managable, your ALTER TABLE or OPTIMIZE TABLE now locks small table for few seconds rather than giant 100GB table… loosing data etc. Read more in my Why MySQL Could be slow with Large Tables article. 5. The n+1 pattern This probably…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… MySQL 5.0 with addition of greedy join the problem on looking at too many table join combinations in joins with very large tables is elevated, however there are still cases when it may take too long. Most practically important one is if Derived tables (selects in FROM clause) are used. MySQL will execute these subselects on…
Post: Finding your MySQL High-Availability solution – Replication
November 13, 2009 By 6 Comments
… known that with MySQL, altering a table means recreating the whole table and it is a blocking operations. Altering a large table may takes many hours. The trick here is to run the alter table… of MMM is its lack of fencing capability. It is important to stop all the connections to the failing master and…
Post: Auditing login attempts in MySQL
December 28, 2012 By 4 Comments
…important task on some scenarios. Unfortunately there are not too many audit capabilities in MySQL Community so the first option to audit MySQL…will have to deal with very large files on high loaded servers. … valuable information. It is important to mention that these tables are stored in INFORMATION…
Post: What's required to tune MySQL?
July 27, 2011 By 4 Comments
… expect to deliver huge wins. If MySQL is actually badly configured, you can…important factors, including the following: The hardware The server configuration The workload The queries The schema design (table…itself. This review can sometimes deliver large gains that go far beyond configuration alone….
Post: Testing MySQL column stores
August 16, 2010 By 15 Comments
… to do some testing on a large data set against two MySQL column-store storage engines. I’d … problem would be “select avg(char_column) from table” where the column contains a mix of ascii … probably wouldn’t happen in a real application. Importantly, I didn’t find significant defects in Infobright …
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…, and since there are still a large number of MySQL 5.0 and 5.1 installations …locking behave with query_cache? That is an important question. If Query Cache is enabled and …table that is already in the Table Cache, any query to a table that has a DDL operation waiting, will block. Why? Because MySQL…
Post: Review of MySQL 5.6 Defaults Changes
October 8, 2012 By 10 Comments
… to over 5000 or use more than 5000 tables. There is also little “savings” in keeping this …based sorts. This means it makes it even more important to set this variable to higher values for …large etc referring to amounts of memory we have in the phones these days, I made in my Optimizing MySQL…
Post: Which Linux distribution for a MySQL database server? A specific point of view.
December 8, 2011 By 38 Comments
…but their install bases are large enough to mention them separately. Running MySQL won’t be much … improvements, however these days it is rarely important to MySQL users. If it is to you, …MySQL. By default it not only starts database, but also updates system tables (if needed), scans all tables …
