9,597 reputation
11025
bio website
location Cincinnati, OH
age 43
visits member for 2 years, 2 months
seen 3 hours ago

If you question or otherwise fail to recognize the superiority of the relational model... you're doing it wrong.


21h
comment select on table slows done during update on same table
A query cache this large is a significant liability. Never full or pruned? Doesn't matter -- the time required for maintaining/managing the memory structures for such a large query cache will cost you performance. Something between 32MB and 256MB is probably a lot more reasonable.
Nov
25
comment In MySQL 5.6, schema changes in master blocks the slave?
All reads from all tables? Or just the table being altered?
Nov
20
comment schema for mysql database with 30million rows
You do realize that you have a seriously, seriously flawed schema here... right? Money should never be stored as FLOAT (nor as DOUBLE) and you should not be redundantly storing text for things like menu item or employee name.
Nov
12
comment Data on the slave becomes inconsistent after couple of days
@NawazSohail MySQL 5.0 is apparently still running on the master, so there's no possibility of GTIDs.
Nov
6
comment Steps or evaluation order of the SQL statement
zazzle.com/sql_queries_explained_coffee_mug-168973002678226909 (not a plug, no affiliation)
Nov
4
comment Differing AUTO_INCREMENT values between 5.1 vs 5.6 slaves
SELECT @@auto_increment_increment, @@auto_increment_offset; on all servers, and compare?
Nov
4
comment Differing AUTO_INCREMENT values between 5.1 vs 5.6 slaves
Curious... what is the master's binlog_format set to? In the table above, what's the largest id actually present, and are the tables InnoDB?
Nov
2
comment Moving MySQL to another server without updating connection strings
Wow... famous last words: "What could possibly go wrong?"
Oct
31
comment MySQL stops unexpectedly on CentOS 6.5 with WHM/Cpanel after migrating server with transfer tool
You'll need to monitor the server. It's likely not mysql that is the memory hog, it just looks, to the kernel, like a good candidate for killing, when memory pressure is high on the system as a whole. A certain popular major name web server that forks off multiple processes serving one concurrent request each is known for causing this condition under load. Before upgrading the instance class, consider that a more stable alternative is keeping database and web server on separate instances. There are other questions and answers here that should give some helpful hints on this topic.
Oct
31
comment SELECT in federated table is to slow
@GeoVIP it depends on the future growth of the remote tables, the cost of data transfer, who controls the other server, how often the remote tables have changes, and likely other factors. For now, it seems like a pretty good improvement. :) Did you just add an index or also try my alternate query on your existing setup? If all you did was add an index, you're probably good. The query I proposed was the part I referred to as not ideal for an interactive site.
Oct
31
comment Last synchronization time between master and slave (replication)
Define "goes down."
Oct
31
comment MySQL stops unexpectedly on CentOS 6.5 with WHM/Cpanel after migrating server with transfer tool
The problem occurred before the first line of error log that you posted. That line is a symptom. The rest is recovery. cat /var/log/messages | grep kernel. The usual suspect is a web server running on the same machine, and the kernel is killing mysql when the web server starts making excessive demands for more and more memory.
Oct
29
comment Moving a large MySql database(Over 15 GB) from server to server mysqldump
Not sure where you got the information about -r causing row-by-row read and saving memory. -r is an alias for --result-file -- specify the output filename: dev.mysql.com/doc/refman/5.6/en/… ... but mysqldump doesn't use a lot of memory, at any rate.
Oct
29
comment Moving a large MySql database(Over 15 GB) from server to server mysqldump
Don't forget the --events --routines and --compress options in mysqldump.
Oct
29
comment Determine if queries have small or large results
Each table involved in any cached query takes 1 block (no matter how many queries against the table are cached), each query takes 1 block, and each result takes 1 block... so there's the origin of your 2.6 average. However, query cache block size is dynamic, and contiguous chunks of free query cache memory are counted a only 1 block... so the free blocks and used blocks don't add up to a meaningful number that you can add together or multiply by. Illustrated here: dba.stackexchange.com/questions/33811/…
Oct
24
comment Is it possible to automate the creation of multiple FEDERATED tables in MySQL?
Version Caveat: If you are writing to the federated table, there is a fairly serious bug in MySQL versions prior to 5.5.36, 5.6.16, and 5.7.4, that could crash the local server due to an invalid pointer if an UPDATE ... JOIN joined a federated table to another table and exactly one row matched on the join. If 0 rows or more than 1 row matched, the issue did not occur, because the optimizer came up with a different query plan. This was fixed in the versions listed, above. If you are only reading from the federated side, this issue has no impact.
Oct
24
comment Is it possible to automate the creation of multiple FEDERATED tables in MySQL?
@PythPhytho yes, the lack of FEDERATED support is one of several drawbacks to RDS, a decent platform overall if you can accept the limitations in functionality that are arguably necessary for a "managed" MySQL offering. Here's an extremely valuable reference that will help avoid some potential gotchas with the federated engine. It's an old article, but federated hasn't changed much in "official" MySQL (it was rewritten as "FederatedX" in MariaDB (and Percona?), but many of these principles still hold). onlamp.com/pub/a/databases/2006/08/10/…
Oct
24
comment Is it possible to automate the creation of multiple FEDERATED tables in MySQL?
@PythPhytho yes, you can. I have a daily report runner on a "local" MySQL server that uses several federated connections to an RDS server to fill in some data from a different system. With a FEDERATED table, the remote server (where the data lives) has absolutely no clue that you're using it this way, because the federated storage engine makes an ordinary client connection to the remote system, and it writes and sends ordinary SQL queries to the far end, to get its work done.
Oct
23
comment How to change table with innodb/myisam engine to federated
@tombom the federated storage engine absolutely does support indexes, and in fact, they're critical for good performance... but they work in a very unconventional way. If FEDERATED doesn't know about a useful index for a given query, it won't send a WHERE clause in the remote query, and it will fetch the entire table and scan it locally. It usually makes sense to declare indexes on the federated table that don't even exist on the real table, so that it won't do this.
Oct
22
comment Returning true or false in a custom function
Note that if (the_nxt(44) = true) will never be true, because the_nxt() returns VARCHAR 'true' instead of the literal value TRUE (which is the integer 1)... and the string 'true' != the integer 1 (true, unquoted). A more correct and less error-prone solution would be for the function to return a TINYINT TRUE or FALSE and test whether the_nxt(44) IS TRUE (not =).