Markus Rechberger / My SQLCertification
 
StartSeite | MarkusRechberger/ | Neues | TestSeite | ForumSeite | Teilnehmer | Kategorien | Index | Hilfe | Einstellungen | Ändern

MySQL Notes

just some notes about the mysql certification come here, reading and learning everything from a book I bought. Guy who pointed me out to make that certification is Geert Vanderkelen who now works for MySQL

the very beginning of that book ...

mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 4.0.24_Debian-10-log |
+----------------------+
1 row in set (0.03 sec)

mysql> SELECT foo from bar
    -> where \c
mysql>

\c will not execute the query and result with a new prompt


semicolon and \g are equivalent

mysql> select version(), now();
+----------------------+---------------------+
| version()            | now()               |
+----------------------+---------------------+
| 4.0.24_Debian-10-log | 2005-08-07 23:21:37 |
+----------------------+---------------------+
1 row in set (0.02 sec)

mysql> select version(), now()\g
+----------------------+---------------------+
| version()            | now()               |
+----------------------+---------------------+
| 4.0.24_Debian-10-log | 2005-08-07 23:21:43 |
+----------------------+---------------------+
1 row in set (0.00 sec)


status

mysql> status
--------------
mysql  Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i386)

Connection id:          33
Current database:       
Current user:           revenger@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Server version:         4.0.24_Debian-10-log
Protocol version:       10
Connection:             Localhost via UNIX socket
Client characterset:    latin1
Server characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 1 day 13 hours 52 min 32 sec

Threads: 1  Questions: 133  Slow queries: 0  Opens: 59  Flush tables: 1  Open tables: 53  Queries per second avg: 0.001
--------------

short form is \s for displaying the status

for getting long command names work in the middle of sql queries (after the first char of a line) invoke mysql with --named-commands

the option --safe-updates is for n00bs :)


if an evil table is full .. like atm somewhere where I have to fix it.. blah

SHOW TABLE STATUS FROM database LIKE 'table_name';
+-----------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
| Name      | Type   | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Create_options | Comment |
+-----------+--------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
| table_name | MyISAM | Dynamic    | 618186 |           6947 |  4294967292 |      4294967295 |     36902912 |         0 |        3535739 | 2005-01-16 13:11:21 | 2005-08-10 20:08:05 | NULL       |                |         |
+-----------+--------+------------+--------+----------------+-----

looking at these evil 618186 .. there's a ttl in it but it didn't get erased so ..

4294967295/1024
4194303.99902343750000000000
4294967295/1024/1024
4095.99999904632568359375 <-- MB

delete from table_name where ttl<now();

and since that fun didn't work at all..

mysql -h hostname -e "show processlist" | grep -v Sleep
| 31767 | root     | localhost                      | flights          | Query   | <toolong>   | NULL         | doing some query       |

truncate table_name;
OK

(renaming such tables could be useful as well depends what they're needed for .. my case was clear enough..)

another way would be to extend the table

for example:

ALTER TABLE table_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn; (like it's descriped on mysql.com in the documentation)


strange that the certification book doesn't show up replication issues, and detail practical cases like the high performance mysql book does... so ongoing the mysql book lost my interest it's more worth to read the other book

ACID (=> Atomicity Consistency Isolation Durability)

read more about it on wikipedia.. or get the book high performance mysql

http://en.wikipedia.org/wiki/ACID


Tabellentypen ändern

alter table mytable type=bdb


MyISAM? Raid Tables

create table mytable(
 a integer not null primary key,
 b char(18) not null
) RAID_TYPE=STRIPED RAID_CHUNKS=4 RAID_CHUNKSIZE=16;


MyISAM? Merge Tables

create table mytable0(
  a integer not null primary key,
  b char(18) not null
);

create table mytable1(
  a integer not null primary key,
  b char(18) not null
);

create table mytable2(
 a integer not null primary key,
 b char(18) not null
);

create table mytable(
 a integer not null primary key,
 b char(18) not null
) TYPE=MERGE UNION=(mytable0,mytable1,mytable2) INSERT_METHOD=LAST;


mysql benchmarking tools

mysql benchmark suite - (funktioniert auch mit anderen datenbanken)

super-smack -stress test tool

mybench -- stress test (einfacher als super-smack)


fultextindexe sind vorzuziehen bevor man eine spalte mit like durchsucht...


mysql only uses one index when searching a record


primary key unter mysql löschen .. alter table drop primary key


basedir .. basedir von mysql
datadir .. db dir von mysql

beides ist in der my.cnf notwendig damit z.b mysqldumpslow funktioniert

my_print_defaults mysqld gibt die gesetzten variablen aus


TQC .. Tagged Queuing command SCSI, speedup bei festplattenzugriffen


use index, force index example (even if it's useless in that case)

mysql> explain select * from fragen use index(typ) where  punkte>0 and typ<1; 
+--------+------+---------------+------+---------+------+------+-------------+
| table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+--------+------+---------------+------+---------+------+------+-------------+
| fragen | ALL  | typ           | NULL |    NULL | NULL |    3 | Using where |
+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from fragen force index(typ) where  punkte>0 and typ<1;
+--------+------+---------------+------+---------+------+------+-------------+
| table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+--------+------+---------------+------+---------+------+------+-------------+
| fragen | ALL  | typ           | NULL |    NULL | NULL |    3 | Using where |
+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)


temptables ... (also counts tables which get created during some queries not only by create temporary table..)

mysql> show status like 'created_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 11    |
| Created_tmp_tables      | 33575 |
| Created_tmp_files       | 0     |
+-------------------------+-------+
3 rows in set (0.00 sec)


installing a mysql db

debian:/proc/22549# /usr/sbin/mysqld  --defaults-file=/etc/mysql/my2.cnf
050910 17:17:31  InnoDB: Started
050910 17:17:31 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist
050910 17:17:31 Aborting

050910 17:17:31  InnoDB: Starting shutdown...
050910 17:17:34  InnoDB: Shutdown completed
050910 17:17:34 /usr/sbin/mysqld: Shutdown Complete
----
debian:/proc/22549# mysql_install_db --defaults-file=/etc/mysql/my2.cnf
WARNING: The host 'debian' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Preparing db table
Preparing host table
Preparing user table
Preparing func table
Preparing tables_priv table
Preparing columns_priv table
Installing all prepared tables
050910 17:20:51 /usr/sbin/mysqld: Shutdown Complete

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h debian password 'new-password'
See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe 
You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd sql-bench ; perl run-all-tests

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com

----
debian:/proc/22549# /usr/sbin/mysqld  --defaults-file=/etc/mysql/my2.cnf
050910 17:20:56  InnoDB: Started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.24_Debian-10-log'  socket: '/var/run/mysqld/mysqld1.sock'  port: 3307  Source distribution


localhost versus 127.0.0.1 the special case

debian:/home/revenger# mysql -P 3307 -h localhost -u root mysql
ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
debian:/home/revenger# mysql -P 3307 -h 127.0.0.1 -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.24_Debian-10-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 


binär log untersuchen

debian:/var/log/mysql# mysqlbinlog mysql-bin.302
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 4
#050910 17:20:51 server id 1  log_pos 4         Start: binlog v 3, server v 4.0.24_Debian-10-log created 050910 17:20:51 at startup
# at 79
#700101  1:00:00 server id 1  log_pos 79        Query   thread_id=1     exec_time=1126365651    error_code=0
use mysql;
SET TIMESTAMP=0;


flush tables with read lock

reset master


...
05:25 < ajdonnison> revenger_, it is HCORLIM, SCORLIM, HVMMLIM and SVMMLIM.
...
05:55 < arjenAU> yes. and then my point would be tat that's probably a waste of memory which in fact costs CPU cycles to use.
05:55 < lokus> max for key-buffer is 4G actaully
05:55 < arjenAU> regardless
05:56 < arjenAU> revenger_: did you actually look at the server stats to work out how much mem for key buffer would make sense?
05:56 < revenger_> arjenAU: which server stats?
05:56 < arjenAU> SHOW STATUS
05:56 < arjenAU> just messing with buffers doesn't make for a better setup.
05:57 < arjenAU> considering you're someone who doesn't know how to swim, you will still drown regardless of the pool size ;-)
05:57  * arjenAU makes up another silly analogy
05:57 < arjenAU> in this case, you want to check key_reads vs key_read_requests
05:57 < revenger_> | Key_read_requests        | 863239    |
05:57 < revenger_> | Key_reads                | 11487     |
05:58 < arjenAU> you want to have about 97% hitrate. if less, increase the buffer. if more, you're wasting memory.
05:58 < arjenAU> 0.01 you're wasting mem
05:59 < lokus> there is also key_blocks_used and unused
05:59 < arjenAU> indulge me. set it to say 256MB, FLUSH STATUS, and check again in a few days
05:59 < revenger_>  Key_blocks_used          | 11516     |
05:59 < arjenAU> please note that you can tweak these settings on the live server, no restarts needed.
05:59 < arjenAU> a block is 1K
05:59 < revenger_> |ok
05:59 < revenger_> -l*
06:00 -!- Jivedue  has quit []
06:00 < arjenAU> likewise, for the sort and read buffer... you want to know what your apps need.
06:00 < arjenAU> mem takes time to allocate.
06:00 < arjenAU> so keeping a relatively small default is good, then you can increase WITHIN a session when needed for a particular query.


http://www.livejournal.com/users/arjen_lentz/


a multi-mastered seems a bit weak.. it relies on duplicate key errors but should work..


memory calc: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections

changing settings onfly
mysql> set global key_buffer_size=256000000;
Query OK, 0 rows affected (0.28 sec)

mysql> set global read_buffer_size=2000000;
Query OK, 0 rows affected (0.00 sec)


StartSeite | MarkusRechberger/ | Neues | TestSeite | ForumSeite | Teilnehmer | Kategorien | Index | Hilfe | Einstellungen | Ändern
Text dieser Seite ändern (zuletzt geändert: 14. September 2005 9:56 (diff))
Suchbegriff: gesucht wird
im Titel
im Text