Rick James Wrote:
-------------------------------------------------------
> Do you want to discuss it here? Or there?
Hi, let's keep it here.
First of all, I have miscalculated the numbers of queries/second ratio. Redone the math more accurately and there only is a rate of 600 queries / second during the first 1000 seconds. Judging after my graphs I really don't think there are any spikes above 1000 queries / second
>
> Please provide
> SHOW CREATE TABLE
mysql> SHOW CREATE TABLE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
Is this what I should provide ? -
-- QUERY START:
create table if not exists tig_users (
uid bigint unsigned NOT NULL auto_increment,
-- Jabber User ID
user_id varchar(2049) NOT NULL,
-- UserID SHA1 hash to prevent duplicate user_ids
sha1_user_id char(128) NOT NULL,
-- User password encrypted or not
user_pw varchar(255) default NULL,
-- Time the account has been created
acc_create_time timestamp DEFAULT CURRENT_TIMESTAMP,
-- Time of the last user login
last_login timestamp DEFAULT 0,
-- Time of the last user logout
last_logout timestamp DEFAULT 0,
-- User online status, if > 0 then user is online, the value
-- indicates the number of user connections.
-- It is incremented on each user login and decremented on each
-- user logout.
online_status int default 0,
-- Number of failed login attempts
failed_logins int default 0,
-- User status, whether the account is active or disabled
-- >0 - account active, 0 - account disabled
account_status int default 1,
primary key (uid),
unique key sha1_user_id (sha1_user_id),
key user_pw (user_pw),
key user_id (user_id(765)),
key last_login (last_login),
key last_logout (last_logout),
key account_status (account_status),
key online_status (online_status)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;
-- QUERY END:
-- QUERY START:
create table if not exists tig_nodes (
nid bigint unsigned NOT NULL auto_increment,
parent_nid bigint unsigned,
uid bigint unsigned NOT NULL,
node varchar(255) NOT NULL,
primary key (nid),
unique key tnode (parent_nid, uid, node),
key node (node),
key uid (uid),
key parent_nid (parent_nid),
constraint tig_nodes_constr foreign key (uid) references tig_users (uid)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;
-- QUERY END:
-- QUERY START:
create table if not exists tig_pairs (
nid bigint unsigned,
uid bigint unsigned NOT NULL,
pkey varchar(255) NOT NULL,
pval mediumtext,
key pkey (pkey),
key uid (uid),
key nid (nid),
constraint tig_pairs_constr_1 foreign key (uid) references tig_users (uid),
constraint tig_pairs_constr_2 foreign key (nid) references tig_nodes (nid)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;
-- QUERY END:
-- QUERY START:
create table if not exists short_news (
-- Automatic record ID
snid bigint unsigned NOT NULL auto_increment,
-- Automaticly generated timestamp and automaticly updated on change
publishing_time timestamp,
-- Optional news type: 'shorts', 'minis', 'techs', 'funs'....
news_type varchar(10),
-- Author JID
author varchar(128) NOT NULL,
-- Short subject - this is short news, right?
subject varchar(128) NOT NULL,
-- Short news message - this is short news, right?
body varchar(1024) NOT NULL,
primary key(snid),
key publishing_time (publishing_time),
key author (author),
key news_type (news_type)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;
-- QUERY END:
-- QUERY START:
create table if not exists xmpp_stanza (
id bigint unsigned NOT NULL auto_increment,
stanza text NOT NULL,
primary key (id)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;
-- QUERY END:
> SHOW TABLE STATUS
mysql> SHOW TABLE STATUS;
+-------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
| muc_history | MyISAM | 10 | Dynamic | 3 | 118 | 356 | 281474976710655 | 1024 | 0 | NULL | 2012-04-21 22:59:18 | 2012-05-16 12:55:21 | NULL | utf8_general_ci | NULL | | |
| short_news | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 49152 | 1429209088 | 1 | 2012-04-25 06:54:46 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC | |
| tig_nodes | InnoDB | 10 | Compact | 382852 | 50 | 19447808 | 0 | 51544064 | 1429209088 | 2830977 | 2012-06-10 18:21:25 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC | |
| tig_pairs | InnoDB | 10 | Compact | 129014 | 3201 | 413089792 | 0 | 13041664 | 1429209088 | NULL | 2012-06-10 18:23:46 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC | |
| tig_users | InnoDB | 10 | Compact | 200325 | 243 | 48840704 | 0 | 100728832 | 1429209088 | 1738291 | 2012-06-10 18:20:28 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC | |
| xmpp_stanza | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 1429209088 | 1 | 2012-04-25 06:54:46 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC | |
+-------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
6 rows in set (0.22 sec)
> SHOW VARIABLES LIKE 'innodb%';
mysql> SHOW VARIABLES LIKE 'innodb%';
+-----------------------------------------+------------------------+
| Variable_name | Value |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_size | 1073741824 |
| 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 | O_DIRECT |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | 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_rollback_on_timeout | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_legacy_cardinality_algorithm | ON |
+-----------------------------------------+------------------------+
36 rows in set (0.00 sec)
> * Poorly configured --> 100 tx/sec
> * Typical --> 1000 tx/sec
> * Specially crafted queries, schema and tuning -->
> 100K tx/sec.
Thanks. I have recently come across mysqltuner, which has to say the following (note that the test session only took 1.5 - 2 hours, not the entire 14 hours the server has been up for; also, the query cache gave me nothing but trouble according to mysqltuner so I have decided against it):
root@XMPP:/var/log/mysql# mysqltuner
>> 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
[!!] Successfully authenticated with no password - SECURITY RISK!
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.62-0ubuntu0.11.10.1-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 356B (Tables: 1)
[--] Data in InnoDB tables: 459M (Tables: 5)
[!!] Total fragmented tables: 5
-------- Performance Metrics -------------------------------------------------
[--] Up for: 14h 50m 11s (795K q [14.895 qps], 145 conn, TX: 352M, RX: 422M)
[--] Reads / Writes: 81% / 19%
[--] Total buffers: 2.0G global + 192.3M per thread (30 max threads)
[OK] Maximum possible memory usage: 7.7G (53% of installed RAM)
[!!] Slow queries: 100% (795K/795K)
[OK] Highest usage of available connections: 50% (15/30)
[OK] Key buffer size / total MyISAM indexes: 1.0G/90.0K
[OK] Key buffer hit rate: 99.4% (338 cached / 2 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1 sorts)
[OK] Temporary tables created on disk: 0% (48 on disk / 250K total)
[OK] Thread cache hit rate: 89% (15 created / 145 connections)
[OK] Table cache hit rate: 39% (61 open / 155 opened)
[OK] Open file limit used: 4% (49/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 459.1M/1.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
query_cache_size (>= 8M)
PS. I am troubled by the line "[OK] Table cache hit rate: 39% (61 open / 155 opened)"
My uneducated guess is that it should help if this was in the 90% range or so. Follows the my.cnf file, having one bolded line at table_open_cache = 200 - thisis something I have placed in just now, I am retesting with such new variable as I am typing here.
root@XMPP:/opt/tigase# egrep "^[^#]." /etc/mysql/my.cnf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
bind-address = 0.0.0.0
key_buffer = 1024M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 30
table_open_cache = 200
thread_concurrency = 16
sort_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 128M
query_cache_size = 0 #128M
query_cache_type = 0 # better disable this nuissance
query_cache_limit = 8M #not going to help by increasing it or soit seems
innodb_buffer_pool_size = 1024M
innodb_flush_method = O_DIRECT
log_error = /var/log/mysql/error.log
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 0
min_examined_row_limit = 0
log-queries-not-using-indexes
expire_logs_days = 10
max_binlog_size = 100M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
time-zone = '+00:00'
>
> XML has a lot of processing overhead, yet you have
> not hit 100% CPU. On the other hand, how many
> cores do you have? How many threads are you
> running?
I must say this machine is running a virtualized ubuntu linux, 64 bit. I tend not to believe what "cat /proc/cpuinfo" has to say:
root@XMPP:/var/log/mysql# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 2
model name : QEMU Virtual CPU version 0.14.1
stepping : 3
cpu MHz : 2393.946
cache size : 4096 KB
physical id : 0
siblings : 16
core id : 0
cpu cores : 16
apicid : 0
initial apicid : 0
fpu : yes
fpu_exception : yes
cpuid level : 4
wp : yes
flags : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 ht syscall nx lm rep_good nopl pni cx16 popcnt hypervisor lahf_lm
bogomips : 4787.89
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management:
------------ following some other such 15 processor descriptions----------------- pretty unbelievable - 16 CPUs x 16 cores each ?!...
*Last minute info, the bare metal machine is 2 CPUs * 4 cores each - 2 x Intel Xeon E5620
*the available RAM looks solid though, 15GB in VM from 48G total of bare metal
Number of threads running in the XMPP server currently (it is under test):
root@XMPP:/var/log/mysql# ps -eLF | grep -w -o "29298" | wc -l
1297
---------------New results from mysqltuenr, still getting those exceptions in this testing session, after having adjsuted the table_open_cache to 200:
-------- Performance Metrics -------------------------------------------------
[--] Up for: 58m 20s (717K q [204.880 qps], 122 conn, TX: 327M, RX: 389M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 2.0G global + 192.3M per thread (30 max threads)
[OK] Maximum possible memory usage: 7.7G (53% of installed RAM)
[!!] Slow queries: 100% (717K/717K)
[OK] Highest usage of available connections: 56% (17/30)
[OK] Key buffer size / total MyISAM indexes: 1.0G/90.0K
[OK] Key buffer hit rate: 99.4% (312 cached / 2 reads)
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (48 on disk / 224K total)
[OK] Thread cache hit rate: 86% (17 created / 122 connections)
[OK] Table cache hit rate: 40% (65 open / 159 opened)
[OK] Open file limit used: 4% (49/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 459.1M/1.0G
Regards,
kellogs