LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 11-16-2004, 08:04 AM   #1
loonix
Member
 
Registered: Aug 2003
Location: Sydney Australia
Distribution: CentOS/archlinux
Posts: 40

Rep: Reputation: 16
MySQL tweaking


Hi alll.
I have a server to fix. The customer has a Pentium 4 3ghz. 2gb of dual channel corsair ddr ram. 80gb sata 150 drive. This is their MySQL server. The server sees around 20 queries per second. I am currently using my-huge.cnf but i nee d some help tweaking this baby. I am not real familiar with doing this. There is a mix of MYISAM & INNODB tabbles.
First does everyone think the hardware is enuogh ??

Here is an exctract from the status page phpmyadmin produces.

Code:
    Connections   	            per hour              % 
Failed attempts   	 0   	       0.00   	        0.00 % 
Aborted  	         40  	      208.70  	        13.25 % 
Total  	                 302  	     1,575.65           100.00 %

Total   	  per hour   	  per minute   	  per second 
5,390  	          28,121.74  	    468.70  	                 7.81
This is at night when load is a little lite. I need some help setting up my.cnf. Or do I need bigger hardware.

Regards

Mick Pollard ( lunix )
------------------------------------------------
BOFH Excuse of the day:
Dual-Homed Software Destabilisation Warning
 
Old 11-27-2004, 01:15 AM   #2
loonix
Member
 
Registered: Aug 2003
Location: Sydney Australia
Distribution: CentOS/archlinux
Posts: 40

Original Poster
Rep: Reputation: 16
No posts yet. Surely someone lurking in here can point me in the right direction to get this sorted out.

Last edited by loonix; 11-28-2004 at 08:58 PM.
 
Old 11-27-2004, 04:08 AM   #3
MasterC
LQ Guru
 
Registered: Mar 2002
Location: Salt Lake City, UT - USA
Distribution: Gentoo ; LFS ; Kubuntu ; CentOS ; Raspbian
Posts: 12,613

Rep: Reputation: 69
I think that hardware is plenty, but I'm not exactly John H. ServerAdministrator.

It's their dedicated MySQL server, and nothing else? Is anything else running (ps)? What kind of transactions are these? Which distro is this running on? Was this compiled, or RPM'd? I'd go ahead and see if changing to the medium and small cnf's change anything, restarting MySQL each time, and being very careful to only do this for a short time to ensure minimal downtime should it arise. This would at least give you a point of reference.

Cool
 
Old 12-02-2004, 11:22 PM   #4
loonix
Member
 
Registered: Aug 2003
Location: Sydney Australia
Distribution: CentOS/archlinux
Posts: 40

Original Poster
Rep: Reputation: 16
Hi me again. I have some more info. MySQL 4.0.20 compiled with pentium 4 optimizations on Slackware 10.
The server primarily does alll my MySQL stuff and 4 websites. These are fairly heavy sites. Around a gig a day, total, of data served.
There is a mix of both MyISAM and INNODB tables. I have spoke to the web programer and he says that he needs to be using MyISAM as it ofers faster indexes and thats what he needs. ( I dont know enough about table type to critisize him )

I have fixed a major problem I forgot to enable SMP and HT in kernel. ( P4 3GB HT. ) Was only runnning as a UP. I have put 2.6.9 on and enabled SMP.
The problem is now all solved. ( nearly anyways )
I still see around 3% of "Aborted Requests". Is this bad and how can I fix it.


I am currently using the /etc/my-huge.cnf



--
Regards
Mick Pollard ( lunix )
------------------------------------------------
BOFH Excuse of the day:
Minor Protocol Crashdump

Last edited by loonix; 12-05-2004 at 06:09 PM.
 
Old 12-05-2004, 05:54 PM   #5
loonix
Member
 
Registered: Aug 2003
Location: Sydney Australia
Distribution: CentOS/archlinux
Posts: 40

Original Poster
Rep: Reputation: 16
Hi. I believe the following may help you to help me.
Here is a copy of my.cnf
Code:
# The MySQL server
[mysqld]
port            = 3306
socket          = /var/run/mysql/mysql.sock
skip-locking
key_buffer = 64M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
query_cache_size= 16m
thread_concurrency = 4
log             = /var/log/mysql/mysql.log
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
Extract from TOP :
Code:
 
top - 09:53:45 up 2 days, 19:00,  1 user,  load average: 0.00, 0.00, 0.00
Tasks: 173 total,   1 running, 172 sleeping,   0 stopped,   0 zombie
 Cpu0 :  0.3% us,  0.1% sy,  0.0% ni, 99.5% id,  0.1% wa,  0.0% hi,  0.1% si
 Cpu1 :  0.1% us,  0.1% sy,  0.0% ni, 99.8% id,  0.0% wa,  0.0% hi,  0.0% si
Mem:   2075736k total,   947672k used,  1128064k free,    67600k buffers
Swap:  2008116k total,        0k used,  2008116k free,   518424k cached
Code:
mysql> show variables;
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name                   | Value                                                                           |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log                        | 50                                                                           |
| basedir                         | /usr/                                                                           |
| binlog_cache_size               | 32768                                                                           |
| bulk_insert_buffer_size         | 8388608                                                                           |
| character_set                   | latin1                                                                           |
| character_sets                  | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert               | ON                                                                           |
| connect_timeout                 | 5                                                                           |
| convert_character_set           |                                                                           |
| datadir                         | /var/lib/mysql/                                                                           |
| default_week_format             | 0                                                                           |
| delay_key_write                 | ON                                                                           |
| delayed_insert_limit            | 100                                                                           |
| delayed_insert_timeout          | 300                                                                           |
| delayed_queue_size              | 1000                                                                           |
| flush                           | OFF                                                                           |
| flush_time                      | 0                                                                           |
| ft_boolean_syntax               | + -><()~*:""&|                                                                           |
| ft_min_word_len                 | 4                                                                           |
| ft_max_word_len                 | 254                                                                           |
| ft_max_word_len_for_sort        | 20                                                                           |
| ft_stopword_file                | (built-in)                                                                           |
| have_bdb                        | NO                                                                           |
| have_crypt                      | YES                                                                           |
| have_innodb                     | YES                                                                           |
| have_isam                       | YES                                                                           |
| have_raid                       | YES                                                                           |
| have_symlink                    | YES                                                                           |
| have_openssl                    | NO                                                                           |
| have_query_cache                | YES                                                                           |
| init_file                       |                                                                           |
| innodb_additional_mem_pool_size | 1048576                                                                           |
| innodb_buffer_pool_size         | 8388608                                                                           |
| innodb_data_file_path           | ibdata1:10M:autoextend                                                                           |
| innodb_data_home_dir            |                                                                           |
| innodb_file_io_threads          | 4                                                                           |
| innodb_force_recovery           | 0                                                                           |
| innodb_thread_concurrency       | 8                                                                           |
| innodb_flush_log_at_trx_commit  | 1                                                                           |
| innodb_fast_shutdown            | ON                                                                           |
| innodb_flush_method             |                                                                           |
| innodb_lock_wait_timeout        | 50                                                                           |
| 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_mirrored_log_groups      | 1                                                                           |
| innodb_max_dirty_pages_pct      | 90                                                                           |
| interactive_timeout             | 28800                                                                           |
| join_buffer_size                | 131072                                                                           |
| key_buffer_size                 | 67108864                                                                           |
| language                        | /usr/share/mysql/english/                                                                           |
| large_files_support             | ON                                                                           |
| license                         | GPL                                                                           |
| local_infile                    | ON                                                                           |
| locked_in_memory                | OFF                                                                           |
| log                             | ON                                                                           |
| log_update                      | OFF                                                                           |
| log_bin                         | ON                                                                           |
| log_slave_updates               | OFF                                                                           |
| log_slow_queries                | OFF                                                                           |
| log_warnings                    | ON                                                                           |
| long_query_time                 | 10                                                                           |
| low_priority_updates            | OFF                                                                           |
| lower_case_file_system          | OFF                                                                           |
| lower_case_table_names          | 0                                                                           |
| max_allowed_packet              | 1047552                                                                           |
| max_binlog_cache_size           | 4294967295                                                                           |
| max_binlog_size                 | 1073741824                                                                           |
| max_connections                 | 100                                                                           |
| max_connect_errors              | 10                                                                           |
| max_delayed_threads             | 20                                                                           |
| max_insert_delayed_threads      | 20                                                                           |
| max_heap_table_size             | 16777216                                                                           |
| max_join_size                   | 4294967295                                                                           |
| max_relay_log_size              | 0                                                                           |
| max_seeks_for_key               | 4294967295                                                                           |
| max_sort_length                 | 1024                                                                           |
| max_user_connections            | 0                                                                           |
| max_tmp_tables                  | 32                                                                           |
| max_write_lock_count            | 4294967295                                                                           |
| myisam_max_extra_sort_file_size | 268435456                                                                           |
| myisam_max_sort_file_size       | 2147483647                                                                           |
| myisam_repair_threads           | 1                                                                           |
| myisam_recover_options          | OFF                                                                           |
| myisam_sort_buffer_size         | 8388608                                                                           |
| net_buffer_length               | 8192                                                                           |
| net_read_timeout                | 30                                                                           |
| net_retry_count                 | 10                                                                           |
| net_write_timeout               | 60                                                                           |
| new                             | OFF                                                                           |
| open_files_limit                | 1024                                                                           |
| pid_file                        | /var/run/mysql/mysql.pid                                                                           |
| log_error                       |                                                                           |
| port                            | 3306                                                                           |
| protocol_version                | 10                                                                           |
| query_alloc_block_size          | 8192                                                                           |
| query_cache_limit               | 1048576                                                                           |
| query_cache_size                | 16777216                                                                           |
| query_cache_type                | ON                                                                           |
| query_prealloc_size             | 8192                                                                           |
| range_alloc_block_size          | 2048                                                                           |
| read_buffer_size                | 131072                                                                           |
| read_only                       | OFF                                                                           |
| read_rnd_buffer_size            | 262144                                                                           |
| rpl_recovery_rank               | 0                                                                           |
| server_id                       | 1                                                                           |
| slave_net_timeout               | 3600                                                                           |
| skip_external_locking           | ON                                                                           |
| skip_networking                 | OFF                                                                           |
| skip_show_database              | OFF                                                                           |
| slow_launch_time                | 2                                                                           |
| socket                          | /var/run/mysql/mysql.sock                                                                           |
| sort_buffer_size                | 524280                                                                           |
| sql_mode                        | 0                                                                           |
| table_cache                     | 64                                                                           |
| table_type                      | MYISAM                                                                           |
| thread_cache_size               | 0                                                                           |
| thread_stack                    | 196608                                                                           |
| tx_isolation                    | REPEATABLE-READ                                                                           |
| timezone                        | EST                                                                           |
| tmp_table_size                  | 33554432                                                                           |
| tmpdir                          | /tmp/                                                                           |
| transaction_alloc_block_size    | 8192                                                                           |
| transaction_prealloc_size       | 4096                                                                           |
| version                         | 4.0.20-log                                                                           |
| version_comment                 | Source distribution                                                                           |
| version_compile_os              | pc-linux                                                                           |
| wait_timeout                    | 28800                                                                           |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
131 rows in set (0.00 sec)

mysql> show status;
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| Aborted_clients          | 104      |
| Aborted_connects         | 0        |
| Bytes_received           | 3866453  |
| Bytes_sent               | 17545346 |
| Com_admin_commands       | 0        |
| Com_alter_table          | 0        |
| Com_analyze              | 0        |
| Com_backup_table         | 0        |
| Com_begin                | 12       |
| Com_change_db            | 5394     |
| Com_change_master        | 0        |
| Com_check                | 0        |
| Com_commit               | 12       |
| Com_create_db            | 0        |
| Com_create_function      | 0        |
| Com_create_index         | 0        |
| Com_create_table         | 0        |
| Com_delete               | 0        |
| Com_delete_multi         | 0        |
| Com_drop_db              | 0        |
| Com_drop_function        | 0        |
| Com_drop_index           | 0        |
| Com_drop_table           | 0        |
| Com_flush                | 0        |
| Com_grant                | 0        |
| Com_ha_close             | 0        |
| Com_ha_open              | 0        |
| Com_ha_read              | 0        |
| Com_insert               | 6        |
| Com_insert_select        | 0        |
| Com_kill                 | 0        |
| Com_load                 | 0        |
| Com_load_master_data     | 0        |
| Com_load_master_table    | 0        |
| Com_lock_tables          | 0        |
| Com_optimize             | 0        |
| Com_purge                | 0        |
| Com_rename_table         | 0        |
| Com_repair               | 0        |
| Com_replace              | 0        |
| Com_replace_select       | 0        |
| Com_reset                | 0        |
| Com_restore_table        | 0        |
| Com_revoke               | 0        |
| Com_rollback             | 0        |
| Com_savepoint            | 0        |
| Com_select               | 5134     |
| Com_set_option           | 0        |
| Com_show_binlog_events   | 0        |
| Com_show_binlogs         | 0        |
| Com_show_create          | 0        |
| Com_show_databases       | 0        |
| Com_show_fields          | 0        |
| Com_show_grants          | 0        |
| Com_show_keys            | 0        |
| Com_show_logs            | 0        |
| Com_show_master_status   | 0        |
| Com_show_new_master      | 0        |
| Com_show_open_tables     | 0        |
| Com_show_processlist     | 117      |
| Com_show_slave_hosts     | 0        |
| Com_show_slave_status    | 0        |
| Com_show_status          | 130      |
| Com_show_innodb_status   | 0        |
| Com_show_tables          | 0        |
| Com_show_variables       | 15       |
| Com_slave_start          | 0        |
| Com_slave_stop           | 0        |
| Com_truncate             | 0        |
| Com_unlock_tables        | 0        |
| Com_update               | 286      |
| Connections              | 2082     |
| Created_tmp_disk_tables  | 8        |
| Created_tmp_tables       | 32       |
| Created_tmp_files        | 0        |
| Delayed_insert_threads   | 0        |
| Delayed_writes           | 0        |
| Delayed_errors           | 0        |
| Flush_commands           | 1        |
| Handler_commit           | 12       |
| Handler_delete           | 0        |
| Handler_read_first       | 2956     |
| Handler_read_key         | 12932    |
| Handler_read_next        | 8688     |
| Handler_read_prev        | 58       |
| Handler_read_rnd         | 5930     |
| Handler_read_rnd_next    | 1351660  |
| Handler_rollback         | 190      |
| Handler_update           | 812      |
| Handler_write            | 607      |
| Key_blocks_used          | 105      |
| Key_read_requests        | 4535     |
| Key_reads                | 105      |
| Key_write_requests       | 7        |
| Key_writes               | 7        |
| Max_used_connections     | 73       |
| Not_flushed_key_blocks   | 0        |
| Not_flushed_delayed_rows | 0        |
| Open_tables              | 56       |
| Open_files               | 99       |
| Open_streams             | 0        |
| Opened_tables            | 62       |
| Questions                | 45259    |
| Qcache_queries_in_cache  | 4350     |
| Qcache_inserts           | 4843     |
| Qcache_hits              | 32182    |
| Qcache_lowmem_prunes     | 0        |
| Qcache_not_cached        | 291      |
| Qcache_free_memory       | 10704160 |
| Qcache_free_blocks       | 5        |
| Qcache_total_blocks      | 8759     |
| Rpl_status               | NULL     |
| Select_full_join         | 18       |
| Select_full_range_join   | 0        |
| Select_range             | 97       |
| Select_range_check       | 0        |
| Select_scan              | 3781     |
| Slave_open_temp_tables   | 0        |
| Slave_running            | OFF      |
| Slow_launch_threads      | 0        |
| Slow_queries             | 0        |
| Sort_merge_passes        | 0        |
| Sort_range               | 315      |
| Sort_rows                | 5930     |
| Sort_scan                | 432      |
| Table_locks_immediate    | 5582     |
| Table_locks_waited       | 0        |
| Threads_cached           | 0        |
| Threads_created          | 2081     |
| Threads_connected        | 62       |
| Threads_running          | 1        |
| Uptime                   | 2580     |
+--------------------------+----------+
132 rows in set (0.00 sec)

mysql>
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. Dannux Linux - Software 3 03-24-2006 09:44 AM
Help tweaking my fonts! calande Linux - General 5 10-26-2005 07:59 PM
Tweaking MySQL? Phaethar Linux - Software 3 07-14-2004 12:12 PM
um, Tweaking Thom_Redhat Linux - Software 13 06-17-2003 04:10 AM
some X tweaking zeky Linux - Software 3 12-23-2002 03:00 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 08:22 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration