Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: MYSQL configuration for best OR performance

  1. #1
    Join Date
    Feb 2005
    Location
    BiH
    Age
    39
    Posts
    811

    Default MYSQL configuration for best OR performance

    I think we never discussed this, the way we should.

    I know there are few large OR sites out there, and I want to ask for their experience with configuring MYSQL for best performances.

    So, Im asking everyone to post here their experience (and mysql conf file if posible), so that we can think of best combination of parameters for OR.

    Thank you all.

  2. #2
    the_sandking's Avatar
    the_sandking is offline hadron remnant - Moderation Fachmann
    Join Date
    Apr 2003
    Location
    Nullspace
    Posts
    5,289

    Default Re: MYSQL configuration for best OR performance

    Proper server configuration for optimal performance won't necessarily be specific to Open-Realty, and will depend on a lot of other things like the number of accounts on the same physical server, the CPU type and number of CPU's/cores, and the amount of physical RAM in the server. There really isn't a one-size-fits-all configuration, and is why there are DB administrator optimization and monitoring tools.

    If you are running a really big site (1million+ rows in the listingsdbelements table), something you can do which will help, is convert your DB tables from using MyIsam to InnoDB, just understand that not all hosts configure mySQL with support for InnoDB.
    "Much of what looks like rudeness in hacker circles is not intended to give offense. Rather, it's the product of the direct, cut-through-the-BS communications style that is natural to people who are more concerned about solving problems than making others feel warm and fuzzy."

    "We gotta' go to the crappy town where I'm a hero!"
    -Hoban 'Wash' Washburne 2485-2519


    "When you’re born you get a ticket to the freak show. When you’re born in America, you get a front-row seat.."
    -George Carlin 1937-2008

    New to Open-Realty® and need help? Check the:
    -OR DOCUMENTATION -

    Important: Read this at least once in your lifetime
    How To Ask Questions The Smart Way

  3. #3
    Join Date
    Feb 2005
    Location
    BiH
    Age
    39
    Posts
    811

    Default Re: MYSQL configuration for best OR performance

    tnx sandking for reply.

    I understand that many variables are important. While I was searching on net for some advices for optimizing mysql, I found a lot of post related to other products (scripts) where users are suggesting some mysql configuration for best performances, thats why I asked for any hint ( I know it cant be global solution, but why not try someone else configuration, if he dont have any problems).

    I asked that people post here config files, and I didnt show my mysql my.cnf, so here it is:

    Code:
    #
    # The MySQL database server configuration file.
    [client]
    port		= 3306
    socket		= /var/run/mysqld/mysqld.sock
    
    [mysqld_safe]
    socket		= /var/run/mysqld/mysqld.sock
    nice		= 0
    
    skip-bdb
    
    set-variable = innodb_buffer_pool_size=2M
    set-variable = innodb_additional_mem_pool_size=500K
    set-variable = innodb_log_buffer_size=500K
    set-variable = innodb_thread_concurrency=2
    [mysqld]
    set-variable=local-infile=0
    #
    # * Basic Settings
    #
    user		= mysql
    pid-file	= /var/run/mysqld/mysqld.pid
    socket		= /var/run/mysqld/mysqld.sock
    port		= 3306
    basedir		= /usr
    datadir		= /var/lib/mysql
    tmpdir		= /tmp
    language	= /usr/share/mysql/english
    skip-external-locking
    #UTF-8
    character-set-server=utf8
    collation-server=utf8_general_ci
    max_connections=350
    
    #
    # For compatibility to other Debian packages that still use
    # libmysqlclient10 and libmysqlclient12.
    old_passwords	= 1
    #
    # * Fine Tuning
    #
    key_buffer		= 16M
    max_allowed_packet	= 16M
    thread_stack		= 128K
    #
    # * Query Cache Configuration
    #
    query_cache_limit	= 1048576
    query_cache_size        = 16777216
    query_cache_type        = 1
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    #log		= /var/log/mysql.log
    #log		= /var/log/mysql/mysql.log
    #
    # Error logging goes to syslog. This is a Debian improvement :)
    #
    # Here you can see queries with especially long duration
    #log-slow-queries	= /var/log/mysql/mysql-slow.log
    #
    # The following can be used as easy to replay backup logs or for replication.
    #server-id		= 1
    log-bin			= /var/log/mysql/mysql-bin.log
    expire-logs-days	= 20
    max_binlog_size         = 104857600
    #binlog-do-db		= include_database_name
    #binlog-ignore-db	= include_database_name
    #
    # * BerkeleyDB
    #
    # According to an MySQL employee the use of BerkeleyDB is now discouraged
    # and support for it will probably cease in the next versions.
    skip-bdb
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # If you want to enable SSL support (recommended) read the manual or my
    # HOWTO in /usr/share/doc/mysql-server/SSL-MINI-HOWTO.txt.gz
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem
    
    
    
    set-variable = innodb_buffer_pool_size=2M
    set-variable = innodb_additional_mem_pool_size=500K
    set-variable = innodb_log_buffer_size=500K
    set-variable = innodb_thread_concurrency=2
    [mysqldump]
    quick
    quote-names
    max_allowed_packet	= 16M
    
    [mysql]
    #no-auto-rehash	# faster start of mysql but no tab completition
    
    [isamchk]
    key_buffer		= 16M
    
    #
    # * NDB Cluster
    #
    # See /usr/share/doc/mysql-server-*/README.Debian for more information.
    #
    # The following configuration is read by the ndbd storage daemons,
    # not from the ndb_mgmd management daemon.
    #
    # [MYSQL_CLUSTER]
    # ndb-connectstring=127.0.0.1
    if you can give any suggestion to change something in my configuration, pls dont hesitate.

    From this, I assume that my host provide support for InnoDB.

    Is there any step-by-step tutorial how to convert my DB tables from using MyIsam to InnoDB?

    Thank you

  4. #4
    Join Date
    Jun 2004
    Posts
    1,067

    Default Re: MYSQL configuration for best OR performance

    Take a look at MySQLTuner it is a script you can run on your MySQL server that will give you some suggestions on how to adjust your MySQL configuration file for better performance.

  5. #5
    Join Date
    Feb 2005
    Location
    BiH
    Age
    39
    Posts
    811

    Default Re: MYSQL configuration for best OR performance

    thank you greeng,

    here is what it says:

    Code:
     >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.22-Debian_0ubuntu6.06.12-log
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 1464)
    [--] Data in InnoDB tables: 3M (Tables: 149)
    [!!] Total fragmented tables: 109
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 5h 59m 49s (65M q [338.757 qps], 121K conn, TX: 1B, RX: 1B)
    [--] Reads / Writes: 95% / 5%
    [--] Total buffers: 51.0M global + 2.6M per thread (350 max threads)
    [OK] Maximum possible memory usage: 969.7M (5% of installed RAM)
    [OK] Slow queries: 0% (13/65M)
    [OK] Highest usage of available connections: 28% (98/350)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/611.2M
    [OK] Key buffer hit rate: 99.7% (3B cached / 10M reads)
    [OK] Query cache efficiency: 60.6% (38M cached / 63M selects)
    [!!] Query cache prunes per day: 6183074
    [OK] Sorts requiring temporary tables: 0% (8K temp sorts / 4M sorts)
    [!!] Joins performed without indexes: 325883
    [!!] Temporary tables created on disk: 38% (633K on disk / 1M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 304K opened)
    [OK] Open file limit used: 6% (121/1K)
    [OK] Table locks acquired immediately: 99% (39M immediate / 39M locks)
    [!!] InnoDB data size / buffer pool: 3.1M/2.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 16M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 64)
        innodb_buffer_pool_size (>= 3M)
    what do you suggest?

    tnx in advance

  6. #6
    the_sandking's Avatar
    the_sandking is offline hadron remnant - Moderation Fachmann
    Join Date
    Apr 2003
    Location
    Nullspace
    Posts
    5,289

    Default Re: MYSQL configuration for best OR performance

    Quote Originally Posted by bit View Post

    what do you suggest?

    Do whatever you can that is in the recommendations.The mySQL Tuner report you have is specific to your setup.



    "Much of what looks like rudeness in hacker circles is not intended to give offense. Rather, it's the product of the direct, cut-through-the-BS communications style that is natural to people who are more concerned about solving problems than making others feel warm and fuzzy."

    "We gotta' go to the crappy town where I'm a hero!"
    -Hoban 'Wash' Washburne 2485-2519


    "When you’re born you get a ticket to the freak show. When you’re born in America, you get a front-row seat.."
    -George Carlin 1937-2008

    New to Open-Realty® and need help? Check the:
    -OR DOCUMENTATION -

    Important: Read this at least once in your lifetime
    How To Ask Questions The Smart Way

  7. #7
    Join Date
    May 2004
    Location
    Long Island, NY
    Age
    48
    Posts
    9,796

    Default Re: MYSQL configuration for best OR performance

    bit,
    mysql tuning is not a 1 time settings change and your done. There are a lot of variables that come into play on a mysql server as mentioned above. I spent 6 weeks tuning my server for optimal mysql performance. I used mysql tuner and tuning primer. I adjusted the cnf file based on the results of the 2 programs and then let it run for 3-4 days before running the diagnostics again and making more changes. Every change you make can affect other areas so you need to keep tweaking the settings until you reach the best settings for your particular server.

  8. #8
    Join Date
    Feb 2005
    Location
    BiH
    Age
    39
    Posts
    811

    Default Re: MYSQL configuration for best OR performance

    thank you guys. Yes pb, I downloaded both script tuner and tuning primer. Did some optimization. Now I will wait for few days and see report again.

    However, when I run tuning primer, I am getting some errors in output, could it be that my script is corrupted or?.

    Code:
            -- MYSQL PERFORMANCE TUNING PRIMER --
                 - By: Matthew Montgomery -
    
    MySQL Version 5.0.22-Debian_0ubuntu6.06.12-log i486
    
    ./tuning-primer.sh: line 497: bc: command not found
    ./tuning-primer.sh: line 498: bc: command not found
    ./tuning-primer.sh: line 499: bc: command not found
    ./tuning-primer.sh: line 500: bc: command not found
    ./tuning-primer.sh: line 501: bc: command not found
    ./tuning-primer.sh: line 502: bc: command not found
    Uptime =  days  hrs  min  sec
    Avg. qps = 311
    Total Questions = 9941868
    Threads Connected = 24
    
    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is NOT enabled.
    Current long_query_time = 10 sec.
    You have 1 out of 9941959 that take longer than 10 sec. to complete
    ./tuning-primer.sh: line 403: bc: command not found
    ./tuning-primer.sh: line 606: [: -gt: unary operator expected
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is enabled
    Binlog sync is not enabled, you could loose binlog records during a server crash
    
    WORKER THREADS
    Current thread_cache_size = 40
    Current threads_cached = 14
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 100
    Current threads_connected = 25
    Historic max_used_connections = 39
    The number of used connections is 39% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    INNODB STATUS
    ./tuning-primer.sh: line 440: bc: command not found
    Current InnoDB index space =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current InnoDB data space =  M
    Current InnoDB buffer pool free = 0 %
    ./tuning-primer.sh: line 440: bc: command not found
    Current innodb_buffer_pool_size =  M
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory
    
    MEMORY USAGE
    ./tuning-primer.sh: line 1321: bc: command not found
    ./tuning-primer.sh: line 1322: bc: command not found
    ./tuning-primer.sh: line 1346: bc: command not found
    ./tuning-primer.sh: line 1349: bc: command not found
    ./tuning-primer.sh: line 1350: bc: command not found
    ./tuning-primer.sh: line 1352: bc: command not found
    ./tuning-primer.sh: line 1354: [: -gt: unary operator expected
    ./tuning-primer.sh: line 459: [: max_memoryHR: integer expression expected
    ./tuning-primer.sh: line 465: [: max_memoryHR: integer expression expected
    ./tuning-primer.sh: line 471: [: max_memoryHR: integer expression expected
    ./tuning-primer.sh: line 478: export: `=max_memoryHR': not a valid identifier
    Max Memory Ever Allocated :  bytes
    ./tuning-primer.sh: line 459: [: per_thread_buffersHR: integer expression expected
    ./tuning-primer.sh: line 465: [: per_thread_buffersHR: integer expression expected
    ./tuning-primer.sh: line 471: [: per_thread_buffersHR: integer expression expected
    ./tuning-primer.sh: line 478: export: `=per_thread_buffersHR': not a valid identifier
    Configured Max Per-thread Buffers :  bytes
    ./tuning-primer.sh: line 459: [: global_buffersHR: integer expression expected
    ./tuning-primer.sh: line 465: [: global_buffersHR: integer expression expected
    ./tuning-primer.sh: line 471: [: global_buffersHR: integer expression expected
    ./tuning-primer.sh: line 478: export: `=global_buffersHR': not a valid identifier
    Configured Max Global Buffers :  bytes
    ./tuning-primer.sh: line 459: [: total_memoryHR: integer expression expected
    ./tuning-primer.sh: line 465: [: total_memoryHR: integer expression expected
    ./tuning-primer.sh: line 471: [: total_memoryHR: integer expression expected
    ./tuning-primer.sh: line 478: export: `=total_memoryHR': not a valid identifier
    Configured Max Memory Limit :  bytes
    ./tuning-primer.sh: line 440: bc: command not found
    Physical Memory :  G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    ./tuning-primer.sh: line 754: bc: command not found
    ./tuning-primer.sh: line 755: bc: command not found
    ./tuning-primer.sh: line 440: bc: command not found
    Current MyISAM index space =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current key_buffer_size =  M
    Key cache miss rate is 1 : 1528
    Key buffer free ratio =  %
    ./tuning-primer.sh: line 792: [: -le: unary operator expected
    ./tuning-primer.sh: line 796: [: -le: unary operator expected
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    ./tuning-primer.sh: line 827: bc: command not found
    ./tuning-primer.sh: line 828: bc: command not found
    Query cache is enabled
    ./tuning-primer.sh: line 440: bc: command not found
    Current query_cache_size =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current query_cache_used =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current query_cache_limit =  M
    Current Query cache Memory fill ratio =  %
    ./tuning-primer.sh: line 440: bc: command not found
    Current query_cache_min_res_unit =  K
    ./tuning-primer.sh: line 845: bc: command not found
    ./tuning-primer.sh: line 846: bc: command not found
    ./tuning-primer.sh: line 847: [: -gt: unary operator expected
    ./tuning-primer.sh: line 854: [: -le: unary operator expected
    ./tuning-primer.sh: line 858: [: -ge: unary operator expected
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    ./tuning-primer.sh: line 440: bc: command not found
    Current sort_buffer_size =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current read_rnd_buffer_size =  M
    Sort buffer seems to be fine
    
    JOINS
    ./tuning-primer.sh: line 440: bc: command not found
    Current join_buffer_size =  M
    You have had 53902 queries where a join could not use an index properly
    join_buffer_size >= 4 M
    This is not advised
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    
    OPEN FILES LIMIT
    Current open_files_limit = 11095 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_cache value = 2000 tables
    You have a total of 1338 tables
    You have 2000 open tables.
    Current table_cache hit rate is 52%
    , while 100% of your table cache is in use
    You should probably increase your table_cache
    
    TEMP TABLES
    ./tuning-primer.sh: line 440: bc: command not found
    Current max_heap_table_size =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current tmp_table_size =  M
    Of 137443 temp tables, 38% were created on disk
    Effective in-memory tmp_table_size is limited to max_heap_table_size.
    Perhaps you should increase your tmp_table_size and/or max_heap_table_size
    to reduce the number of disk-based temporary tables
    Note! BLOB and TEXT columns are not allow in memory tables.
    If you are using these columns raising these values might not impact your
    ratio of on disk temp tables.
    
    TABLE SCANS
    ./tuning-primer.sh: line 440: bc: command not found
    Current read_buffer_size =  M
    Current table scan ratio = 5 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 4952
    You may benefit from selective use of InnoDB.
    If you have long running SELECT's against MyISAM tables and perform
    frequent updates consider setting 'low_priority_updates=1'
    If you have a high concurrency of inserts on Dynamic row-length tables
    consider setting 'concurrent_insert=2'.
    Question: could bad addon code cause this: "You have had 53902 queries where a join could not use an index properly".

  9. #9
    Join Date
    Feb 2005
    Location
    BiH
    Age
    39
    Posts
    811

    Default Re: MYSQL configuration for best OR performance

    forget to give my mysql variables from phpmyadmin:

    Code:
    Server variables and settings
    Variable 	Session value / Global value
    auto increment increment 	1
    auto increment offset 	1
    automatic sp privileges 	ON
    back log 	50
    basedir 	/usr/
    binlog cache size 	32,768
    bulk insert buffer size 	8,388,608
    character set client 	utf8
    character set connection 	utf8
    character set database 	utf8
    character set filesystem 	binary
    character set results 	utf8
    character set server 	utf8
    character set system 	utf8
    character sets dir 	/usr/share/mysql/charsets/
    collation connection 	utf8_general_ci
    collation database 	utf8_general_ci
    collation server 	utf8_general_ci
    completion type 	0
    concurrent insert 	1
    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
    div precision increment 	4
    engine condition pushdown 	OFF
    expire logs days 	20
    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 	YES
    have bdb 	NO
    have blackhole engine 	NO
    have compress 	YES
    have crypt 	YES
    have csv 	YES
    have example engine 	NO
    have federated engine 	YES
    have geometry 	YES
    have innodb 	YES
    have isam 	NO
    have ndbcluster 	DISABLED
    have merge engine 	YES
    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 	524,288
    innodb autoextend increment 	8
    innodb buffer pool awe mem mb 	0
    innodb buffer pool size 	5,242,880
    innodb checksums 	ON
    innodb commit concurrency 	0
    innodb concurrency tickets 	500
    innodb data file path 	ibdata1:10M:autoextend
    innodb data home dir 	
    innodb doublewrite 	ON
    innodb fast shutdown 	1
    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 	512,000
    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 support xa 	ON
    innodb sync spin loops 	20
    innodb table locks 	ON
    innodb thread concurrency 	2
    innodb thread sleep delay 	10,000
    interactive timeout 	28,800
    join buffer size 	4,190,208
    key buffer size 	33,554,432
    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
    large page size 	0
    large pages 	OFF
    license 	GPL
    local infile 	OFF
    locked in memory 	OFF
    log 	OFF
    log bin 	ON
    log bin trust function creators 	OFF
    log error 	
    log slave updates 	OFF
    log slow queries 	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 	16,776,192
    max binlog cache size 	4,294,967,295
    max binlog size 	104,857,600
    max connect errors 	10
    max connections 	100
    max delayed threads 	20
    max error count 	64
    max heap table size 	314,571,776
    max insert delayed threads 	20
    max join size 	18,446,744,073,709,551,616
    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 sp recursion depth 	0
    max tmp tables 	32
    max user connections 	0
    max write lock count 	4,294,967,295
    multi range count 	256
    myisam data pointer size 	6
    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
    ndb autoincrement prefetch sz 	32
    ndb force send 	ON
    ndb use exact count 	ON
    ndb use transactions 	ON
    (Global value) 	OFF
    ndb cache check time 	0
    net buffer length 	16,384
    net read timeout 	30
    net retry count 	10
    net write timeout 	60
    new 	OFF
    old passwords 	ON
    open files limit 	11,095
    optimizer prune level 	1
    optimizer search depth 	62
    pid file 	/var/run/mysqld/mysqld.pid
    prepared stmt count 	0
    port 	3,306
    preload buffer size 	32,768
    protocol version 	10
    query alloc block size 	8,192
    query cache limit 	2,097,152
    query cache min res unit 	4,096
    query cache size 	134,217,728
    query cache type 	ON
    query cache wlock invalidate 	OFF
    query prealloc size 	8,192
    range alloc block size 	2,048
    read buffer size 	2,093,056
    read only 	OFF
    read rnd buffer size 	4,190,208
    relay log purge 	ON
    relay log space limit 	0
    rpl recovery rank 	0
    secure auth 	OFF
    server id 	1
    skip external locking 	ON
    skip networking 	OFF
    skip show database 	OFF
    slave compressed protocol 	OFF
    slave load tmpdir 	/tmp/
    slave net timeout 	3,600
    slave skip errors 	OFF
    slave transaction retries 	10
    slow launch time 	2
    socket 	/var/run/mysqld/mysqld.sock
    sort buffer size 	2,097,144
    sql mode 	
    sql notes 	ON
    sql warnings 	ON
    storage engine 	MyISAM
    sync binlog 	0
    sync frm 	ON
    system time zone 	CEST
    table cache 	2,000
    table lock wait timeout 	50
    table type 	MyISAM
    thread cache size 	40
    thread stack 	131,072
    time format 	%H:%i:%s
    time zone 	SYSTEM
    timed mutexes 	OFF
    tmp table size 	314,572,800
    tmpdir 	/tmp
    transaction alloc block size 	8,192
    transaction prealloc size 	4,096
    tx isolation 	REPEATABLE-READ
    updatable views with limit 	YES
    version 	5.0.22-Debian_0ubuntu6.06.12-log
    version comment 	Debian Etch distribution
    version compile machine 	i486
    version compile os 	pc-linux-gnu
    wait timeout 	28,800
    If anyone can give advice what to tune, you are welcome.

    Thank you

  10. #10
    Join Date
    Feb 2004
    Posts
    1,085

    Default Re: MYSQL configuration for best OR performance

    Bit, what problem are you having, what do you want to address?
    Using OR and MySQL together work great with both using default settings. If you are having issues they need to be addressed. For example there are things you can do to help if you have rather large tables like the listingdbelements table. If you do a search on the table for rows with empty values you may find thousands of rows. It seems that when you modify or add a property that there is an insert for each field you have in your listing, empty or not. (now I am speaking from older versions, I've not tested 3.0 yet). Do this to see.
    SELECT * FROM $config[table_prefix]listingsdbelements WHERE listingsdbelements_field_value = '';
    This should let you see what I mean. If you have 100,000 listings in your database and each listing has 100 fields you do the math, there are a LOT of rows in the listingsdbelements table and I've found many that are are not needed as they are empty unless each field in your listing is completed. Do this to clean it up.
    DELETE FROM $config[table_prefix]listingsdbelements WHERE listingsdbelements_field_value = '';

    That alone can help improve your performance. Slow queries are another issue. One thing I've noticed is that when your search form has several fields to search from like beds,baths,price,city etc your qurey to the search.inc.php passes all of those to the URL string and is read by the search.inc.php. Reducing the fields and eliminating the ones that the user does not choose will speed up the queries. Take this example URL:
    index.php?PROP_TYPE_DESCRIPTION=&SALE_PRICE-min=&SALE_PRICE-max=&BEDROOMS=&BATHS_T
    OTAL=&action=searchresults
    Notice the fields that have no value after the equal sign. remove them and the search.inc.php has less work to do.

    I am not saying do not optimize your MySQL, just optimize to correct your problem.

Similar Threads

  1. UMP performance
    By FJCOM in forum Language help, ideas, discussion & Releases (CLOSED)
    Replies: 13
    Last Post: 07-12-2008, 09:53 PM
  2. Add Listing Performance
    By asawyer13 in forum General Help (CLOSED)
    Replies: 5
    Last Post: 03-25-2008, 02:37 PM
  3. MySQL performance questions
    By tooljob in forum General Help (CLOSED)
    Replies: 9
    Last Post: 05-09-2007, 09:57 PM
  4. MySQL Port Configuration
    By webavant in forum Installation/Configuration Help (CLOSED)
    Replies: 0
    Last Post: 04-28-2006, 06:44 PM
  5. MySQL error configuration.php
    By tysonh04 in forum HELP (CLOSED)
    Replies: 2
    Last Post: 05-11-2004, 08:11 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •