… the values from MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query: mysql [localhost] {msandbox} (test…/dist/5.6.10 | | BACK_LOG | 50 | 80 | | OPEN_FILES_LIMIT | 1024 | 5000 | | INNODB_AUTOEXTEND_INCREMENT | 8 | 64 | | MAX_CONNECT_ERRORS… MySQL 5.5 and before it was “ON” by default with query cache size of 0 which makes it disabled. I wish query…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… traversed in both directions for different queries. This will use fast primary key for some queries and use key K as covering… is a hard limit on amount of indexes you can have, which is 64 per table in recent MySQL versions. However it is often too many. Instead of thinking about hard limit of indexes I prefer…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… limitation in resources in a cluster (cpu, memory, disk, power,etc) then split the problem up into more chunks. Each Shard-Query… of compute resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies to the… minor abstraction modifications in the data access layer. Shard-Query can provide query execution plans based on the relational algebra rewrites So…
Post: Apache PHP MySQL and Runaway Scripts
May 20, 2008 By 20 Comments
… unfortunately very serious limitation in practice when you’re using MySQL with PHP as you can have single runaway query which takes a… – for example you can open another MySQL connection to kill the query which was running (otherwise query may well continue to run even… sleep() function is wrong and if I would use real MySQL queries this would be counted to max_execution_time and so…
Post: Review of MySQL 5.6 Defaults Changes
October 8, 2012 By 10 Comments
… which would mean limit disabled. Very few users actually need DOS prevention this variable strives to provide as MySQL is typically protected by Firewall to begin with. open_files_limit = 5000 strangely enough this is… – 64K or even more would be good default. query_cache_size = 1M Query cache remains off by default but default size is…
Post: Load management Techniques for MySQL
May 6, 2012 By 3 Comments
One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times… need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though fixing… too much in this case throttling by having relatively short queries and introducing “sleeps” between them can be a good idea…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…_heap_table_size variable which allows you to limit size of MEMORY tables (the limit applies both to implicit and explicit ones… Percona Server you can do better as you can query temporary tables too: mysql> select sum(data_length+index_length) from information… and fixed. This is where your MySQL Support contract can be handy. Conclusion Understanding where MySQL can allocate memory can help us…
Post: Is your MySQL Application having Busy IO by Oracle Measures ?
January 8, 2012 By 1 Comment
… MySQL as MySQL optimizer with its nested loops joins does not favor sequential scans even for analytic queries executions. Also lack of parallel query execution makes limits how much IO full table scan queries can consume. If…
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
… common cache methods for MySQL. Popular cache methods The MySQL query cache When the query cache is enabled, MySQL examines each query to see if the contents have been stored in the query cache… frequently. Use what you need Both Memcached and the MySQL query cache are limited in size. If you try to cache more information…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… system has will place a limit on amount of queries system can ran, for example if we have query which requires 1 CPU… data to estimate capacity of MySQL system ? We can look at CPU and IO consumption per Query and compare it to estimated… get CPU consumption per query ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018…
