Shaikh Sonny Aman’s Blog

Lets learn and share!

MySQL DBA Preparation: Mysql Architecture

Posted on | February 2, 2009 |

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

Category: mysql

Comments

2 Responses to “MySQL DBA Preparation: Mysql Architecture”

  1. foysal
    November 24th, 2009 @ 12:43 pm

    aman vai

    kemon achen?? apner PHP programming er ki obostha?? kothai ki position e kormoroto achen??

    foysal

  2. admin
    November 24th, 2009 @ 1:00 pm

    valo asi,
    korun obostha
    trippert lab e IT consulted/sr. software engineer hisabe kormoroto asi.
    thanks :)

    amar question,
    tumi/apni kon foysal [vai] ? :)

Leave a Reply