Re: count * performance issue

От: Arjen van der Meijden
Тема: Re: count * performance issue
Дата: ,
Msg-id: 47D2488F.1000801@tweakers.net
(см: обсуждение, исходный текст)
Ответ на: Re: count * performance issue  (Craig James)
Список: pgsql-performance

Скрыть дерево обсуждения

count * performance issue  ("sathiya psql", )
 Re: count * performance issue  (Chris, )
 Re: count * performance issue  ("A. Kretschmer", )
  Re: count * performance issue  ("Shoaib Mir", )
   Re: count * performance issue  ("sathiya psql", )
    Re: count * performance issue  ("Shoaib Mir", )
     Re: count * performance issue  ("sathiya psql", )
      Re: count * performance issue  ("Shoaib Mir", )
   Re: count * performance issue  (Mark Mielke, )
    Re: count * performance issue  ("A. Kretschmer", )
     Re: count * performance issue  ("sathiya psql", )
      Re: count * performance issue  ("A. Kretschmer", )
      Re: count * performance issue  (Dave Cramer, )
 Fwd: count * performance issue  ("sathiya psql", )
 Re: count * performance issue  ("A. Kretschmer", )
  Re: count * performance issue  ("sathiya psql", )
   Re: count * performance issue  ("A. Kretschmer", )
   Re: count * performance issue  (Greg Smith, )
  Re: count * performance issue  (Mark Mielke, )
 Re: count * performance issue  (Dave Cramer, )
  Re: count * performance issue  ("sathiya psql", )
   Re: count * performance issue  ("Harald Armin Massa", )
   Re: count * performance issue  ("A. Kretschmer", )
   Re: count * performance issue  (Alvaro Herrera, )
   Re: count * performance issue  (Greg Smith, )
    Re: count * performance issue  (Craig James, )
     Re: count * performance issue  (Bruce Momjian, )
     Re: count * performance issue  ("Steinar H. Gunderson", )
      Re: count * performance issue  (Greg Smith, )
       Re: count * performance issue  ("Dave Page", )
     Re: count * performance issue  (Bill Moran, )
     Re: count * performance issue  ("Mark Lewis", )
     Re: count * performance issue  ("D'Arcy J.M. Cain", )
     Re: count * performance issue  (Tom Lane, )
      Re: count * performance issue  (Craig James, )
       Re: count * performance issue  (paul rivers, )
       Re: count * performance issue  (Mark Kirkwood, )
       Benchmark: Dell/Perc 6, 8 disk RAID 10  (Craig James, )
        Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  ("Scott Marlowe", )
        Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  ("Joshua D. Drake", )
         Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Craig James, )
          Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Greg Smith, )
           Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  ("Joshua D. Drake", )
            Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Greg Smith, )
             Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Michael Stone, )
        Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  ("Justin Graf", )
         Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Doug Knight, )
          Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Craig James, )
         Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Will Weaver, )
          Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  ("Justin Graf", )
         Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Craig James, )
      Re: count * performance issue  (Josh Berkus, )
       Re: count * performance issue  (Tom Lane, )
        Re: count * performance issue  (Greg Smith, )
         Re: count * performance issue  (Tom Lane, )
        Re: count * performance issue  (Gregory Stark, )
         Re: count * performance issue  ("Joe Mirabal", )
          Re: count * performance issue  (Bill Moran, )
           Re: count * performance issue  (Greg Smith, )
          Re: count * performance issue  ("Scott Marlowe", )
          Re: count * performance issue  (Greg Smith, )
           Re: count * performance issue  ("Robins Tharakan", )
            Re: count * performance issue  (Mark Mielke, )
            Re: count * performance issue  ("Joshua D. Drake", )
            Re: count * performance issue  ("Scott Marlowe", )
             Re: count * performance issue  (Albert Cervera Areny, )
            Re: count * performance issue  (Bill Moran, )
             Re: count * performance issue  (Matthew, )
              Re: count * performance issue  (Tino Wildenhain, )
               Re: count * performance issue  (Matthew, )
                Re: count * performance issue  ("Heikki Linnakangas", )
                Re: count * performance issue  (Andrew Sullivan, )
       Re: count * performance issue  (Mark Mielke, )
        Re: count * performance issue  (paul rivers, )
     Re: count * performance issue  (Mark Kirkwood, )
     Re: count * performance issue  (Arjen van der Meijden, )
  Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  ("Scott Marlowe", )
   Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  ("Scott Marlowe", )
   Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Jesper Krogh, )
   Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Justin, )
    Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Greg Smith, )
     Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Justin, )
      Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Justin, )
       Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Dave Cramer, )
        Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Craig James, )
         Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Craig James, )
         Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Dave Cramer, )
          Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  ("Scott Marlowe", )
          Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Justin, )
           Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Tino Wildenhain, )
           Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  ("Andrej Ricnik-Bay", )
           Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Craig Ringer, )
            Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Justin, )
         Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Justin, )
         Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Michael Stone, )
  Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Dave Cramer, )
   Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Justin, )
    Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Craig James, )
     Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Justin, )
      Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Enrico Sirola, )
       Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  ("Scott Marlowe", )
      Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (James Mansion, )
 Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  ("justin", )
  Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  ("Scott Marlowe", )
   Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  (Jesper Krogh, )
 Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  ("justin", )
  Re: Benchmark: Dell/Perc 6, 8 disk RAID 10  ("Scott Marlowe", )

On 6-3-2008 16:28 Craig James wrote:
> On the one hand, I understand that Postgres has its architecture, and I
> understand the issue of row visibility, and so forth.  On the other
> hand, my database is just sitting there, nothing going on, no
> connections except me, and... it takes FIFTY FIVE SECONDS to count 20
> million rows, a query that either Oracle or MySQL would answer in a
> fraction of a second.  It's hard for me to believe there isn't a better
> way.

Can you explain to me how you'd fit this in a fraction of a second?

mysql> select count(*) from messages;
+----------+
| count(*) |
+----------+
| 21908505 |
+----------+
1 row in set (8 min 35.09 sec)

This is a table containing the messages on forumtopics and is therefore
relatively large. The hardware is quite beefy for a forum however (4
3Ghz cores, 16GB, 14+1 disk raid5). This table has about 20GB of data.

If I use a table that contains about the same amount of records as the
above and was before this query probably much less present in the
innodb-buffer (but also less frequently touched by other queries), we
see this:

mysql> select count(*) from messagesraw;
+----------+
| count(*) |
+----------+
| 21962804 |
+----------+
1 row in set (5 min 16.41 sec)

This table is about 12GB.

In both cases MySQL claimed to be 'Using index' with the PRIMARY index,
which for those tables is more or less identical.

Apparently the time is still table-size related, not necessarily
tuple-count related. As this shows:

mysql> select count(*) from articlestats;
+----------+
| count(*) |
+----------+
| 34467246 |
+----------+
1 row in set (54.14 sec)

that table is only 2.4GB, but contains 57% more records, although this
was on another database on a system with somewhat different specs (8
2.6Ghz cores, 16GB, 7+7+1 raid50), used a non-primary index and I have
no idea how well that index was in the system's cache prior to this query.

Repeating it makes it do that query in 6.65 seconds, repeating the
12GB-query doesn't make it any faster.

Anyway, long story short: MySQL's table-count stuff also seems
table-size related. As soon as the index it uses fits in the cache or it
doesn't have to use the primary index, it might be a different story,
but when the table(index) is too large to fit, it is quite slow.
Actually, it doesn't appear to be much faster than Postgresql's (8.2)
table-based counts. If I use a much slower machine (2 2Ghz opterons, 8GB
ddr memory, 5+1 old 15k rpm scsi disks in raid5) with a 1GB, 13M record
table wich is similar to the above articlestats, it is able to return a
count(*) in 3 seconds after priming the cache.

If you saw instantaneous results with MySQL, you have either seen the
query-cache at work or where using myisam. Or perhaps with a fast
system, you had small tuples with a nice index in a nicely primed cache.

Best regards,

Arjen


В списке pgsql-performance по дате сообщения:

От: Arjen van der Meijden
Дата:
Сообщение: Re: count * performance issue
От: Alvaro Herrera
Дата:
Сообщение: Re: Re: Confirmaçã de envio / Sending confirmation(captchaid:13266b402f09)