… the tests individually Sysbench gives about 330 req/sec and mysqldump for large table completes in about 95 seconds. If we run them… sysbench OLTP on small table drops more than 150 times when heavy mysqldump is running concurrently. mysqldump itself also slows down about 2x. What…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 59 Comments
…, background flushes while on the drawbacks side we see significantly large table size (especially if data size is close to memory size… handling, concurrency issues, problems dealing with very large number of tables, slow data load and ALTER TABLE and others. Another big one is COUNT… too much time to do restore for Innodb. On large scale installations mysqldump does not work anyway but it may still work…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
… process can be a lot faster, especially for large indexes which would get inserts in very random … can also use this trick for ALTER TABLE which requires table rebuild. Dropping all indexes; doing ALTER and… or tools (like mysqldump) do not get advantage of it and will use old slow method of building…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
…large schemas with a lot of different table structures. So which variables affect the load speed: Table…insert (standard mysqldump output) or LOAD DATA INFILE (–tab mysqldump output). …slow down dramatically as amount of data loaded in the table increased and the fact this depends not only on table…
Post: Performance impact of complex queries
January 17, 2007 By 11 Comments
… MySQL Performance by complex queries on large data sets(ie some large aggregate queries) and batch jobs. It ….4 times I could make single full table scan query to slow things that bad, so “ls” would … the master using mysqldump, or even careful file transfer if you’re using MyISAM tables. Chop it If …
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 13 Comments
…tables and run on linux with 16gigs or ram. Here is our my.cnf # sammple MySQL config file for very large…=2048 long-query-time=5 log-slow-queries=/apps/log/slow-query interactive_timeout=300 wait_…_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-…
Post: Living with backups
November 6, 2008 By 28 Comments
… doing very heavy queries against each table in the database when mysqldump is used or reading a lot … I/O coming from MySQL, but also such large I/O requests take significant time to complete… filled with random information additional I/O occur slowing everything down even further. What does it all …
Comment: Should you move from MyISAM to Innodb ?
February 1, 2010 By 59 Comments
… on mysqldump and innodb file-per table. Our “Database File-Per-Table Archives” web site summarizes like this: “31 Hosts 90 Databases 2919 Tables… group. Largest instances top out at approx 60M rows. Most tables are < 0.5M rows. Some applications use MyISAM with partitioned... when their SYNC is completed. Therefore, the HUGE/SLOW restore of large InnoDB tables is not an issue, My suggestion to use all…
