GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Mysql doesn't use primary index

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    Could You paste "show status" ? Skip variables with value = 0

    Comment


    • #17
      show status;
      +-----------------------------------+--------------+
      | Variable_name | Value |
      +-----------------------------------+--------------+
      | Aborted_clients | 2 |
      | Aborted_connects | 2 |
      | Bytes_received | 1596 |
      | Bytes_sent | 22046 |
      | Com_change_db | 1 |
      | Com_show_databases | 1 |
      | Com_show_fields | 27 |
      | Com_show_status | 1 |
      | Com_show_tables | 1 |
      | Compression | OFF |
      | Connections | 862868 |
      | Created_tmp_files | 46901 |
      | Created_tmp_tables | 3 |
      | Flush_commands | 1 |
      | Handler_read_rnd_next | 32 |
      | Handler_write | 161 |
      | Key_blocks_unused | 107350 |
      | Key_blocks_used | 11411 |
      | Key_read_requests | 54226934 |
      | Key_reads | 16980 |
      | Key_write_requests | 219634 |
      | Key_writes | 7345 |
      | Last_query_cost | 21555.899000 |
      | Max_used_connections | 380 |
      | Open_files | 232 |
      | Open_tables | 186 |
      | Qcache_free_blocks | 494 |
      | Qcache_free_memory | 130821696 |
      | Qcache_hits | 3378511 |
      | Qcache_inserts | 407703 |
      | Qcache_not_cached | 100424 |
      | Qcache_queries_in_cache | 928 |
      | Qcache_total_blocks | 2393 |
      | Questions | 6740807 |
      | Rpl_status | NULL |
      | Slave_running | OFF |
      | Ssl_session_cache_mode | NONE |
      | Table_locks_immediate | 843738 |
      | Table_locks_waited | 35763 |
      | Threads_cached | 4 |
      | Threads_connected | 8 |
      | Threads_created | 13860 |
      | Threads_running | 3 |
      | Uptime | 55618 |
      +-----------------------------------+--------------+
      251 rows in set (0.00 sec)

      Comment


      • #18
        Please run:
        Show processlist

        and paste output of the "top" with maximal cpu usage

        Comment


        • #19
          I couldn't catch a busy time, but this shows 113 % CPU usage:

          top - 17:02:26 up 58 days, 13:39, 2 users, load average: 0.58, 0.55, 0.62
          Tasks: 70 total, 1 running, 69 sleeping, 0 stopped, 0 zombie
          Cpu(s): 27.9%us, 28.5%sy, 0.0%ni, 41.6%id, 0.8%wa, 0.2%hi, 1.0%si, 0.0%st
          Mem: 2076468k total, 1304408k used, 772060k free, 203136k buffers
          Swap: 1951888k total, 0k used, 1951888k free, 843996k cached

          PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
          3049 mysql 15 0 409m 113m 4720 S 113 5.6 217:00.85 mysqld
          21761 root 15 0 2228 1120 856 R 0 0.1 0:00.19 top
          1 root 15 0 1948 644 552 S 0 0.0 0:03.44 init
          2 root RT 0 0 0 0 S 0 0.0 0:00.00 migration/0
          3 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/0
          4 root RT 0 0 0 0 S 0 0.0 0:00.00 migration/1
          5 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/1
          6 root 10 -5 0 0 0 S 0 0.0 0:00.14 events/0
          7 root 10 -5 0 0 0 S 0 0.0 0:00.01 events/1
          8 root 10 -5 0 0 0 S 0 0.0 0:01.03 khelper
          9 root 10 -5 0 0 0 S 0 0.0 0:00.00 kthread
          13 root 14 -5 0 0 0 S 0 0.0 0:00.10 kblockd/0
          14 root 10 -5 0 0 0 S 0 0.0 0:00.28 kblockd/1
          15 root 17 -5 0 0 0 S 0 0.0 0:00.00 kacpid
          105 root 11 -5 0 0 0 S 0 0.0 0:00.00 kseriod
          145 root 18 -5 0 0 0 S 0 0.0 0:00.00 kswapd0
          146 root 18 -5 0 0 0 S 0 0.0 0:00.00 aio/0
          147 root 19 -5 0 0 0 S 0 0.0 0:00.00 aio/1
          292 root 15 0 0 0 0 S 0 0.0 0:00.04 kirqd
          617 root 10 -5 0 0 0 S 0 0.0 0:00.00 khubd
          780 root 12 -5 0 0 0 S 0 0.0 0:00.00 scsi_eh_0
          794 root 11 -5 0 0 0 S 0 0.0 0:00.00 scsi_eh_1


          show processlist;
          +---------+------+---------------------+------+---------+--- ---+--------------+----------------------------------------- ------------------------------------------------------------ -+
          | Id | User | Host | db | Command | Time | State | Info |
          +---------+------+---------------------+------+---------+--- ---+--------------+----------------------------------------- ------------------------------------------------------------ -+
          | 1029605 | root | localhost | gg | Query | 0 | NULL | show processlist |
          | 1042170 | gg | 212.175.22.32:50384 | gg | Query | 1 | Sending data | SELECT news_id,news_title,news_position,news_hit,news_cat ,news_date FROM news WHERE DATE(news_date) |
          | 1042171 | gg | 212.175.22.32:50385 | gg | Sleep | 2 | | NULL |
          | 1042198 | gg | 212.175.22.32:38002 | gg | Sleep | 1 | | NULL |
          | 1042202 | gg | 212.175.22.32:38006 | gg | Query | 1 | Locked | UPDATE news SET news_hit='216' WHERE news_id='182212' |
          | 1042204 | gg | 212.175.22.32:38008 | gg | Sleep | 1 | | NULL |
          | 1042209 | gg | 212.175.22.32:38013 | gg | Query | 1 | Locked | SELECT news_id,news_position,news_title,news_hit,news_dat e FROM news WHERE news_date BETWEEN concat( |
          | 1042210 | gg | 212.175.22.32:38014 | gg | Sleep | 1 | | NULL |
          +---------+------+---------------------+------+---------+--- ---+--------------+----------------------------------------- ------------------------------------------------------------ -+
          8 rows in set (0.00 sec)

          Comment


          • #20
            Hmmm ... does cpu usage rise with hdd i/o ?
            Could You check Your raid status (if there is a raid) ?
            Does hdd run in dma mode ?

            Could You run "optimize table news" and "analyze table news" ?

            Comment


            • #21
              There is no RAID.

              I couldn't find out dma status:

              hdparm -i /dev/sda
              /dev/sda:
              HDIO_GET_IDENTITY failed: Invalid argument

              hdparm -I /dev/sda
              /dev/sda:
              HDIO_DRIVE_CMD(identify) failed: Invalid argument

              I've runned optimize/analyze many times.

              Comment


              • #22
                Please check:

                explain SELECT news_id,news_title,news_hit FROM news where news_date in ( $xxx ) ORDER BY news_date DESC LIMIT 0, 25;

                where $xxx is list of the dates eg: $xxx = "'2009-04-01', '2009-04-02', '2009-04-03'" ;

                There is a limit of the length of the query, but I think that You will be able to put two months in that list. This should limit amount of the rows.

                Comment


                • #23
                  Thank you very much for your replies. I've moved MySQL to another server. Now it's much more faster and it's CPU usage is max 50%.

                  The problem seems about sorting queries. After replacing ORDER BY news_date to ORDER BY news_id slow queries decreased. As I need to sort queries by date this won't be an absolute solution. I'm still looking for a batter way.

                  Thank you so much again.

                  Comment


                  • #24
                    You could check another thing, that query with 25 latest news is te major one, so:
                    1. fetch 24 latest rows (A)
                    2. insert new row into news table (B)
                    3. put A+B into static file
                    4. show that file to the users
                    That would give a breath to the mysql.

                    Comment


                    • #25
                      I use static files based caching for home page. But seems that it's not enough. I'll start caching other most used parts of the site starting with query you suggest.

                      Thanks.

                      Comment


                      • #26
                        To speed up queries like this:

                        SELECT news_id,news_title,news_image_small,news_summary FROM news WHERE news_position IN (8,3,7) and news_cat='1' ORDER BYnews_date DESC LIMIT 0, 2;

                        and avoid the filesort, you should add a index that contains the ORDER BY column.
                        In this case since you have the "news_position IN (8,3,7)" and that is not a constant value you should place that column last in the index. Even after the ORDER BY column.

                        ALTER TABLE news ADD INDEX news_ix_cat_date_pos(news_cat, news_date, news_position);

                        But if your query had been "WHERE a = 1 AND b = 1 ORDER BY c" then the index should be (a,b,c) since the c which is order by is least absolute (if you understand what I mean).

                        Your query:

                        ... WHERE DATE(news_date) = CURDATE()

                        Is bad because it forces an operation on each news_date field of every row.

                        Since news_date is a datetime it is enough to rewrite it to be:

                        ... WHERE news_date >= CURDATE()

                        Since CURDATE() will in this case implicitly be be converted to the datetime value '2009-04-15 00:00:00' you will get all of todays records anyway.

                        Comment


                        • #27
                          Thank you so much sterin71,
                          This kind of explanations really helpful for me.

                          Comment


                          • #28
                            I appreciate the concern which is been rose. The things need to be sorted out because it's not about the individual but it can be with everyone


                            john

                            Office Jobs
                            Jobs in the Office
                            Office Jobs Advice
                            Information for Office Jobs

                            Office Jobs

                            Comment

                            Working...
                            X