1. Please take a little time for this simple survey! Thank you for participating!
    Dismiss Notice
  2. Dear Pleskians, please read this carefully! New attachments and other rules Thank you!
    Dismiss Notice
  3. Dear Pleskians, I really hope that you will share your opinion in this Special topic for chatter about Plesk in the Clouds. Thank you!
    Dismiss Notice

Slow MySQL

Discussion in 'Plesk for Linux - 8.x and Older' started by DoobyWho, Oct 21, 2005.

  1. DoobyWho

    DoobyWho Guest

    0
     
    One of my users has a 500mb table in MySQL and for some reason whenever the user runs a query via PHP on another table (say one with 20 records, each simple text so table size is a few kb) the whole server gets bogged down.

    Any ideas? When its run via SSH, the query goes fast. However via PHP it's really slow. Either using PHPMyAdmin or my script.

    Here is my my.cnf
    ------------------
    [mysqld]
    safe-show-database
    innodb_data_file_path=ibdata1:10M:autoextend
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    max_allowed_packet=1000M;

    [mysql.server]
    user=mysql
    basedir=/var/lib

    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    ------------------

    Here are my mysql variables using 'show variables'
    ------------------
    back_log 50
    basedir /usr/
    binlog_cache_size 32768
    bulk_insert_buffer_size 8388608
    character_set_client latin1
    character_set_connection latin1
    character_set_database latin1
    character_set_results latin1
    character_set_server latin1
    character_set_system utf8
    character_sets_dir /usr/share/mysql/charsets/
    collation_connection 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 1000
    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 1024
    have_archiveNO
    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_ndbcluster NO
    have_opensslNO
    have_query_cache YES
    have_raid NO
    have_rtree_keys YES
    have_symlinkYES
    init_connect
    init_file
    init_slave
    innodb_additional_mem_pool_size 1048576
    innodb_autoextend_increment 8
    innodb_buffer_pool_awe_mem_mb 0
    innodb_buffer_pool_size 8388608
    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 1048576
    innodb_log_file_size 5242880
    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 28800
    join_buffer_size 131072
    key_buffer_size 8388600
    key_cache_age_threshold 300
    key_cache_block_size 1024
    key_cache_division_limit 100
    language /usr/share/mysql/english/
    large_files_support ON
    license GPL
    local_infileON
    locked_in_memory OFF
    log OFF
    log_bin OFF
    log_error
    log_slave_updates OFF
    log_slow_queries OFF
    log_update OFF
    log_warnings1
    long_query_time 10
    low_priority_updates OFF
    lower_case_file_system OFF
    lower_case_table_names 0
    max_allowed_packet 1048574976
    max_binlog_cache_size 4294967295
    max_binlog_size 1073741824
    max_connect_errors 10
    max_connections 100
    max_delayed_threads 20
    max_error_count 64
    max_heap_table_size 16777216
    max_insert_delayed_threads 20
    max_join_size 4294967295
    max_length_for_sort_data 1024
    max_relay_log_size 0
    max_seeks_for_key 4294967295
    max_sort_length 1024
    max_tmp_tables 32
    max_user_connections 0
    max_write_lock_count 4294967295
    myisam_data_pointer_size 4
    myisam_max_extra_sort_file_size 2147483648
    myisam_max_sort_file_size 2147483647
    myisam_recover_options OFF
    myisam_repair_threads 1
    myisam_sort_buffer_size 8388608
    net_buffer_length 16384
    net_read_timeout 30
    net_retry_count 10
    net_write_timeout 60
    new OFF
    old_passwords OFF
    open_files_limit 1024
    pid_file /var/run/mysqld/mysqld.pid
    port 3306
    preload_buffer_size 32768
    protocol_version 10
    query_alloc_block_size 8192
    query_cache_limit 1048576
    query_cache_min_res_unit 4096
    query_cache_size 0
    query_cache_type ON
    query_cache_wlock_invalidate OFF
    query_prealloc_size 8192
    range_alloc_block_size 2048
    read_buffer_size 131072
    read_only OFF
    read_rnd_buffer_size 262144
    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 3600
    slave_transaction_retries 0
    slow_launch_time 2
    socket /var/lib/mysql/mysql.sock
    sort_buffer_size 2097144
    sql_mode
    storage_engine MyISAM
    sql_notes ON
    sql_warningsON
    sync_binlog 0
    sync_replication 0
    sync_replication_slave_id 0
    sync_replication_timeout 0
    sync_frm ON
    system_time_zone CDT
    table_cache 64
    table_type MyISAM
    thread_cache_size 0
    thread_stack 196608
    time_format %H:%i:%s
    time_zone SYSTEM
    tmp_table_size 33554432
    tmpdir
    transaction_alloc_block_size 8192
    transaction_prealloc_size 4096
    tx_isolation REPEATABLE-READ
    version 4.1.14
    version_comment Source distribution
    version_compile_machine i686
    version_compile_os redhat-linux-gnu
    wait_timeout28800
    +---------------------------------+----------------------------+


    I have 1GB ram and users are uploading large files to MySQL, anywhere from 5mb to a couple hundred (only now and then).
     
  2. DoobyWho

    DoobyWho Guest

    0
     
    nevermind- i fixed it. you can delete this :)

    It was a runaway query i found by using the slow query option.
     
Loading...