MySQL-Server + 15k DB + SHOW DATABASES -> dauert ewig ...

[djcrackman]

Hallo erst mal ;).

mysql-max-4.1.18-i686
~15.000 Datenbanken
~3.000 User

Code: Select all

mysql> SHOW DATABASESg
+-------------+
| Database    |
+-------------+
| test-user01 |
| test-user02 |
| test-user03 |
| test-user04 |
| test-user05 |
+-------------+
5 rows in set (14.74 sec)

mysql>

Das mal als Beispiel. Hardware:

Athlon 64 3000+ (Socket 939)
2x 250GB SATA (RAID1)
2GB DDR400 RAM

Mit folgendem Parameter starte ich den MySQLd:

Code: Select all

/opt/mysql_max_4.1.18_i686_main/bin/mysqld -- --basedir=/opt/mysql_max_4.1.14_i686_main --datadir=/www/mysql_main --pid-file=/var/run/mysqld_main.pid --socket=/tmp/mysql.sock --bind-address=192.124.188.36 --user=mysql --max_connections=1024 --log=/www/logs/mysql.log --language=/opt/mysql_max_4.1.14_i686_main/share/mysql/german &

Status sieht so aus:

Code: Select all

mysql> SHOW STATUSg
+----------------------------+------------+
| Variable_name              | Value      |
+----------------------------+------------+
| Aborted_clients            | 8          |
| Aborted_connects           | 500        |
| Binlog_cache_disk_use      | 0          |
| Binlog_cache_use           | 0          |
| Bytes_received             | 1368969505 |
| Bytes_sent                 | 4098790627 |
| Com_admin_commands         | 4618       |
| Com_alter_db               | 0          |
| Com_alter_table            | 268        |
| Com_analyze                | 0          |
| Com_backup_table           | 0          |
| Com_begin                  | 3155       |
| Com_change_db              | 526972     |
| Com_change_master          | 0          |
| Com_check                  | 62         |
| Com_checksum               | 0          |
| Com_commit                 | 3135       |
| Com_create_db              | 1          |
| Com_create_function        | 0          |
| Com_create_index           | 0          |
| Com_create_table           | 14866      |
| Com_dealloc_sql            | 0          |
| Com_delete                 | 156452     |
| Com_delete_multi           | 5          |
| Com_do                     | 0          |
| Com_drop_db                | 10         |
| Com_drop_function          | 0          |
| Com_drop_index             | 0          |
| Com_drop_table             | 979        |
| Com_drop_user              | 0          |
| Com_execute_sql            | 0          |
| Com_flush                  | 5          |
| Com_grant                  | 2          |
| Com_ha_close               | 0          |
| Com_ha_open                | 0          |
| Com_ha_read                | 0          |
| Com_help                   | 0          |
| Com_insert                 | 1811550    |
| Com_insert_select          | 6130       |
| Com_kill                   | 0          |
| Com_load                   | 0          |
| Com_load_master_data       | 0          |
| Com_load_master_table      | 0          |
| Com_lock_tables            | 36065      |
| Com_optimize               | 396        |
| Com_preload_keys           | 0          |
| Com_prepare_sql            | 0          |
| Com_purge                  | 0          |
| Com_purge_before_date      | 0          |
| Com_rename_table           | 0          |
| Com_repair                 | 146        |
| Com_replace                | 24891      |
| Com_replace_select         | 142        |
| Com_reset                  | 0          |
| Com_restore_table          | 0          |
| Com_revoke                 | 0          |
| Com_revoke_all             | 0          |
| Com_rollback               | 19         |
| Com_savepoint              | 0          |
| Com_select                 | 6187609    |
| Com_set_option             | 8983       |
| Com_show_binlog_events     | 0          |
| Com_show_binlogs           | 150        |
| Com_show_charsets          | 1496       |
| Com_show_collations        | 1496       |
| Com_show_column_types      | 0          |
| Com_show_create_db         | 161        |
| Com_show_create_table      | 1581       |
| Com_show_databases         | 732        |
| Com_show_errors            | 0          |
| Com_show_fields            | 4066       |
| Com_show_grants            | 133        |
| Com_show_innodb_status     | 0          |
| Com_show_keys              | 1041       |
| Com_show_logs              | 0          |
| Com_show_master_status     | 0          |
| Com_show_ndb_status        | 0          |
| Com_show_new_master        | 0          |
| Com_show_open_tables       | 0          |
| Com_show_privileges        | 0          |
| Com_show_processlist       | 3          |
| Com_show_slave_hosts       | 0          |
| Com_show_slave_status      | 0          |
| Com_show_status            | 3          |
| Com_show_storage_engines   | 60         |
| Com_show_tables            | 111587     |
| Com_show_variables         | 3051       |
| Com_show_warnings          | 0          |
| Com_slave_start            | 0          |
| Com_slave_stop             | 0          |
| Com_stmt_close             | 0          |
| Com_stmt_execute           | 0          |
| Com_stmt_prepare           | 0          |
| Com_stmt_reset             | 0          |
| Com_stmt_send_long_data    | 0          |
| Com_truncate               | 85         |
| Com_unlock_tables          | 36064      |
| Com_update                 | 800678     |
| Com_update_multi           | 66         |
| Connections                | 406167     |
| Created_tmp_disk_tables    | 79082      |
| Created_tmp_files          | 151        |
| Created_tmp_tables         | 479137     |
| Delayed_errors             | 0          |
| Delayed_insert_threads     | 0          |
| Delayed_writes             | 5945       |
| Flush_commands             | 1          |
| Handler_commit             | 22         |
| Handler_delete             | 130378     |
| Handler_discover           | 0          |
| Handler_read_first         | 1198941    |
| Handler_read_key           | 39515403   |
| Handler_read_next          | 103018984  |
| Handler_read_prev          | 742589     |
| Handler_read_rnd           | 14390491   |
| Handler_read_rnd_next      | 2001730870 |
| Handler_rollback           | 2300       |
| Handler_update             | 8935122    |
| Handler_write              | 21304201   |
| Key_blocks_not_flushed     | 0          |
| Key_blocks_unused          | 7004       |
| Key_blocks_used            | 7248       |
| Key_read_requests          | 87756687   |
| Key_reads                  | 5481333    |
| Key_write_requests         | 5211459    |
| Key_writes                 | 3769215    |
| Max_used_connections       | 37         |
| Not_flushed_delayed_rows   | 0          |
| Open_files                 | 127        |
| Open_streams               | 0          |
| Open_tables                | 65         |
| Opened_tables              | 2924374    |
| Qcache_free_blocks         | 0          |
| Qcache_free_memory         | 0          |
| Qcache_hits                | 0          |
| Qcache_inserts             | 0          |
| Qcache_lowmem_prunes       | 0          |
| Qcache_not_cached          | 0          |
| Qcache_queries_in_cache    | 0          |
| Qcache_total_blocks        | 0          |
| Questions                  | 11859961   |
| Rpl_status                 | NULL       |
| Select_full_join           | 142276     |
| Select_full_range_join     | 19712      |
| Select_range               | 227973     |
| Select_range_check         | 1551       |
| Select_scan                | 2827496    |
| Slave_open_temp_tables     | 0          |
| Slave_retried_transactions | 0          |
| Slave_running              | OFF        |
| Slow_launch_threads        | 7738       |
| Slow_queries               | 677        |
| Sort_merge_passes          | 75         |
| Sort_range                 | 202321     |
| Sort_rows                  | 47182724   |
| Sort_scan                  | 1276593    |
| Table_locks_immediate      | 11409646   |
| Table_locks_waited         | 90         |
| Threads_cached             | 0          |
| Threads_connected          | 4          |
| Threads_created            | 406158     |
| Threads_running            | 2          |
| Uptime                     | 260092     |
+----------------------------+------------+
163 rows in set (0.01 sec)

mysql>

System-Load sieht so aus:

Code: Select all

14:59:04 up 144 days, 14:00,  1 user,  load average: 1.86, 1.70, 1.51

Wenn mehrere User per phpMyAdmin sich einloggen, dann nutzt der MySQd ~80% CPU. Und das mal für ne Viertelstunde *seufz*.

Irgendjemand Lösungsvorschläge?

[alexander newald]

Was sagt

procinfo -f -n1 -D

währenddessen?

[djcrackman]

So in etwa:

Code: Select all

Linux 2.6.11-hardened-r15 (root@xxx) (gcc 3.3.6 ) #4 1CPU [xxx.(none)]

Memory:      Total        Used        Free      Shared     Buffers
Mem:       2057860     1977588       80272           0       46292
Swap:      4016232        9188     4007044

Bootup: Sun Nov  6 23:58:39 2005    Load average: 1.04 1.15 1.16 1/99 70685

user  :       0:00:00.03   3.0%  page in :     9288  disk 1:      958r     164ww
nice  :       0:00:00.00   0.0%  page out:     8312  disk 2:      226r     154ww
system:       0:00:00.14  14.0%  page act:       824
IOwait:       0:00:00.76  76.0%  page dea:        09
hw irq:       0:00:00.05   5.0%  page flt:      1082
sw irq:       0:00:00.00   0.0%  swap in :        0
idle  :       0:00:00.00   0.0%  swap out:        0
uptime: 145d 14:17:48.74         context :        05

irq  0:      1000 timer                 irq 11:         0 eth0
irq  1:         0                       irq 12:       485 eth1
irq  2:         0 cascade [4]           irq 14:      1122 ide0
irq  7:         3                       irq 15:       380 ide1

[isotopp]

mysql-max-4.1.18-i686
~15.000 Datenbanken
~3.000 User

Code: Select all

mysql> SHOW DATABASESg
+-------------+
| Database    |
+-------------+
| test-user01 |
| test-user02 |
| test-user03 |
| test-user04 |
| test-user05 |
+-------------+
5 rows in set (14.74 sec)

mysql>

Code: Select all

$ df -Th /www/mysql_main/.
$ time ls -l /www/mysql_main 2>/dev/null 1>/dev/null

Welches Dateisystem verwendest Du und wie lange dauert es, Datadir aufzulisten?

MySQL führt ein SHOW DATABASES so durch:

Code: Select all

[pid 16022] read(22, "3show databases", 15) = 15
[pid 16022] rt_sigprocmask(SIG_BLOCK, ~[RTMIN RT_1], [HUP INT QUIT PIPE ALRM TERM TSTP], 8) = 0
[pid 16022] rt_sigprocmask(SIG_SETMASK, [HUP INT QUIT PIPE ALRM TERM TSTP], NULL, 8) = 0
[pid 16022] fcntl64(22, F_SETFL, O_RDWR|O_NONBLOCK) = 0
[pid 16022] time([1143911156])          = 1143911156
[pid 16022] sched_setscheduler(16022, SCHED_OTHER, { 6 }) = -1 EINVAL (Invalid argument)
[pid 16022] open("./", O_RDONLY|O_NONBLOCK|O_LARGEFILE|O_DIRECTORY) = 23
[pid 16022] fstat64(23, {st_mode=S_IFDIR|0755, st_size=224, ...}) = 0
[pid 16022] fcntl64(23, F_SETFD, FD_CLOEXEC) = 0
[pid 16022] getdents64(23, /* 8 entries */, 4096) = 248
[pid 16022] stat64("./.", {st_mode=S_IFDIR|0755, st_size=224, ...}) = 0
[pid 16022] stat64("./..", {st_mode=S_IFDIR|0755, st_size=344, ...}) = 0
[pid 16022] stat64("./mysql", {st_mode=S_IFDIR|0750, st_size=1584, ...}) = 0
[pid 16022] stat64("./ib_logfile0", {st_mode=S_IFREG|0660, st_size=5242880, ...}) = 0
[pid 16022] stat64("./ib_logfile1", {st_mode=S_IFREG|0660, st_size=5242880, ...}) = 0
[pid 16022] stat64("./ibdata1", {st_mode=S_IFREG|0660, st_size=10485760, ...}) = 0
[pid 16022] stat64("./linux-slow.log", {st_mode=S_IFREG|0660, st_size=207, ...}) = 0
[pid 16022] stat64("./linux.pid", {st_mode=S_IFREG|0660, st_size=6, ...}) = 0
[pid 16022] getdents64(23, /* 0 entries */, 4096) = 0
[pid 16022] close(23)                   = 0

Mithin wird das datadir geöffnet und mit getdents() ausgelesen. Für jeden Namen, den getdents() zurückgibt wird ein stat64() durchgeführt.

Die Zeit, die SHOW DATABASES verbraucht, ist direkt proportional zu dem Aufwand, den Dein Dateisystem mit diesen Systemaufrufen hat.

Anlegen von 10000 Demo-Datenbanken nach Deinem Schema:

Code: Select all

linux:/export/data/demo/data # for i in {1..10000}; do name=$(printf "kunde-%05dn" $i); mkdir $name; done

Ich verwende Suse 10.0 mit Reiserfs auf LVM (Laptop Harddisk, 7200 rpm SATA):

Code: Select all

linux:/export/data/demo/data # df -Th .
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/mapper/system-data
          reiserfs    6.0G  2.4G  3.7G  39% /export/data

Mehrmaliges durchlaufen lassen von "ls -l":

Code: Select all

linux:/export/data/demo/data # time ls -l > /dev/null 2>&1

real    0m0.293s
user    0m0.136s
sys     0m0.096s
linux:/export/data/demo/data # time ls -l > /dev/null 2>&1

real    0m0.257s
user    0m0.144s
sys     0m0.084s

Zum Vergleich das SHOW DATABASES:

Code: Select all

root@localhost [(none)]> show databases;
...
10001 rows in set (0.14 sec)

[djcrackman]

@isotipp Einen Hardware-Fehler bzw zu wenig Hardware-Ressourcen schließe ich aus (im übrigen hab ich mich vertan: in dem Ding hab ich die IDE und nicht die SATA-HDD's verbaut):

Code: Select all

# hdparm -tT /dev/hda

/dev/hda:
 Timing cached reads:   2288 MB in  2.02 seconds = 1131.17 MB/sec
 Timing buffered disk reads:   90 MB in  3.02 seconds =  29.83 MB/sec
# uptime
 20:11:40 up 146 days, 19:13,  1 user,  load average: 1.66, 2.15, 2.32

Ergo: Leistungsreserven sollten genug vorhanden sein (das da Oben habe ich unter Last durchgeführt). Aber um deine Fragen zu beantworten:

- Dateisystem ist ext2
- Das Performance-Problem existiert nur, wenn ein eingeschränkter User ein SHOW DATABASES durchführt. Eingeschränkt im sinne von: er hat nur auf seine 5 MySQL-Datenbanken Zugriff. Alles andere wird ihm verweigert.

Code: Select all

# df -Th /www/mysql_main
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/md/4     ext2    223G   72G  140G  34% /www

Code: Select all

# time ls -l /www/mysql_main 2>/dev/null 1>/dev/null

real    0m0.314s
user    0m0.207s
sys     0m0.103s
# time ls -l /www/mysql_main 2>/dev/null 1>/dev/null

real    0m0.401s
user    0m0.199s
sys     0m0.127s
# time ls -l /www/mysql_main 2>/dev/null 1>/dev/null

real    0m0.346s
user    0m0.221s
sys     0m0.116s

Durchgeführt als root:

Code: Select all

mysql> SHOW DATABASESg

[...]

14693 rows in set (0.09 sec)
mysql>

Durchgeführt als eingeschränkter User "test-user":

Code: Select all

mysql> SHOW DATABASESg
+-------------+
| Database    |
+-------------+
| test-user01 |
| test-user02 |
| test-user03 |
| test-user04 |
| test-user05 |
+-------------+
5 rows in set (16.77 sec)

mysql>

Verstehst du worauf ich hinaus will? Das Ganze ist nur extrem lahm, wenn er mysql.db durchläuft. Das tut er nur, wenn ich als non-root User eingeloggt bin.

[djcrackman]

Niemand eine Idee?

[djcrackman]

Aktuell habe ich hier ~33.800 Datenbanken auf einem einzelnen System. Als root dauert ein show databases; in etwa 25 Sekunden. Als eingeschränkter User (siehe Erklärung weiter Oben) sieht es da schon trauriger aus: Unter 1 Minute (für show databases;) läuft da gar nix. Es waren also dringend Änderungen angesagt.

Folgende Schritte habe ich mittlerweile gemacht:

Code: Select all

- Aufstockung des RAMs auf 4GB (DDR400, CL2)
- Austausch des Motherboards; neues Board: Tyan Tomcat K8E
- Austausch der CPU; neue CPU: AMD Athlon64 FX-53, S939
- MySQL-System auf ein neues dediziertes RAID1 ausgelagert;
  als Datenträger wurden 2 Stk. Western Digital WD740ADFD 
  verwendet (16MB Cache, 10.000rpm, SATAII)
- Als Dateisystem wird ReiserFS verwendet
- 1 große MySQL-Instanz durch 6 kleine MySQL-Instanzen ersetzt
- InnoDB deaktiviert
- Log und Error-Log deaktiviert

Durch diese Veränderung konnte ich folgendes Erreichen:

Code: Select all

- MySQL benötigt durchschnittlich 1 GB weniger RAM
- Wenn MySQL abraucht, dann sind nicht alle User betroffen
- Lastspitzen lassen sich leichter ermitteln und dagegen wirken

In Zahlen bedeutet das: pro MySQL-Instanz habe ich 380MB Key-Cache und 150MB Query-Cache (war bisher vollständig deaktiviert) aktiviert. Für einen eingeschränkten User dauerte in show databases; nun ca 11 Sekunden. Für root (pro Instanz) ca 4 Sekunden.

Unterm Strich kann ich sagen: gefällt mir gut die aktuelle Lösung. Würde ich nur 1 MySQL-Instanz haben wollen, bliebe mir der Umstieg auf SAS-Platten nicht erspart -> das ist derzeit leider nicht finanzierbar.