The purpose of this post if to show how you can automate the creation and pruning of partitioned tables. If you want to read about partitioning I recommend reading our manual.
In short partitioning makes it possible to spread your individual tables across a file system according to the partition rules you specify.
Reasons for partition your tables might be:
- Insert performance, smaller index trees for stable insert throughput.
- Select performance, only read data from selected (aka partitioning pruning) partitions.
- Delete performance, drop partitioning is must quicker than doing range deletes of old data.
Partitioning definitions is part for the CREATE/ALTER table statements, in …
I got sidetracked today playing with Magic Squares and thought it
might be a good chance to give an example of using MySQL Routines
, Loops and IF checks.
So If you are unaware of what a Magic Square is I have included a few links. It might save you a Google search but otherwise think Sudoku as an example.
So I recently realized that I have not yet talked much about
Many good blog posts on MySQL partitions already exists and I have listed a few below.
One of the first posts in this blog was about reporting MySQL
bugs "properly", in a way that maximizes chances for it to be
processed really soon. I had written the following there:
"Ideally, you should provide a complete test case and/or instructions that any reader can use to reproduce your problem"Indeed, if one can just copy/paste something to mysql command line client or run some file attached to see the problem, chances are high for the bug to be processed really soon. We all like to get low hanging fruits from time to time, and Oracle engineers who work on bugs are not exceptions. But does this mean that bug without clear test case has no value and is going to be ignored?
It should NOT be the case. Let's review Bug #70617 reported by my …
While it was explained already by Sveta and others what does it really mean
to "Verify" (or "Confirm", in Launchpad/Percona's terms) a bug in MySQL
software, and why this step in a bug's life cycle is important, we still often
read complains about too much time taken to verify the bug even
with a clearly repeatable test case that can be just copy/pasted,
like Bug #69985 or notably more serious Bug
#69990. Moreover, I often make comments of this kind
So, it seems there is still a need …
I’m observing the process of most awesome SHOW commands being abolished, destroyed and some weird information_schema tables are introduced instead.
Say, even though you can select configuration variables using @@syntax, you can’t do same for much more interesting to DBAs status variables in any more interesting logic.
Apparently instead of doing
SHOW STATUS LIKE "questions"
one has to do this now (I’m being dramatic here, above hasn’t been removed yet, but hasn’t been expanded for better usage either):
SELECT VARIABLE_NAME, VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="QUESTIONS"
Do note, those SQL standard followers will get caps-lock button swapped with space bar soon.
Of course, we, DBAs, know that one can simplify stuff by creating stored routines:
CREATE FUNCTION `gstatus`(v varchar(64)) returns varchar(1024) return ( SELECT …[Read more]