Please Help what is wrong with the setting

  • Filter
  • Time
  • Show
Clear All
new posts

  • Please Help what is wrong with the setting

    I have 10 different web sites access to sql database and the hosting company is saying that there is a problem with the indexes these are the configuration below.

    Will you please tell me how to fix it I have 15 days either i fix the problem or i will be thrown from the hosting company.

    thank you


    Server variables and settings
    Variable Session value / Global value
    back log 50
    basedir /
    binlog cache size 32,768
    bulk insert buffer size 8,388,608
    character set client utf8
    (Global value) latin1
    character set connection utf8
    (Global value) latin1
    character set database latin1
    character set results utf8
    (Global value) latin1
    character set server latin1
    character set system utf8
    character sets dir /usr/share/mysql/charsets/
    collation connection utf8_unicode_ci
    (Global value) latin1_swedish_ci
    collation database latin1_swedish_ci
    collation server latin1_swedish_ci
    concurrent insert ON
    connect timeout 5
    datadir /var/lib/mysql/
    date format %Y-%m-%d
    datetime format %Y-%m-%d %H:%i:%s
    default week format 0
    delay key write ON
    delayed insert limit 100
    delayed insert timeout 300
    delayed queue size 1,000
    expire logs days 0
    flush OFF
    flush time 0
    ft boolean syntax + -><()~*:""&|
    ft max word len 84
    ft min word len 4
    ft query expansion limit 20
    ft stopword file (built-in)
    group concat max len 1,024
    have archive NO
    have bdb NO
    have blackhole engine NO
    have compress YES
    have crypt YES
    have csv NO
    have example engine NO
    have geometry YES
    have innodb YES
    have isam NO
    have merge engine YES
    have ndbcluster NO
    have openssl NO
    have query cache YES
    have raid NO
    have rtree keys YES
    have symlink YES
    init connect
    init file
    init slave
    innodb additional mem pool size 1,048,576
    innodb autoextend increment 8
    innodb buffer pool awe mem mb 0
    innodb buffer pool size 209,715,200
    innodb data file path ibdata1:10M:autoextend
    innodb data home dir
    innodb fast shutdown ON
    innodb file io threads 4
    innodb file per table OFF
    innodb flush log at trx commit 1
    innodb flush method
    innodb force recovery 0
    innodb lock wait timeout 50
    innodb locks unsafe for binlog OFF
    innodb log arch dir
    innodb log archive OFF
    innodb log buffer size 1,048,576
    innodb log file size 5,242,880
    innodb log files in group 2
    innodb log group home dir ./
    innodb max dirty pages pct 90
    innodb max purge lag 0
    innodb mirrored log groups 1
    innodb open files 300
    innodb table locks ON
    innodb thread concurrency 8
    interactive timeout 28,800
    join buffer size 3,141,632
    key buffer size 629,145,600
    key cache age threshold 300
    key cache block size 1,024
    key cache division limit 100
    language /usr/share/mysql/english/
    large files support ON
    lc time names en_US
    license GPL
    local infile ON
    locked in memory OFF
    log OFF
    log bin OFF
    log error
    log slave updates OFF
    log slow queries OFF
    log update OFF
    log warnings 1
    long query time 10
    low priority updates OFF
    lower case file system OFF
    lower case table names 0
    max allowed packet 1,048,576
    max binlog cache size 4,294,967,295
    max binlog size 1,073,741,824
    max connect errors 10
    max connections 45
    max delayed threads 20
    max error count 64
    max heap table size 16,777,216
    max insert delayed threads 20
    max join size 4,294,967,295
    max length for sort data 1,024
    max prepared stmt count 16,382
    max relay log size 0
    max seeks for key 4,294,967,295
    max sort length 1,024
    max tmp tables 32
    max user connections 15
    max write lock count 4,294,967,295
    myisam data pointer size 4
    myisam max extra sort file size 2,147,483,648
    myisam max sort file size 2,147,483,647
    myisam recover options OFF
    myisam repair threads 1
    myisam sort buffer size 8,388,608
    myisam stats method nulls_unequal
    net buffer length 16,384
    net read timeout 30
    net retry count 10
    net write timeout 60
    new OFF
    old passwords OFF
    open files limit 4,096
    pid file /var/lib/mysql/alexis.internetwebserver.net.pid
    port 3,306
    preload buffer size 32,768
    prepared stmt count 0
    protocol version 10
    query alloc block size 8,192
    query cache limit 20,971,520
    query cache min res unit 4,096
    query cache size 209,715,200
    query cache type ON
    query cache wlock invalidate OFF
    query prealloc size 8,192
    range alloc block size 2,048
    read buffer size 5,238,784
    read only OFF
    read rnd buffer size 262,144
    relay log purge ON
    relay log space limit 0
    rpl recovery rank 0
    secure auth OFF
    server id 0
    skip external locking ON
    skip networking OFF
    skip show database OFF
    slave net timeout 3,600
    slave transaction retries 0
    slow launch time 2
    socket /var/lib/mysql/mysql.sock
    sort buffer size 5,242,872
    sql mode
    sql notes ON
    sql warnings ON
    storage engine MyISAM
    sync binlog 0
    sync frm ON
    sync replication 0
    sync replication slave id 0
    sync replication timeout 0
    system time zone EDT
    table cache 512
    table type MyISAM
    thread cache size 50
    thread stack 196,608
    time format %H:%i:%s
    time zone SYSTEM
    tmp table size 33,554,432
    transaction alloc block size 8,192
    transaction prealloc size 4,096
    tx isolation REPEATABLE-READ
    version 4.1.22-standard
    version comment MySQL Community Edition - Standard (GPL)
    version compile machine i686
    version compile os pc-linux-gnu
    wait timeout 28,800

    Variable Value
    Flush_commands 37
    Slow_queries 2,873
    Begin Handler
    Variable Value
    Handler_commit 561
    Handler_delete 646 k
    Handler_discover 0
    Handler_read_first 3,513 k
    Handler_read_key 777 M
    Handler_read_next 387 M
    Handler_read_prev 9,935 k
    Handler_read_rnd 54 M
    Handler_read_rnd_next 1,331 M
    Handler_rollback 6,583
    Handler_update 9,574 k
    Handler_write 107 M
    Begin Query cache
    Variable Value
    Flush query cache
    Qcache_free_blocks 4,553
    Qcache_free_memory 23 M
    Qcache_hits 3,704.61 M
    Qcache_inserts 121 M
    Qcache_lowmem_prunes 2,604 k
    Qcache_not_cached 4,470 k
    Qcache_queries_in_cache 78 k
    Qcache_total_blocks 164 k
    Begin Threads
    Variable Value
    Show processes
    Slow_launch_threads 0
    Threads_cached 37
    Threads_connected 10
    Threads_created 46
    Threads_running 5
    Threads_cache_hitrate_% 100.00%
    Begin Binary log
    Variable Value

    Binlog_cache_disk_use 0
    Binlog_cache_use 0
    Begin Temporary data
    Variable Value
    Created_tmp_disk_tables 246 k
    Created_tmp_files 8,352
    Created_tmp_tables 781 k
    Begin Delayed inserts
    Variable Value
    Delayed_errors 0
    Delayed_insert_threads 1
    Delayed_writes 66 k
    Not_flushed_delayed_rows 0
    Begin Key cache
    Variable Value

    Key_blocks_not_flushed 0
    Key_blocks_unused 539 k
    Key_blocks_used 19 k
    Key_read_requests 2,115 M
    Key_reads 3,888 k
    Key_write_requests 6,565 k
    Key_writes 3,366 k
    Key_buffer_fraction_% 12.21%
    Key_write_ratio_% 51.27%
    Key_read_ratio_% 0.18%
    Begin Joins
    Variable Value
    Select_full_join 246 k
    Select_full_range_join 719
    Select_range 318 k
    Select_range_check 6,395
    Select_scan 4,591 k
    Begin Replication
    Variable Value
    Show slave hosts Show slave status
    Rpl_status NULL
    Slave_open_temp_tables 0
    Slave_retried_transactions 0
    Slave_running OFF
    Begin Sorting
    Variable Value
    Sort_merge_passes 4,174
    Sort_range 250 k
    Sort_rows 60 M
    Sort_scan 1,162 k
    Begin Tables
    Variable Value
    Flush (close) all tables Show open tables
    Open_tables 512
    Opened_tables 1,102 k
    Table_locks_immediate 247 M
    Table_locks_waited 100 k
    Variable Value
    Open_files 981
    Open_streams 0

  • #2
    You should fix Your tables and queries.

    Slow_queries 2,873

    Almost 3k of the slow queries (at least 10 seconds of execution)

    Handler_read_rnd 54 M

    The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.

    Handler_read_rnd_next 1,331 M

    The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.


    • #3
      Yes sir thank you for your help but one problem I dont know how to fix them can you please help me little more.



      I got the cache size larger and play some I dont event know what I did but i got this

      Select_full_join 7,745
      Select_full_range_join 25
      Select_range 16 k T
      Select_range_check 80

      Handler_read_rnd 2,000 k


      • #4
        You need to create indexes on your tables.
        Especially for your JOIN queries.

        Do you know about indexes?
        Otherwise read here to get some more information: http://20bits.com/articles/interview-questions-database-inde xes/.

        So what you need to do is:
        1. Find out which queries that are a problem.
        If you have a small site then you probably don't have that many queries and then you can just search for them in the source code.
        If you have a large site then I suggest the MySQL http://dev.mysql.com/doc/refman/5.4/...query-log.html" target="_blank">slow-query-log[/url] if you can use it for your webhosting company.
        And start by looking at which tables that contain a lot of records. Very often on a site you only have a couple of tables that really contains a lot of records, the rest is usually quite small, so focus you energy with indexes for these large tables where they will be put to most use.

        Check the design of your database.
        Either SHOW CREATE TABLE yourTable; or SHOW INDEXES yourTable or using some GUI.

        Start with adding indexes for the columns that are part of the JOIN condition.
        This is most important because it is causing a lot of load on your database as it looks like.
        So if you have a query that look like:

        SELECT ...FROM yourTableAINNER JOIN yourTableB ON yourTableA.id = yourTableB.parent_id

        Then you should index _both_ yourTable.id and yourTableB.parent_id.

        ALTER TABLE yourTableA ADD INDEX yourTableA_ix_id(id);ALTER TABLE yourTableB ADD INDEX yourTableB_ix_parentid(parent_id);

        If you start with this you can at least get rid of the table scans with the joins which very fast puts a huge load on your server.

        Then you can start looking at the rest of your queries that are just like "... WHERE x = 3;" and verify that the x column is indexed so that the query does not have to scan the entire table to find the matching rows.

        When you have done this you will gain much more speed in your case than any server variables will ever give you.

        Good luck!


        • #5
          can i give you login name and the password )

          I got lost sorry but I will try i dont want to mess things up

          I greatly appreciated for your help.



          • #6
            Are You aware that this will take time and it may be connected with serious application rebuild ?

            Is there some sort of query log or debug ? It would be easier to work on ready queries.


            • #7
              No I am not


              • #8
                So, is there a query list or there is php mess like:
                $query = "select columns from ".$prefix."table ".$where." ".$orderby." limit ".$start.",".$stop ;
                $result = mysql_query( $query ) ;

                And what type of webpages are there ? Handmade or made by other people (like phpbb, phpnuke, etc) ?


                • #9

                  thank you for all your help



                  • #10
                    And by looking at your site I guess that you are running osCommerce, correct?

                    When I look at the sql code in the install directory I can say that indexes looks to be very scarce in the default database for osCommerce. Basically they only seem to have a primary key and some tables have one more index but that's all.
                    And looking at the PHP code then yes Januzi it looks like it's a php mess.

                    My suggestion is that you either figure out how to use the slow query log and turn it on to find which queries that are worst or hire someone to optimize it for you.

                    I don't think that you at this stage need to rewrite any php code, but I do think that you will have to create quite a few indexes in your database to solve your problem.


                    • #11
                      so how am i gonna do that )




                      • #12
                        What about:
                        http://www.google.se/search?q=mysql+optimization+consultant& amp;ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US fficial&client=firefox-a

                        I googled a bit more for you, read this article:

                        Then you can apparently install this plugin if you don't want to use the mysql slow query log:

                        I would have been glad to help you but I'm going on vacation in less than 36 hours and I can't really squeeze in the 4-8 hours I think will take to get it somewhat under control.


                        • #13
                          Thank you I greatly appreciated



                          • #14
                            Okay I did the easiest way and I install the contribution will you please check the botton.

                            thank you

                            I don't know what to do with that I am sorry.
                            I attached a file if you can please take a look




                            • #15
                              Those are queries only from one display of the front page ?
                              Disable products counter. Oscommerce is running query for every category, the more categories and subcategories the worst situation. (There was other solution at the oscommerce forums, maybe You'll find it)

                              As for tax rate, there is only one tax rate ? I changed friends shop so the query "select sum(tax_rate) as tax_rate from tax_rates ..." is running only once at the begin of the script. 100 products = 1 tax query