Warum ist MySQL hier so langsam?

Hallo,
folgende Tabelle enthält ca. 110 Mio Datensätze.

CREATE TABLE `c` (
`sid` bigint(19) unsigned NOT NULL default '0',
`cid` mediumint(6) unsigned NOT NULL default '0',
`times` int(10) unsigned NOT NULL default '0',
`ranking` smallint(3) unsigned NOT NULL auto_increment,
UNIQUE KEY `sid_2` (`sid`,`ranking`),
KEY `sid` (`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


explain select cid, count(distinct(sid)) AS anz FROM c GROUP BY cid ORDER BY
anz;
+----+-------------+----------------+------+---------------+ ------+---------
+------+-----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+----------------+------+---------------+ ------+---------
+------+-----------+---------------------------------+
| 1 | SIMPLE | contents_large | ALL | NULL | NULL | NULL
| NULL | 112778690 | Using temporary; Using filesort |
+----+-------------+----------------+------+---------------+ ------+---------
+------+-----------+---------------------------------+

Diese Query dauert ca. 26min. Wobei bei "show processlist" diese Query
mindestens 15 Minuten im Status "copying to tmp table on disk" stehen
bleibt. Aber im Tmp Verzeichnis passiert nichts.

Zum Server: 16 GB Ram, RAID-10 aus 6x SAS 15.000 U/min, 2,66 GHz QuadCore,
also nicht gerade eine langsame Maschine.
Hier noch die my.cnf:

skip-locking
set-variable = key_buffer=2048M
set-variable = max_allowed_packet=256M
set-variable = table_cache=256
set-variable = sort_buffer=256M
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=256M
set-variable = tmp_table_size=256M
set-variable = record_buffer=2M
set-variable = query_cache_size=16M
set-variable = thread_stack=256K
set-variable = connect_timeout=60


Dass MySQL sich einen *** um die 3 weiteren Cores schenkt, ist schon klar.
MySQL Version ist 4.1.22 unter RHEL4.

Mich wundert warum MySQL ewig im Status "copying to tmp table hängt". Zum
Vergleich habe ich das mal mit einer speziellen Clusterversion von Postgres
auf der gleichen Maschine getestet. Die war mindestens 5-6x schneller als
MySQL.

Der Server swappt natürlich nicht.

Christian

Re: Warum ist MySQL hier so langsam?

Claus Reibenstein wrote:
> Christian Schmelzer schrieb:
>
>> folgende Tabelle enthält ca. 110 Mio Datensätze.
>
> Nicht gerade wenig.
>
>> CREATE TABLE `c` (
>> `sid` bigint(19) unsigned NOT NULL default '0',
>> `cid` mediumint(6) unsigned NOT NULL default '0',
>> `times` int(10) unsigned NOT NULL default '0',
>> `ranking` smallint(3) unsigned NOT NULL auto_increment,
>> UNIQUE KEY `sid_2` (`sid`,`ranking`),
>> KEY `sid` (`sid`)
>
> Welchen Sinn soll es haben, sid in 2 Keys unterzubringen?
>

Hallo,
ja, vom Prinzip ist das natürlich unsinnig. Aber Mysql ist (leider)
teilweise schneller wenn noch ein einzelner Index vorhanden ist. Die
Beispiel Query ist ja auch nicht die einzige Query. Es gibt noch andere mit
Verknüpfungen zu anderen Tabellen. Da muss ich dann auch abwegen wo man noch
kombinierte Indices nutzen kann, denn dann brauche ich für alle möglichen
Kombinationen noch jeweils einen Extra Index.
Ich frage mich halt was Mysql macht während dort ewig "copying to tmp table"
steht. Das Datenfile ist nur 2 GB groß. Dieser Server müsste theoretisch das
gesamte File schneller im Ram haben und durchscannen.
Der Vergleich mit Postgres mit identischen Indices war 5-6x schneller. Das
war aber auch eine spezielle Version die richtig Feuer auf allen Cores
machen kann, d.h. die führt die Query parallel aus.

Christian

Re: Warum ist MySQL hier so langsam?

Claus Reibenstein wrote:
> Christian Schmelzer schrieb:
>
>> folgende Tabelle enthält ca. 110 Mio Datensätze.
>
> Nicht gerade wenig.
>
>> CREATE TABLE `c` (
>> `sid` bigint(19) unsigned NOT NULL default '0',
>> `cid` mediumint(6) unsigned NOT NULL default '0',
>> `times` int(10) unsigned NOT NULL default '0',
>> `ranking` smallint(3) unsigned NOT NULL auto_increment,
>> UNIQUE KEY `sid_2` (`sid`,`ranking`),
>> KEY `sid` (`sid`)
>
> Welchen Sinn soll es haben, sid in 2 Keys unterzubringen?
>
>> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>>
>>
>> explain select cid, count(distinct(sid)) AS anz FROM c GROUP BY cid
>> ORDER BY anz;
>>
+----+-------------+----------------+------+---------------+ ------+---------
>> +------+-----------+---------------------------------+
>>> id | select_type | table | type | possible_keys | key |
>>> key_len ref | rows | Extra |
>>
+----+-------------+----------------+------+---------------+ ------+---------
>> +------+-----------+---------------------------------+
>>> 1 | SIMPLE | contents_large | ALL | NULL | NULL |
>>> NULL NULL | 112778690 | Using temporary; Using filesort |
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> Alarm!
>

Hm,
nun habe ich

CREATE TABLE `c` (
`sid` bigint(19) unsigned NOT NULL default '0',
`cid` mediumint(6) unsigned NOT NULL default '0',
`times` int(10) unsigned NOT NULL default '0',
`ranking` smallint(3) unsigned NOT NULL auto_increment,
UNIQUE KEY `sid_2` (`sid`,`ranking`),
KEY `cid` (`cid`,`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



explain select cid, count(distinct(sid)) AS anz FROM c GROUP BY cid ORDER BY
anz\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: c
type: index
possible_keys: NULL
key: cid
key_len: 11
ref: NULL
rows: 112778690
Extra: Using index; Using temporary; Using filesort

Ergebnis: 23:10min, d.h. fast kein Unterschied.

D.h. immer noch 4-5x langsamer als die getunte Postgres Version. Mir kommt
Mysql eh sehr langsam vor wenn richtig Daten vorhanden sind. Bei weniger
Daten ist Mysql schneller (< 5 Mio Datensätze). Das ist aber nicht gerade
viel.

Christian

Re: Warum ist MySQL hier so langsam?

Christian Schmelzer wrote:
> Andreas Kretschmer wrote:
>> begin Christian Schmelzer schrieb:
>>> D.h. immer noch 4-5x langsamer als die getunte Postgres Version. Mir
>>> kommt Mysql eh sehr langsam vor wenn richtig Daten vorhanden sind.
>>> Bei weniger Daten ist Mysql schneller (< 5 Mio Datensätze). Das ist
>>> aber nicht gerade viel.
>>
>> MySQL kann pro Table und Select nur einen Index nutzen. Da ist PG
>> einfach besser dran, und mit Bitmap Index Scan, also seit 8.1, ist
>> das echt sehr schnell. Ich verkneif mir jetzt mal Links zu
>> Benchmark-Vergleichen MySQL <--> PG zu nennen, das wäre wohl nicht
>> angenehm in dieser NG.
>>
>>
>> Mich würde aber mal interessieren, was Du da für eine PG-Version hast
>> und was da getunt ist.
>>
>
> Greenplum DB

Da laufen im Prinzip auf dem QuadCore 3-4 Instanzen, die gleichzeitig die
Query abarbeiten. Ich habe hier bis jetzt selber noch gar nichts getunt und
sie hat Mysql bereits um Längen geschlagen.

Christian