MySQL DBA Preparation: Mysql Architecture
Posted on | February 2, 2009 | No Comments
Hasin bhai is one of most
encouraging persons I have ever met. Good people generally advice you
to do good, but he will support and encourage (and push) for any good
thing. He took my word(along with some others) to take preparation for
MySQL DBA exam. I don’t know if I can make it, but I will go through
the book MySQL Certification Study Guide’s chapters related to DBA
exams. So, I thought to share what I’ve learned( or need to learn )
reading the book here with you.
23.1. Client/Server Overview
What is mysqld_safe?
It is a non-client utility script for starting up and monitoring the server.
What is myisamchk?
It
is a stand alone non-client utility program for checking and repair
isam database by accessing/modifying table files directlly.
In what language the interface API is written?
C
How you can get the C client library?
The C client library is available as part of MySQL distributions.
What are mysql connectors?
The
connectors are drivers that act as bridges to the MySQL server for
client programs that communicate using a particular protocol.
Which connectors are provided by MySQL AB?
MySQL
Connector/OBDC, MySQL Connector/J, and MySQL Connector/NET, which are
connectors for clients that use the ODBC, JDBC, or .NET protocols.
MySQL works in which type of environment?
Distributed environment
Can you connect to the MySQL server directly if it is running on your local machine?
No,A client program must be used even to connect to a mysql daemon running on local machine.
Can mysql be used in heterogeneous network?
yes
Can mysql provides cross platform interoperability?
yes
To connect to a mysql server running on window, do you must use a client running on windows?
No, client or server can run on any system.
23.2. Communication Protocols
If the client program and the server runs on same machine, what type connection is made to communicate?
Local connection.
TCP/IP cannot be used in local connection. true or false?
false
What other protocols can be used for remote connections except TCP/IP
None.
All operating system excepts MacOSX supports TCP/IP, true/false?
False. All OS supports it
Unix socket file is supported for Unix only
true
Unix socket files can be used for remote connection only between two mysqld both running on unix systems
False, socket files can be only used for local connection
Named pipe and shared memory can be used for remote connection between two mysqld both running windows
false, they can be used only for local connection.
How TCP/IP can be disabled?
If MySQL server is started with the –skip-networking option.
Some versions of Unix servers do not support unix socket file protocol.
false. every version supports.
Named pipes are supported by every windows server.
false, only the servers ha -nt in its name (mysql-nt, mysql-max-nt).
Named pipes are enbaled by default.
false
How to enable named pipes?
by starting the -nt server with the –enable-named-pipe option.
Shared memory connections are suppoted by windows server only which name has -nt.
false, supported by all windows servers.
Shared memory is enabled by default.
false
How to enable shared memory?
you must start the server with the –shared-memory option. (Note: no ‘enable‘ prefix)
All the connection methods are equally efficient.
false
Communication via named pipe is faster than TCP/IP in every windows configuration.
false, in many windows configuration they are slower.
On Unix, unix socket file provides better performance than TCP/IP.
true
ODBC is faster than native C library on some platform.
false, its slower on any platform since it is layered on top of the C lib.
JDBC connection made via mysql connector/J is much slower than the connection made by native C client library.
false. its roughly about the same speed.
23.3. The SQL Parser and Storage Engine Tiers
The server executes each statement in a tree tier processing model.
false, in a two tire processing model.
Upper tier includes the SQL parser and opitmizer.
true
Parser and optimizer directly interact with the tables only available in the statement.
false, they do not interact to any table directly.
The lower tier comprises of only one selected storage engine.
flase. a set of storage engines.
Storage engines cannot be included at configuration time.
false. they can be easily included in the server at configuration time.
The SQL tier is highly dependant on the storage engine managing any given table.
false, for most of the part SQL tier is free from any dependancy.
ALTER TABLE, CREATE TABLE is alway storage-engine depandant.
false, only if engine options is given.
Full-text and spatial indexes is supported by all storage engines.
false, Only by MyISAM storage engine supports these.
COMMIT and ROLLBACK have an effect in every storage engine.
false, only transcational storage engines like InnoDB
23.4. How MySQL Uses Disk Space
What is the use of data directory?
Mysql primarily uses diskspace for directories and files found under the data directory.
There is always a directory corresponding to any database.
true. its true regardless the engine type of the tables.
.frm files contains table structure.
true
Memory type tables doesnot have any .frm files for its structure.
false, All tables irrespective of its storage engine must have a .frm file.
MyISAM uses a single file for its data and indexes for each table.
false, it creates two files, one data file and one index file for each table.
The InnoDB storage engine has its own tablespace and log files.
true
InnoDB saves the undo logs in the log files if needed for a transaction rollback.
false, this information is saved in table space.
InnoDB tablespace files used to ensure no data loss.
false, log files contains.
All committed transactions are saved in tablespace file by InnoDB engine.
false, they are saved in log files.
Default tablespace filename by InnoDB is ibdata1.
true.
Default log files are named ib_logfile0 and ib_logfile1 by InnoDB engine.
true.
It is possible to configure InnoDB to use one tablespace file per table.
true.
23.5. How MySQL Uses Memory
For each client that connects, the server allocates a thread to it to handle the connection.
true
mysql server maintains a small cache for thread handler.
true.
Threads are created only by the server.
false.
Can storage engine create own thread?
yes, they can.
Replication uses threads.
true
MySQL server stores the grant table into memory.
true
Key buffer in memory holds index block for which storage engine?
MyISAM.
MyISA also creates a memory buffer for the table rows.
false. it depends on the OS.
Open tables are loaded into cache by mysql server
true. it loads them in table cache.
mysql does not support query cache.
false.
What is the use of host cache.
it holds the hostname resolution lookups
The InnoDB storage engine logs information about current transactions in a memory buffer.
true.
Current transaction is directly written to the physical log files by InnoDB engine.
false. it is written when the transaction is committed.
The server might create internal temporary tables in memory during the course of query processing
true.
how to determine the size of such tables?
by tmp_table_size system variable.
What is Created_tmp_disk_tables?
If the size of a temporary table exceeds the value of the
tmp_table_size system variable, the server converts it to a
MyISAM-format table on disk and increments its Created_tmp_disk_tables
status variable.
The server maintains one buffer for each client connection.
false. The server maintains several buffers for each client connection.
One is used as a communications buffer for exchanging information with
the client. Other buffers are maintained per client for reading tables
and for performing join and sort operations.
What is use of SHOW statements?
Several SHOW statements enable you to check the sizes of various memory-related parameters
How to see the system variables about how the server is configured?
by using SHOW VARIABLES command
How to check the run time state of cache?
by using SHOW STATUS command
MySQL UNIX_TIMESTAMP problem while using in where clause to find out result from a date
Posted on | January 13, 2009 | 2 Comments
Today my teammate Mohsin was in a funny problematic situation and discovered an interesting behavior of unix_timestamp function of MySQL.
The task:
Say, he had a table which contained data along with the timestamp of the insertion time in a column. Now, his task was to find out all the entries on a particular date.
The query:
His query was similar to this:
“select * from aTable where insertion_time = unix_timestamp(’2009-01-13′)“
Here, insertion_time is a column of type timestamp.
The result:
This query returns an empty result set however there was some entry on that date i.e. 2009-01-13!!
The cause:
unix_timestamp(’2009-01-13′) returns a timestamp for the first second on that day. But he had no entry on that time i.e.
on 0000hours on 13th January 2009:)
But all the entries were inserted on a later time!
The solution:
There are more than one solution to this problem, the simple one might be like this:
“select * from aTable where insertion_time >= unix_timestamp(’2009-01-13′) and insertion_time < unix_timestamp(’2009-01-14′) “
safari jquery keypress event bind problem for arrow key!
Posted on | November 21, 2008 | 1 Comment
Its a little post just to share with you that today I was working on keyboard navigation with javascript. Surprisingly the arrow keys was not working at all while using $(document).keypress(function(event){alert(’in here’)}) on Safari browser! Even the alert was not firing up!!
Anyway, for this you need to use $(document).keyup solved the problem 
Check the demo is not working with Safari even on the jquery site
http://docs.jquery.com/Events/keypress#fn
Life with a meaning
Posted on | September 26, 2008 | 5 Comments
I don’t know what it is — a life with a meaning. I cannot define it, neither you can. Instead, we all may have our definition of a meaningful life. Or, do we? Do you really know what will be a meaningful life for you? Do you know what you want - not at this moment, but overall in your life? which will give you feeling of completeness, a solemn joy of achievement?
I tried to find this answer from my childhood. When I was in grade two, I used to cry to my elder sister that as being the youngest I will left alone on the earth without them, my parents.. as they will die sooner generally. Wired thought of course, but on that time the meaning of my life was their affection, their presence.
I grew up gradually. I used to go to the village with my family traveling long way by bus and boat. The boat riding was the most favorite part of the journey. I used to enjoy the scenery along side the river, the paddy field, the rhythm of the boatmen’s paddling through the blue water to an endless way… I wish I could be boatman and enjoy all the beauty that nature can reveal.
Reading and writing- became my next meaning to life when I was in high school. I found books are the only media that can make others experience and emotion live to me.
Anyway.. now I think the meaning of life lies in some contribution to the community/communities I belong. Little or more doesn’t matter, can be a software, can be piece of knowledge, can be a little help to some of those who need, can be a research for the greater betterment… doing something which so not limited only to myself.
In the contrary, at the same time I wish I could see the whole word. I could deny the state of being an ant in my house whose world is the peripheral of my walls. I wish could be as free.
I don’t know actually what want to mean.. what I am writing.. perhaps…
NetNewsWire - A great RSS feed reader for Mac
Posted on | September 24, 2008 | 1 Comment
I am new to the fantastic mac world and was searching for what “free” but fascinating apps are there. Googling for some time I came to a site called pure mac. From there I found the application NetNewsGator.
The screenshots and the features seemed awesome!
2 pane view of NetNewsWire
Among its numerous features the most interesting to me was the internal web page browser. From the preference tab you can also select to enable plugins, java and javascript!
You can create smart folders( mac users got the meaning I guess ) and you can choose your conditions from an existing pool.
Happy feeding ! 
Did not hesitate to download.
Snip first words from first n charecters using php
Posted on | September 9, 2008 | 2 Comments
Problem: You are writing a blogging software. Where on the first page you like to show the lates posts in a list but not their full content, only say first 200 letters. Porblem raises when the last word is spans out of the 200 letter limit.
For example, say, 198th letter is ‘H’ which is the start of the word ‘Home’. So, if you take only first 200 letters, the text will endup with ‘Hom’, certainly thats unwanted.
What to do?
Ans:
One solution can be like this:
Lightwieght VOIP billing software using mysql,servlet and ajax
Posted on | July 7, 2008 | 4 Comments
Though I don’t like to do extra work beside my day job, but there are some people (and some offer :P) whom I don’t like to refuse. Surely the work must be interesting.
Before some days, one of my ex bosses gave a project to build a VOIP billing software. I was studying SIP protocol for a while and the offer seemed very interesting.
The task was simple though. It will just show the call history mainly. But the challenge was to make it as light weight as possible and secondly there is no documentation about the data!
The reason behind making it light weight is that the application will be hosted on the switch, i.e. the web server is running on the switch box. Well, I decided to use no flashy look at all, used plain css just the login prompt is made of ext-js. The pagination appears on top right is also ajax based.
Main problem was to find out the data. I have been given the credential to log on to the mysql server but they could not provide any information which table contains which data. The database was complex enough with more than 60 tables and the designer seemed to take extra care to put redundant data on different tables with similar name like “clients”,”client164″,”clientinfo” etc. And guess what, the actual log in infromation is in client164 table 
Here are some screen shots:
GPS Tracking: Good side and the ugly side
Posted on | June 27, 2008 | 1 Comment
Perhaps many of us are well aware of GPS i.e Global Positioning system and its applications like GPS Tracking. GPS enabled gadgets can be regarded as a compulsory tool in many cases especially in field works, surveying, expeditions etc. It can confirm you that you are never lost. My wife is a geologist and I often hear her saying how GPS Tracking is so important in her job. In her current project they are using LiveWire Real, this little instrument helps them knowing everyone’s position in an wide area.
Besides, when I lost my car in last year, I decided to use some devices to protect car theft. Actually it is quiet impossible to avoid car theft, but I took a different measure. I installed T-Trac XS Internet GPS Car Tracking System in my car and really feeling secure. Now I can help the police to know the exact location of my car if I am unfortunate enough to have my car stolen again.
Surely these are some good side of GPS tracking. But I was very disgusted when I came to know that one my friend has presented his wife this pen. It is nothing but a small gps tracking device that can track his wife. Well some of you may find nothing wrong with this, but to me it is an example of mean mindness and mistrust. You can use such thing for your pet, but not for your partner.
Should you?
Learning Flex3 Task 3: Using function in script tag and in separate ActionScript file
Posted on | June 7, 2008 | No Comments
In the last example we have used some functionality against the click event of the button. Now we will encapsulate the code in a function and call it.
For embedding script we have to use a special tag in flex. It is the mx:Script tag. Inside the tag we will use a CDATA section to write the script codes. If any of us is not aware of what CDATA is, just to inform him/her inside CDATA no tag is parsed. You can write virtually whatever you like.
Let’s add a script tag into our application.
<mx:Script>
<![CDATA[
public function clearText():void
{
lblName.text = '';
txtName.text = '';
}
]]>
</mx:Script>
Calling this function is simple as you might guess:
<mx:Button id=”btn1″ label=”Clear All” click=”clearText()” />
Don’t forget to enter some comments, it’s a good practice.
The entire code will now look like:
<?xml version=”1.0″?>
<mx:Application xmlns:mx=”http://www.adobe.com/2006/mxml”>
<mx:Script>
<![CDATA[
/**
* clear text clears the text of the text and the label
* it is invoked from Button btn1;
*/
public function clearText():void
{
lblName.text = '';
txtName.text = '';
}
]]>
</mx:Script>
<mx:Panel title=”App 2″>
<mx:TextInput id=”txtName” text=”hello” />
<mx:Button id=”btn1″ label=”Clear All” click=”clearText()” />
<mx:Label id=”lblName” text=”{txtName.text}”/>
</mx:Panel>
</mx:Application>
But it’s not a good idea to write down logic and layout in the same file. Just like we use separate js files for JavaScript codes with html, we can also use a separate ActionScript file for the code and put the layout code in the mxml file.
Here I have created a file and pasted the function code into it. Saved the file as app2.as in the same folder of the main.mxml file. You can use any valid names for the ActionScript file with the extension .as since it is the default extension for ActionScript files.
We can now use this file as the source of the script in this way using the source attribute:
<mx:Script source=”app2.as” />
Prev: Learning FLex3 Task2 : First action and accessing component property
Start: Learning Flex3 Task1: IDE and basic application
Next:
Learning Flex3 Task1: IDE and basic application
Posted on | June 7, 2008 | No Comments
You know xhtml? Or have some knowledge on html or xml syntax with some javascript concepts? Yes? Cool, you will find flex3 with ActionScript is almost nothing more than developing a html page with javascript. As you know there are some built in libraries in javascript like Math, ActionScript with Flex has some extra libs present. That’s all.
Now, let’s get going.
Windows users can get the FlashDevelop software from www.flashdevelop.org/. It is a nice actionscript development environment. Linux users can Eclipse and get the instructions from http://www.communitymx.com/content/article.cfm?cid=F3ECF or can use SE|PY from sourceforge.net/projects/sepy/.
I am using FlashDevelop at present while writing this.
Here is a simple typical hello world application code:
<?xml version=”1.0″?>
<mx:Application xmlns:mx=”http://www.adobe.com/2006/mxml”>
<mx:Label text=”Hello world”/>
</mx:Application>
Code Break Down:
First line the xml header. The flex files are saved as msxml format which is nothing but an xml file. So, this header must be present
Then comes mx:Application. This starts the application. Anyone who is not familiar in xmlns or the Xml Name Space can think it as a prefix to the component. This prefix helps to distinguish the components. Say, you have made a custom label component of you own and named it too Label. Now how the compiler come to know that which one is your label or application label. So to meet this problem you can use your own namespace like xmlns:smarty=”xxxx”. Now on you can use your Label like smarty:Label.
Lastly, <mx:Label text=”Hello world”/>. This self explanatory statement adds a label in the application and sets the text “Hello world”. It is very similar to html tags, right?
Generally, we use Panel as container of all elements. It not only looks little better but also help organizing the UI elements.
<?xml version=”1.0″?>
<mx:Application xmlns:mx=”http://www.adobe.com/2006/mxml”>
<mx:Panel title=”App 1″>
<mx:Label text=”Hello world”/>
</mx:Panel>
</mx:Application>
Here is how it looks now:
FlashDevelop instructions:
1. Create a new flex3 project.
2. Save the code in the Main.msxml file.
3. Press F5. simple hah?
Download file Main.mxml
Next: Learning FLex3 Task2 : First action and accessing component property
Subscribe to our feed
Search
Categories
- ActionScript
- Ajax
- Apache
- ASP.NET
- book
- C#
- Coding Style
- CSS
- CVS
- Flex
- Freelancing
- India
- j2se
- JavaScript
- Joomla
- jQuery
- Life
- Linux
- Mac OSX
- MFC
- mysql
- myworks
- orchid
- PHP
- PostgreSQL
- Project Management
- scribefire
- Shell
- Software Engineering
- Software Review
- SQA
- SQL Server
- svn
- Technology
- Testing
- TortoiseCVS
- Uncategorized
- Versioning
- Web
- Windows Vista
- গল্প